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

質問

• VBAのマクロを使用して、大量のセルの内容を参照し、照らし合わせる作業を高速化する方法について考えています。
• 普通はセルを直接参照するよりも、配列を使って処理を行った方が速くなると言われています。
• しかし、条件によってセルの色を変えたり削除したりする場合、配列のみでは処理ができず、速度低下が起こってしまいます。どのようにしてこの問題を解決できるでしょうか？

回答No.3

cj_mover

こんにちは。お邪魔します。

"配列のみで"というのはちょっと汲み取れませんけれど、
"配列を有効に使う"ことで処理を速くしたいってことなら色々方法はあると思います。
コレクションのプロパティ値を直接、配列で設定できるのは、
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を使わないのはやはり時間がかかるからですか？