【VBA】配列を使ったマクロで高速化

いつもお世話になっております。

現在大量にセルの内容を参照し、照らし合わせるマクロを作っていますが、内容が膨大でマクロでも
時間がかかるので、何とか高速化できないか、考えております。


そこで、セルを直接参照するより、配列を使った方が速くなるという話を聞いたのですが、
配列を使うときに普通はVariantで宣言をして、セルの値のみ格納すると思います。
例えば、次のように、A列とB列を10000個のセルを1個ずつ比較し、一致した時にある作業をさせて
C列に出力するとします。

A = Range("A1:A10000")
B = Range("B1:B10000")
For i = 1 To 10000
For j = 1 To 10000
If A(i, 1) = B(j, 1) Then
A(i, 1) = A(i, 1) + 1
End If
Next
Next

Range("C1:C10000") = A

このようなマクロならいいのですが、値が一致した時にそのセルの色を変えるだとか削除するだとか行うと、次のようになり、配列のみでの構成ができなくなってしまいます

A = Range("A1:A10000")
B = Range("B1:B10000")
For i = 1 To 10000
For j = 1 To 10000
If A(i, 1) = B(j, 1) Then
Cells(i, 1).Interior.ColorIndex = 3
End If
Next
Next

前者は15秒、後者は19秒かかりました。上のプログラムは例であり、
実際に実務の方でマクロを実行すると数十分かかってしまいます。

何とか、条件によってセルの色変えたり削除するときに、速度を低下させずに配列のみで構成することは不可能でしょうか？

No.4 ベストアンサー 回答者： cj_mover 回答日時： 2012/10/03 01:07

こんにちは。

＃３、ｃｌです。

＃３の訂正が１点、
　　　補足が２点、
　　　お礼欄でのお尋ねに関するこたえを最後に書きます。

訂正■
>　　a = Range("A1:B" & nBtmRow).Value
>　　ReDim b(1 To UBound(a), 1 To 1)
>　　For i = 1 To nBtmRow
↑これ間違えました。正しくは↓
　　a = Range("A1:B" & nBtmRow).Value
　　ub = UBound(a)
　　ReDim b(1 To ub, 1 To 1)
　　For i = 1 To ub
たまたま Range("A1:... のように１行目から始まる範囲だから通ってしまいますが
実際は、配列の中身（インデックス）と行番地は相対ですから
あらためて UBound() 採らないとおかしいですね。失礼しました。
ちなみに、ub = UBound(a) ですが、これ a は２次元配列ですから、
省略しないで ub = UBound(a, 1) と書く方がベターでした。
（※ubはLong型です）

補足１■
> のように、ひとつずつ、処理するよりは
> 　　Range("A2,A4,A6,A8,A10").Interior.ColorIndex = 3
> のように、プロパティ設定を一度で済ませた方が
合わせて書かなければいけなかったのですが、
「オブジェクトの参照・取得とプロパティ設定を一度で済ませた方が」
です。
要は"時間が掛かる処理"はなるべく減らそうということなので片手落ちはまずかったですね。

補足２■
> 配列を使って、作業セルに作業用のフラグ配列を出力して
提示したサンプルコードでいうとフラグ配列とここで呼んでいるのは
[True | Empty]です。フラグ配列というと普通は[True | Flase]ですね。
でもExcelの .SpecialCells() にとって、フラグと呼べるのは
"セルの値のデータ型の相違"ですから、
[Boolean | Empty]という二値をVariant型の変数に格納した後で配列ごとセル範囲に出力している
と、ちゃんと説明するべきでした。
これが理由で Variant型 の変数配列を使うのですし、
逆に Variant型 でなければできないことをしている訳です。

＃３へのコメントへのレス■
> 1つ気になったのが、rangeオブジェクトでまとめて選択する方法として
> Joinの例をあげましたが、Unionを使わないのはやはり時間がかかるからですか？

端的にいうと「はい」その通りです。
少なくとも、
　　Set rng = Union(rng, rng2)
のように、ひとつずつ追加していくのは、割と時間掛かります。
とはいっても、Unionしないで、ひとつずつプロパティ設定をするよりは速いですから
処理を速くする方法のバリエーションのひとつに加えておいても良いと思います。
ただ、Union メソッド は、Range型の引数３０個まで指定できますから、
私のスタイル（つまり好み）として、ひとつずつ追加していくことは全くやらないだけです。
加えて言うと、例えば
　　Union(Range("A2"), Range("A4"))
なんて、普通の人でもばかばかしくて書かないですよね。
　　Range("A2,A4")　　　　（←↑厳密には少し意味が違いますが。）
の方がはるかに速いですし。
で、突き詰めていくと、（少し私の話になってしまいますが）
Range() に指定できる参照文字列は２５５文字まで目いっぱい使って
Union() に指定できるRange型の引数は３０個まで目いっぱい使って
３０個ずつまとめたものを更にUnionして
Areas３２７６７を超えないように手当てして、なんて、やっていくと
配列を引数にしてRangeオブジェクトを返す関数が出来て、
.SpecialCells とかExcelの一般機能使うより数倍速くすることは
可能だということを確かめたりもしました。

経験上、面倒だったのは、いつも数字の壁（仕様上の制限）だったので、
考えないで済むのなら、なるべく有利な環境に限定したものを
書いていった方が、取っ掛かりとしてはやり易いと思います。
（＃この↑３行で済む話を長々書いた感もありますが（汗）

もし興味があれば↓

For Nextマクロの高速化についてご教示ください。
http://oshiete.goo.ne.jp/qa/4007086.html

私の"ガラパゴスなＶＢＡ"の話よりも他の方のが参考になるかもしれません。
あ、↑の質問者さんも"高速化"探究し続けていた方なので色々ヒントとか
共通の疑問とか、見つけやすいかも知れませんね。

それでは。。。

この回答へのお礼

丁寧な補足と回答ありがとうございます。
回答者様のプログラム文が非常に参考になりました。

URL先の方も読まさせていただきます

お礼日時：2012/10/08 08:06

No.3 回答者： cj_mover 回答日時： 2012/10/02 02:52

こんにちは。

お邪魔します。

"配列のみで"というのはちょっと汲み取れませんけれど、
"配列を有効に使う"ことで処理を速くしたいってことなら色々方法はあると思います。
コレクションのプロパティ値を直接、配列で設定できるのは、
Rangeオブジェクトに関して言えば、単矩形範囲（ひとつの連続した四角い範囲）に対する
.Value プロパティと .Formula プロパティ、つまり、セルの値に関するもの、だけなので、
配列だけでどうにかするというよりは、Excelの一般機能の仕様に照らして
処理に掛かる時間を、より短くできる手段をまず選び、
その処理に配列を有効に使う方法はないか？というトライになるかと思います。

例えば、
　　For i = 2 To 10 Step 2
　　　　Cells(i, 1).Interior.ColorIndex = 3
　　Next i
のように、ひとつずつ、処理するよりは
　　Range("A2,A4,A6,A8,A10").Interior.ColorIndex = 3
のように、プロパティ設定を一度で済ませた方が
処理するセルが多いほど時間の差は大きくなります。
つまり目標とするべきは、沢山のAreasを持つRangeオブジェクトをどうやってひとつにまとめて取得するか
という点になります。
その意味では、"配列を有効に使える"方法が幾つかあります。
極端な例でいえば、（難度↓激高ですが）
Range("A2,A4,A6,A8,A10")というように参照文字列を作る過程で
ref = Join(Array("A2", "A4", "A6", "A8", "A10"), ",")
のように配列を使うことも考えられなくはないですよね（Array関数は配列を示す為の喩です）。
でも、
Join関数はExcel2000以降だし、Rangeの参照文字列はいつでも255文字まで、
RangeのAreasの上限は（昔調べただけなので最新バージョンは知りませんが）Excel2000で32767まで、
後述しますが、Range.SpecialCellsで取得できるRange.Areasの上限はExcel2003で8192まで、
（忘れてしまったまま、まだ調べてませんが最新バージョンはもっと多いですね↑）
Excelの仕様、バージョンごとの仕様、そういう具体的なひとつずつの条件
（というより"Excelの都合"？）に合わせる形で、トライの仕方が全く異なってきます。
なので、条件が曖昧なままでは切り口が見つけにくいでしょうから、
職場でExcel2003で使う人がいるとかいないとかバージョン情報や、
システム上、レコード数の上限が幾つに設定されているとか、
ひとつひとつ条件を確認してクリアにしていく所から始めた方がいいかも知れませんね。
.Sort .AutoFilter .AdvancedFilter .SpecialCells とか、
配列でフラグを出力した上で篩に掛ける、という方法には、使えそうなExcel一般機能
幾つかありますよね。
でもバージョンによっては量的な制限で足りない機能は使えないとか、あてにならないとか、
制限内で収まる保証があるならから、より速い手段を選べるとか。
こんな話、面白くないかも知れませんが、速く処理する為には、皆さんそうだと思いますけど
まめに情報収集して、細かな差異に拘り見逃さない態度で何度も挑戦して失敗して、、、
細かいこと、大切にして、続ける、体力、みたいのが、結局、物いうみたいです。

本題に戻って、
ひとつ例を挙げておきます。
「Ａ列とＢ列の値を比較して、同じであれば、その行を赤く塗りつぶします」
１００００行なら、バージョンの違いで動かないってことはない（Excel2000以降）ので、
配列を使って、作業セルに作業用のフラグ配列を出力して
.SpecialCells()でフラグがたったセル範囲を纏めて取得して
.Interior.Colorを一度で設定します。
Win7/Excel2010で試したら40000行で0.3～0.5sec.程度でした。
勿論これが最善って訳じゃないですし、どんな方法が適しているかは質問者さんにしか判断できないです。
ほんの一例ですが、配列を使うメリットは出せていると思います。

Sub 準備()
　　Const NROW = 10000 ' ←行数を指定
　　With Range("A1:B" & NROW)
　　　　.Formula = "=RANDBETWEEN(1,5)"
　　　　.Value = .Value
　　End With
End Sub

Sub Re7727219j() 'okg
　　Dim a, b
　　Dim nBtmRow As Long
　　Dim i As Long, j As Long

　　Application.EnableEvents = False
　　Application.Calculation = xlCalculationManual
　　nBtmRow = Cells(65536, 1).End(xlUp).Row
　　a = Range("A1:B" & nBtmRow).Value
　　ReDim b(1 To UBound(a), 1 To 1)
　　For i = 1 To nBtmRow
　　　　If a(i, 1) = a(i, 2) Then b(i, 1) = True 'a(i, 1) + 1 なら↓xlLogicalをxlNumbersに
　　Next
　　Application.ScreenUpdating = False
　　With Range("C1:C" & nBtmRow)
　　　　.Value = b
　　　　.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Interior.Color = vbRed
　　　　.Value = Empty
　　End With
　　Application.EnableEvents = True
　　Application.Calculation = xlCalculationAutomatic
End Sub

この回答へのお礼

とても参考になりました。ありがとうございます。

>>配列を使って、作業セルに作業用のフラグ配列を出力して
.SpecialCells()でフラグがたったセル範囲を纏めて取得して
.Interior.Colorを一度で設定します。

この方法は試せそうなのでやってみます。
しかし、8192というのはちょっと少ないですね。2010の方もあるのでそちらで実行してみるのも手ですけど



1つ気になったのが、rangeオブジェクトでまとめて選択する方法として
Joinの例をあげましたが、Unionを使わないのはやはり時間がかかるからですか？

お礼日時：2012/10/02 22:33

No.2 回答者： DreamyCat 回答日時： 2012/10/01 22:22

上記の例だと一致してもなお無駄に１００００までチェックを続けていますが

実際のものはただしく書かれているのでしょうか。

上記が実際に即した考え方のものであるなら１００００個の値について１００００列（行）の比較をすべて行っていることになりますが・・・・

この回答への補足

すみません。実際にはきちんと工夫はしていますのでさすがに10000×10000ではないです。ただ、配列は使っていない状態だったので、結構時間がかかってしまってはいます。
そこで配列を使おうと思ったのですが、完璧にfor文の中身を配列だけにできないか疑問に思い質問させていただきました

補足日時：2012/10/01 23:43

この回答へのお礼

そうですね。1～5万データあるファイルが10ファイルあって、比較対象がまた同じくらいありますね。
その2セットのファイルを比較するマクロを組んでます。

従って、高速化する方法を一つでも多く知っておきたいのです

お礼日時：2012/10/01 23:35

No.1 回答者： Hirorin_20 回答日時： 2012/10/01 21:38

>セルを直接参照するより、配列を使った方が速くなるという話を聞いたのですが、

理由は単純です。配列はメモリ上に取られるので早いのです。

>For i = 1 To 10000
>For j = 1 To 10000
1万回の繰り返しの中で1万回繰り返したら、1億回の繰り返しですよね。どうしても必要なら仕方ないですけど、回数は少なくしましょう。ちょっと知恵を絞ればできます。
例えば、ソートしておいてから比較すればかなり少なくなります。
あるいは合わせて2万個を1つの配列にしてソートすれば前後だけで一致をチックできるのでものずごく回数が減りますよね。

この回答へのお礼

あーすみません。それはすでにしています。
上のは配列に関することのみに要点を置いてるのでそこは端折りました。
(excelのソートの規則性がいまいちわからず四苦八苦してますが）

私は上記プログラム純粋に配列のみで可能かどうかを聞きたいです。

お礼日時：2012/10/01 23:33