VBA 高速化テクニック～配列とセルの相互転記

VBAでセルにデータを書き込む際に、1セルずつ処理すると結構時間がかかる。

以下のマクロは1000行、100列の範囲に行×列の計算結果を入力するもので、実行するとかかった時間が表示される。

Sub セルに直接書き込み()
    '開始時間をtに格納
    Dim t As Double: t = Timer()
    
    '自動更新、自動計算をOFF
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim i As Long, j As Long
    For i = 1 To 1000
        For j = 1 To 100
            ActiveSheet.Cells(i, j) = i * j
    Next j, i
    
    '自動更新、自動計算をON
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    '経過時間を表示
    Debug.Print Timer - t
End Sub

私の環境では約3.3秒かかった。
このやり方はあまり速くない。

VBAでは2次元配列をセル範囲に一括転記することができる。
以下のように一旦2次元配列を作成し、配列上に結果を作成してから一括でセル範囲に書き込むと速い。

Sub 配列を作成してから転記()
    '開始時間をtに格納
    Dim t As Double: t = Timer()
    
    '自動更新、自動計算をOFF
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim Arr(1 To 1000, 1 To 100)
    For i = 1 To 1000
        For j = 1 To 100
            Arr(i, j) = i * j
    Next j, i
    Range(Cells(1, 1), Cells(1000, 100)) = Arr
    
    '自動更新、自動計算をON
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    '経過時間を表示
    Debug.Print Timer - t
End Sub

セルの内容は先ほどのマクロと同じ結果になるが、こちらは約0.25秒で終わった。13倍も差が出る。

逆に、セルから配列への転記も可能だが、こちらはVariant型の動的配列を使用する必要がある。
以下のような固定長配列への転記はエラーが発生する。

Sub 固定長配列への転記はエラーになる｡()
    Dim Arr(1 To 1000, 1 To 100)
    Arr = Range(Cells(1, 1), Cells(1000, 100))
End Sub

なお、セルから固有型(LongやString等)の動的配列へも転記できない。

Sub 固有型動的配列への転記もエラーになる｡()
    Dim Arr() As Long
    Arr = Range(Cells(1, 1), Cells(1000, 100))
End Sub

以下のようにVariant型の動的配列を使うとセル範囲を転記することができる。
※As Variantは省略可能

Sub Variant型の動的配列を使ってセル範囲を2倍()
    Dim Arr() As Variant
    Arr = Range(Cells(1, 1), Cells(1000, 100))
    For i = 1 To 1000
        For j = 1 To 100
            Arr(i, j) = Arr(i, j) * 2
    Next j, i
    Range(Cells(1, 1), Cells(1000, 100)) = Arr
End Sub

ネットのサンプル等で、Variant型の動的配列ではなく、Variant型の変数を使用するやり方が紹介されてているケースがある。

このような書き方である。

Sub Variant型の変数を使ってセル範囲を2倍()
    Dim Arr As Variant
    Arr = Range(Cells(1, 1), Cells(1000, 100))
    For i = 1 To 1000
        For j = 1 To 100
            Arr(i, j) = Arr(i, j) * 2
    Next j, i
    Range(Cells(1, 1), Cells(1000, 100)) = Arr
End Sub