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

質問

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

※別ファイルのAccessファイルをデータベースとして、「今年」テーブルと、「前年」テーブルそれぞれから条件抽出して、受注数量を合計させるものです。
※SQLのVBAです。なお、実際ファイルのVBAは、以下記述の５倍量あります（内容は、セル範囲が違うだけで同じ。現在速度：約３０秒）。
※「配列」というのを活用すれば速くなるようなのですが、理解できませんでした。。。
ーーーー【以下、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
ーーーーーーーーーーーーーーーーーーーー
以上

回答No.5

imogasi

自信ないですが、気になったもので、１意見として、言わせてください。
今年データー＞シート１
去年データ－＞シート２　に
（エクセルシートにデータを）インポートします。ＶＢＡで遣っても、１０行以内のコードで、できるでしょう。
ーー
これで、なにがしかのタイム（作業時間）をロスしますが、処理（下記Ｅｘｃｅｌシートでの処理）が、辛抱できる範囲内なら、下記のこの案もあり得るのでは。
ーー
今ＡＤＯでやっているのは
・今年データシートと昨年データシートの２種類データに対し、検索し集計する
すなわち
・－－今年シート－－－－｜ーーー昨年シート
納入期日　Ｂ１３　Ｂ１４　Ｋ１４　Ｋ１５
ーーーーーーーーーーーーーーーーーーーーー
営業所　　Ｃ１３
販売伝票　Ｄ１３　左に　　左に　左に　左に
品名　　　Ｅ１３　同じ　　同じ　同じ　同じ
得意先　　Ｆ１３
請求先　　Ｇ１３
出荷先名　Ｈ１３
が条件で、条件の内容を規定している。
すなわち（アクセスとは別の）シートのセルを参照して、データが条件と一致するか見る。
これはユーザーフォーム等で１セットの条件に、見やすいように、まとめられると思う。
他のソフトにまたがる処理でなくなるので完了処理が早くなると思う。
条件が１行内の複数列が、条件を満たしているかどうかを判別するタイプのもので、レコード間（エクセルの場合で言えば、違った行データ）の関連を気にする性格の検索ではないので幸い（簡単）なわけです。
ーー
一方、エクセルのＶＢＡコード
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
（１０列Ｘ１００００行の仮のデータで実行してみた）
のような処理が可能になるので、質問のＡＡの部分にある条件をエクセルＶＢＡコードで
組めば簡単ではないかな。この際、ごく初歩的な、総なめ法を使うのです。
これは
http://officetanaka.net/excel/vba/speed/s11.htm
のような記事があったのが、頭にあったからです
c = Worksheets("Sheet1").Range("a1:j10000")の１行で１０Ｘ１００００のセルデータが２次元配列的になってくれる。
ｃはVariant変数で、質問の「＞配列」というのを活用すれば速くなるようなのですが」から思いついたのですが。
会社でVBAを組むことは許されているようですし。
ＶＢＡコードでなく、ＳＵＭＩＦＳ関数でできるかもしれない。
http://www.becoolusers.com/excel/sumifs.html
ーーー
・データ量（数、１００万を超えるのでしょうか？）が質問に書いてない
・ＳＱＬ文のＡＡの部分で、「営業箇所」などのあとに＝が必要ないですか。
・小生も自信を持って本件書いてない。おかしければ無視してください。
・エクセルＶＢＡの（ＳＱＬをつかわない）プログラムを作りなおすのは、面倒ですが
　総当たり法のロジックでやるなら簡単なプログラム（ＩＦ文でＡＮＤ条件）でしょう。
・なぜエクセルシートに条件を入れているのか。アクセスのフォームなどに、まとめるのが普通では。
迷いや疑問はあるのですが。

お礼

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

回答No.4

Hayashi_Trek

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

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

お礼

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

回答No.3

NotFound404

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

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

ただ実際には５倍の処理
シートへの書き込み＝４×５＝２０回くらい？
ごく大雑把に３０秒÷２０＝１．５秒なので対策しても微々たる効果しかないかもしれません。
ネットワーク経由など環境に依存する部分もあるので…。

お礼

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

回答No.2

BarcodeMaster

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

お礼

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

回答No.1

NotFound404

抽出のベースとなる
> 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
が繰り返し出てきますが最初だけで充分だと思います。

とりあえず。

お礼

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

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