こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

エクセルVBAの処理速度アップについて

以下のエクセルVBAを組んでいるのですが、処理の速度をあげたいです。
無駄な記述があると思うのですが、調べても試行錯誤しても、上手くいきません。
処理速度をあげる記述をご教示願います。よろしくお願い申し上げます。

※別ファイルのAccessファイルをデータベースとして、「今年」テーブルと、「前年」テーブルそれぞれから条件抽出して、受注数量を合計させるものです。
※SQLのVBAです。なお、実際ファイルのVBAは、以下記述の5倍量あります(内容は、セル範囲が違うだけで同じ。現在速度:約30秒)。
※「配列」というのを活用すれば速くなるようなのですが、理解できませんでした。。。
ーーーー【以下、VBA】-----------
Sub DSUM集計()
Application.ScreenUpdating = False
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mySQL As String
Dim cmd As ADODB.Command

Dim AA As Variant
AA = "AND 営業箇所" & Range("C13") & " AND 拒 IS NULL AND 販売伝票 <" & Range("D13") & " AND 品名 " & Range("E13") & " AND 得意先名 " & Range("F13") & " AND 請求先名 " & Range("G13") & " AND 出荷先名 " & Range("H13")

Set db = New ADODB.Connection
db.Provider = "Microsoft.Ace.OLEDB.12.0"
db.Open "\\▲▲▲\ACCESS.accdb"

With Worksheets("補助計算")

mySQL = " SELECT SUM(受注数量) FROM 今年 "
mySQL = mySQL & "WHERE 納入期日=" & Range("B13") & AA
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = db
cmd.CommandText = mySQL
Set rs = cmd.Execute
Range("I13").CopyFromRecordset rs

mySQL = " SELECT SUM(受注数量) FROM 今年 "
mySQL = mySQL & "WHERE 納入期日=" & Range("B14") & AA
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = db
cmd.CommandText = mySQL
Set rs = cmd.Execute
Range("I14").CopyFromRecordset rs

mySQL = " SELECT SUM(受注数量) FROM 前年 "
mySQL = mySQL & "WHERE 納入期日=" & Range("K14") & AA
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = db
cmd.CommandText = mySQL
Set rs = cmd.Execute
Range("M14").CopyFromRecordset rs

   mySQL = " SELECT SUM(受注数量) FROM 前年 "
mySQL = mySQL & "WHERE 納入期日=" & Range("K15") & AA
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = db
cmd.CommandText = mySQL
Set rs = cmd.Execute
Range("M15").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set db = Nothing
Application.ScreenUpdating = True
End With
End Sub
ーーーーーーーーーーーーーーーーーーーー
以上

投稿日時 - 2015-10-05 20:29:45

QNo.9059198

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

自信ないですが、気になったもので、1意見として、言わせてください。
今年データー>シート1
去年データ->シート2 に
(エクセルシートにデータを)インポートします。VBAで遣っても、10行以内のコードで、できるでしょう。
ーー
これで、なにがしかのタイム(作業時間)をロスしますが、処理(下記Excelシートでの処理)が、辛抱できる範囲内なら、下記のこの案もあり得るのでは。
ーー
今ADOでやっているのは
・今年データシートと昨年データシートの2種類データに対し、検索し集計する
すなわち
・--今年シート----|ーーー昨年シート
納入期日 B13 B14 K14 K15
ーーーーーーーーーーーーーーーーーーーーー
営業所  C13
販売伝票 D13 左に  左に 左に 左に
品名   E13 同じ  同じ 同じ 同じ
得意先  F13
請求先  G13
出荷先名 H13
が条件で、条件の内容を規定している。
すなわち(アクセスとは別の)シートのセルを参照して、データが条件と一致するか見る。
これはユーザーフォーム等で1セットの条件に、見やすいように、まとめられると思う。
他のソフトにまたがる処理でなくなるので完了処理が早くなると思う。
条件が1行内の複数列が、条件を満たしているかどうかを判別するタイプのもので、レコード間(エクセルの場合で言えば、違った行データ)の関連を気にする性格の検索ではないので幸い(簡単)なわけです。
ーー
一方、エクセルのVBAコード
Sub test03()
c = Worksheets("Sheet1").Range("a1:j10000")
s = 0
For i = 1 To 10000
If Cells(i, 1) = "a9973" Then s = s + Cells(i, "K") ’"a9973"は私のデータの場合の勝手な内容
If Cells(i, 1) = "a9978" Then s = s + Cells(i, "K")
Next i
MsgBox s
End Sub
(10列X10000行の仮のデータで実行してみた)
のような処理が可能になるので、質問のAAの部分にある条件をエクセルVBAコードで
組めば簡単ではないかな。この際、ごく初歩的な、総なめ法を使うのです。
これは
http://officetanaka.net/excel/vba/speed/s11.htm
のような記事があったのが、頭にあったからです
c = Worksheets("Sheet1").Range("a1:j10000")の1行で10X10000のセルデータが2次元配列的になってくれる。
cはVariant変数で、質問の「>配列」というのを活用すれば速くなるようなのですが」から思いついたのですが。
会社でVBAを組むことは許されているようですし。
VBAコードでなく、SUMIFS関数でできるかもしれない。
http://www.becoolusers.com/excel/sumifs.html
ーーー
・データ量(数、100万を超えるのでしょうか?)が質問に書いてない
・SQL文のAAの部分で、「営業箇所」などのあとに=が必要ないですか。
・小生も自信を持って本件書いてない。おかしければ無視してください。
・エクセルVBAの(SQLをつかわない)プログラムを作りなおすのは、面倒ですが
 総当たり法のロジックでやるなら簡単なプログラム(IF文でAND条件)でしょう。
・なぜエクセルシートに条件を入れているのか。アクセスのフォームなどに、まとめるのが普通では。
迷いや疑問はあるのですが。

投稿日時 - 2015-10-07 20:48:37

お礼

ご回答ありがとうございます。遅くなりすみません。
ご教示いただきました内容で確認してみます。ありがとうございます。

投稿日時 - 2015-10-10 00:17:42

ANo.4

どこで時間がかかているのかを
Debug.Print "1:" & Cstr(Timer)
Debug.Print "2:" & Cstr(Timer)
Debug.Print "3:" & Cstr(Timer)
を要所要所にいれて処理時間を確認してください。

見たところSQL文の実行以外は、瞬時に終わる命令ばかりです。
つまりデータの取得が遅い=テーブル構造がまずい(適切にキーやインデックスが設定されていない?)と、思われます。
もしキーやインデックスが設定されているのに遅い場合は、Accessの限界と諦めるしかありません。

投稿日時 - 2015-10-07 13:58:49

お礼

ご回答ありがとうございます。遅くなりすみません。
わかりました。試してみて確認してみます。ありがとうございます。

投稿日時 - 2015-10-10 00:16:58

ANo.3

今、対象とされているテーブルとテーブル構造が同じテーブルをコピペで作ります。
仮にテーブル名を抽出ベースとします。

Sub DSUM集計()
Application.ScreenUpdating = False
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mySQL As String
Dim cmd As ADODB.Command

Dim AA As Variant
AA = " 営業箇所" & Range("C13") & " AND 拒 IS NULL AND 販売伝票 <" & Range("D13") & " AND 品名 " & Range("E13") & " AND 得意先名 " & Range("F13") & " AND 請求先名 " & Range("G13") & " AND 出荷先名 " & Range("H13") & ";"

Set db = New ADODB.Connection

With db
  .Provider = "Microsoft.ACE.OLEDB.12.0;"
  .Properties("Data Source").Value = "\\▲▲▲\ACCESS.accdb"
  .Open
End With

mySQL = "DELETE * FROM 抽出ベース"
CN.Execute mySQL '既存レコード削除

mySQL = "INSERT INTO 抽出ベース" & _
      " SELECT * FROM 元テーブル名" & _
      " WHERE " & AA
CN.Execute mySQL '抽出ベーステーブルへのレコード更新
’とすれば以下
With Worksheets("補助計算")

mySQL = " SELECT SUM(受注数量) FROM 抽出ベース "
mySQL = mySQL & "WHERE 納入期日=" & Range("B13") ' & AA

・・・省略

となるのでAAの抽出コストが無くなりますから
少しは速くなるのかな・・・と考えた次第です。
抽出ベーステーブルの更新に時間が掛かり効果が無いかもしれませんが、
サンプルを作るわけにもいかないので当方では検証不可です。

ただ実際には5倍の処理
シートへの書き込み=4×5=20回くらい?
ごく大雑把に30秒÷20=1.5秒なので対策しても微々たる効果しかないかもしれません。
ネットワーク経由など環境に依存する部分もあるので…。

投稿日時 - 2015-10-06 22:17:20

お礼

ご回答ありがとうございます。遅くなりすみません。
ご教示いただきましたvbaをいちど試してみます。ありがとうございます。

投稿日時 - 2015-10-10 00:15:32

まだやってなくて、やれるのであれば・・・
各テーブルの「納入期日」にインデックス(重複あり)を追加でどうだろうか?

投稿日時 - 2015-10-06 10:01:16

お礼

ご回答ありがとうございます。遅くなりすみません。
インデックス設定やってみます。ありがとうございます。

投稿日時 - 2015-10-10 00:14:48

ANo.1

抽出のベースとなる
> AA = "AND 営業箇所" & Range("C13") & " AND 拒 IS NULL AND 販売伝票 <" & Range("D13") & " AND 品名 " & Range("E13") & " AND 得意先名 " & Range("F13") & " AND 請求先名 " & Range("G13") & " AND 出荷先名 " & Range("H13")
でテーブルを作成するわけには行かないのでしょうか。
(Accessファイルの作成者(貴方ではない?)が自身以外は不可侵・参照のみ許可だとダメですが…)

あと、
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = db
が繰り返し出てきますが最初だけで充分だと思います。

とりあえず。

投稿日時 - 2015-10-05 23:42:43

お礼

ご回答ありがとうございます。抽出のベースとなる部分のテーブル化についてですが、おっしゃっている意味は「その部分の条件内容が固定ならば、テーブルにしたら良いのでは?」ということでしょうか?(間違っていたらすみませんなのですが)そういう意味でのテーブル化は、希望内容に残念ながら沿いません。
私の言葉足らずで申し訳ございませんでしたが、抽出のベースとなる部分はエクセル側で条件を適宜変更するという運用想定をしているためです。。。

繰り返しの部分につきましては、試してみます。ありがとうございます。

投稿日時 - 2015-10-06 00:59:00

あなたにオススメの質問