Excel VBAで大きなループを早く処理するマクロを作ろう[variant]

2019年7月15日

妹「んしょ、んしょ」
兄「VBAか。どれどれ」

Public Sub SpeedCheck1()
    Dim lData(1000, 1000, 3) As Long
    Dim lsta As Long
    Dim lend As Long
        For i = 1 To 1000
        For ii = 1 To 1000
            lData(i, ii, 1) = ii + i
        Next
    Next
    For i = 1 To 1000
        For ii = 1 To 1000
            Cells(i, ii).Value = lData(ii, i, 1)
        Next
    Next
End Sub

兄「これ、すごく遅いんじゃないか？」
妹「11秒くらいかな。1000*1000。100万件の書き込みだけど、まあ我慢できなくもないよ」
兄「これはなんで遅いか解る？」
妹「Cellsのループでしょ？やたらと重いし……。VBAの限界かな」
兄「違う、それはVBAの限界じゃない……。よく、『C#ならもっと早いんですけどね？システム作りません？ 』 とか言ってシステム開発へ誘導する人もいるが、大抵はVBAコードがダメなんだよ」
妹「ならなんで遅いの？」

兄「まず、Cellsのセルオブジェクト。これは色だったり文字位置だったり、と色々な情報が入っている。そこは使わないにしても、Valueプロパティに対して書き込みをするから、オブジェクトにはプロパティが含まれているんだよね」
妹「ふーん。でもCellsを使うと遅いならエクセルのデータってマクロで書けないんじゃないの？やっぱりVBAの限界だよね」

兄「こういう時は、variant型を使ってデータを一回逃がすといいんだ」
妹「variant型って学校だと遅いから使うなって言ってたよ、お兄ちゃんもまだまだだね！」
兄「……実際にやってみたら解るよ」

Declare Function GetTickCount Lib "Kernel32" () As Long
'11625
Public Sub Imouto()
    Dim lData(1000, 1000, 3) As Long
    Dim lsta As Long
    Dim lend As Long
        For i = 1 To 1000
        For ii = 1 To 1000
            lData(i, ii, 1) = ii + i
        Next
    Next
    lsta = GetTickCount()
    For i = 1 To 1000
        For ii = 1 To 1000
            Cells(i, ii).Value = lData(ii, i, 1)
        Next
    Next
    Range("A1") = GetTickCount() - lsta
End Sub

Declare Function GetTickCount Lib "Kernel32" () As Long
'735 ms
Public Sub Aniki()
    Dim lData(1000, 1000, 3) As Long
    Dim lsta As Long
    Dim lend As Long
        For i = 1 To 1000
        For ii = 1 To 1000
            lData(i, ii, 1) = ii + i
        Next
    Next
    '普通に書き込んだ時
    lsta = GetTickCount()
    Dim var As Variant
    var = Range("A1:ALL1000")
    For i = 1 To 1000
        For ii = 1 To 1000
            var(i, ii) = lData(ii, i, 1)
        Next
    Next
    Range("A1:ALL1000") = var
    Range("A1") = GetTickCount() - lsta
End Sub

妹「あれ……結果が同じなのに、なんで私のが11秒かかるのにお兄ちゃんのは1秒以下で終わってるの？」
兄「variantに移した事で、内部メモリを使ってループがまわるから、その分処理時間が短くなるんだよ」
妹「詳しく……」

var = Range("A1:ALL1000")　'←　一回、レンジをvariant型の変数に移す事でメモリに展開される
For i = 1 To 1000
         For ii = 1 To 1000
             var(i, ii) = lData(ii, i, 1)　'← メモリからメモリへのコピーだから処理速度がかなり早くなる
         Next
Next
Range("A1:ALL1000") = var　'←メモリから一気にレンジを上書きする事でループが無くなり処理が一気に終わる。

兄「ポイントをまとめるとこうかな」
妹「やった！これでマクロが速くなるね！」
兄「ただ、あんまりにも大きな配列を扱うとメモリが足りなくなるんだ。その場合は、半分ずつに分けたりしないといけないね」
妹「メモリ不足？」

兄「↑こういうのが出たら大人しくRangeの位置を少量ずつずらして処理する事になるね」
妹「ソースコードが面倒臭くならない？」
兄「いや、これが出るようなエラーなら待ち時間が何十分ってレベルにまで膨れ上がってる処理だから、マクロとしては使いづらくなってるよ。大人しく少量に分けた方がいいね。それかメモリを大量に積めばいけるよ」
妹「……面倒だからメモリを追加しようかな」