Excel VBA はセルへのアクセスがとても遅い

概要

Excel VBAでセルに値をゲット、セットするのに時間がかかるのでその検証。
自分は使いやすいのでtest3の方法をよく使う。

結論

種類	方法
test1	100万個のセルに1個ずつ値をセット
test2	100万個のセルにまとめて値をセット
test3	variant型の配列を用意してまとめて値をセット

方法	実行時間
test1	17.44sec
test2	0.56sec
test3	2.61sec

コード

test1

test1

Sub test1()
    Dim t As Single
    t = Timer
    
    With Sheet1
        Dim i As Long
        Dim j As Long
        
        'セル1個ずつに値をセット
        For i = 1 To 1000
            For j = 1 To 1000
                .Cells(i, j) = "a"
            Next
        Next
    End With
    
    Debug.Print "test1:" & Timer - t & "sec"
End Sub

test2

test2

Sub test2()
    Dim t As Single
    t = Timer
    
    With Sheet1
        'まとめてセルに値をセットする
        .Range(.Cells(1, 1), .Cells(1000, 1000)) = "a"
    End With
    
    Debug.Print "test2:" & Round(Timer - t, 2) & "sec"
End Sub

test3

test3

Sub test3()
    Dim t As Single
    t = Timer
    
    With Sheet1
        Dim i As Long
        Dim j As Long
        
        '2次元配列が確保される。添え字は1～
        Dim var As Variant
        var = .Range(.Cells(1, 1), .Cells(1000, 1000))
        
        '変数へのアクセスなので1個ずつでも遅くない
        For i = LBound(var, 1) To UBound(var, 1)  '1 To 1000
            For j = LBound(var, 2) To UBound(var, 2)  '1 To 1000
                var(i, j) = "a"
            Next
        Next
        
        'セルに値をセットする
        .Range(.Cells(1, 1), .Cells(1000, 1000)) = var
        
    End With
    
    Debug.Print "test3:" & Round(Timer - t, 2) & "sec"
End Sub

まとめ

大したことしてないはずなのに、えらく重たい時は見直してみるといいかもしれない。