読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

主にVBAネタを扱っているブログです。

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

2つのプログラムの違いは宣言時に()があるかどうかだけである。

Variant型変数でも問題なく動作するが、私は動的配列の方をオススメしたい。
Variant型変数を使った場合、結局その中身としてVariant型の動的配列を保持しているので、ひとつ余計にラッピングされてしまう。

分かりやすく説明するために2つのパターンをローカルウインドウで見てみた。
f:id:t-hom:20150830135743p:plain

arr1の方は、型がVariant/Variant(1 to 3, 1 to 3)となっており、Variant型の中にVariant1型配列が格納されているのが分かる。(実際には参照ポインタだと思う。)

arr2の方は、そのままVariant型の動的配列である。

プログラムの実行結果は変わらないので、Variant変数の方が好みだという方はそちらを使っても良いと思う。
ただ、内部動作としては動的配列を使った方が綺麗なので、こだわりが無いなら動的配列を使おう。

以上。

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。