●読者対象は以下のような状況を改善したい人
・エクセルVBAの処理時間が長い。
・最初は早かったがデータが段々と多くなり時間がかかるようになった。
●この記事の技をマスターすると
・8倍高速なエクセルVBA処理を作れる。
※実際は縮まらない処理があり、大体1/4くらいに落ち着くと思われる。
エクセルVBAで物を作っていてある程度規模が大きくなってくると必ず、処理時間の壁にぶつかる。
初心者向けの解説では1セルずつ処理していくのが基本となっている。この方法はとても遅い。
1セルずつでなく一気に処理すれば約8倍の速さで処理できる。
具体的な例を示そう。
A列、B列の1~1,048,576行まで値が入っている。
A列、B列の値を少し加工してC列、D列に入れる処理を考える。
↓
A列の値の頭に"c"を追加してC列に書き込み
B列の値の頭に"d"を追加してD列に書き込み
●まずは1セルずつ処理していく方法
Public Sub OneByOne()
Dim i As Long
Dim nLastRow As Long
' C,D列の値をクリア
Sheet1.Columns("C:D").Clear
Debug.Print Now & " start"
' 最終行を取得
nLastRow = Sheet1.Range("A1").End(xlDown).Row
For i = 1 To nLastRow
'A列の値を少し加工してC列にコピー
Sheet1.Range("C" & i).Value = "c" & Sheet1.Range("A" & i).Value
'B列の値を少し加工してD列にコピー
Sheet1.Range("D" & i).Value = "d" & Sheet1.Range("B" & i).Value
If i Mod 10000 = 0 Then
Application.StatusBar = i
DoEvents
End If
Next i
Debug.Print Now & " end"
Application.StatusBar = ""
End Sub
処理時間は160秒
セルの読み取りに2,097,152回シートにアクセスし、さらにセルに書き込むために2,097,152回シートにアクセスしている。
●次に2次元配列を使う方法
Public Sub twoD_array()
Dim i As Long
Dim nLastRow As Long
Dim v1 As Variant
' C,D列の値をクリア
Sheet1.Columns("C:D").Clear
Debug.Print Now & " start"
' 最終行を取得
nLastRow = Sheet1.Range("A1").End(xlDown).Row
' ■■■■■■■■■■■■■■■■■■■■■■■
' ■ 2次元配列にA,B列の内容を取得 ■
' ■ ※1 セル1個ずつ取得するよりも超高速! ■
' ■■■■■■■■■■■■■■■■■■■■■■■
v1 = Sheet1.Range("A1:B" & nLastRow)
' ■■■■■■■■■■■■■■■■■■■■■
' ■ 加工処理 ■
' ■ ※2 メモリ内での処理なので超高速! ■
' ■■■■■■■■■■■■■■■■■■■■■
For i = 1 To nLastRow
v1(i, 1) = "c" & v1(i, 1)
v1(i, 2) = "d" & v1(i, 2)
Next i
' ■■■■■■■■■■■■■■■■■■■■■■■
' ■ 2次元配列の内容をC,D列に挿入 ■
' ■ ※1 セル1個ずつ挿入するよりも超高速! ■
' ■■■■■■■■■■■■■■■■■■■■■■■
Sheet1.Range("C1:D" & nLastRow) = v1
Debug.Print Now & " end"
End Sub
処理時間は19秒
セルの読み取りに1回シートにアクセスし、さらにセルに書き込むために1回シートにアクセスしている。
ということで同じことをやっているにも関わらず処理時間は1/8以下になっている。
私は大体以下のステップで処理を作る。このように作れば高速なExcelVBA処理になる。
※場合によって①はなく2次元配列を自分で定義する場合もよくある。
①エクセルシートから2次元配列を作る
(1ステップ)
②2次元配列上で加工等を実施する
(メモリー上での操作なので高速)
③2次元配列をエクセルシートに書き戻す
(1ステップ)
時間のかかるエクセルシートへのアクセスがたったの2回である(これ重要)
エクセルシートへのアクセス回数はコストが高いので最小限にするべき。
細かいソースの解説はしないが、ソースをじっくり見ていただければわかることだろう。
●注意点
・セルの数がもっと膨大になるとエラーになる場合がある。その場合10万行ずつ処理する等の対応が必要な場合がある。
・この2次元配列を導入する以前にセル結合をまずなくすのが先決である。セル結合があるとこのやり方も本領発揮してくれません。
セル結合しない方がExcelVBAは超高速パフォーマンス! - engineer-kiyoの日記
実はセル結合ありまくりの状態からこの2次元配列を導入すると処理時間1/64も夢ではない。
事実8時間たっても終わらなかった処理が2分で終わるようになったこともある。←この時は他にもいろいろ改善したんだけども。
是非この記事を参考に高速なVBA処理を作り上げて貴重な時間を無駄にしないようにしてほしい。
直接教えてほしい方はツイッターから連絡ください。
→@EngineerKiyo