[Excel で VBA] シートへの高速なアクセス

前ページにおいて、VBA から Excel のシート (表) にアクセスする方法を学んだ。

通常の用途ではこれで構わないのだが、研究などで書き込むセルの数が増えるとどんどん実行速度が遅くなってしまう。
それを避けるためのテクニックをここでは紹介しよう。

画面更新を抑制する
配列に貯めたデータを直接書き込む
セルへの書き込み時間の比較
セルの内容を配列に読み込む

「Excel / OpenOffice で学ぶフーリエ変換入門」では本章で解説するセルアクセス方法を用いたマクロを
ソース閲覧可能な形で収録しております。

画面更新の抑制

まず、セルへのアクセスを高速化するための常道として、画面更新を抑制することが多くの場合用いられる。
これは、セルへ値を書き込むのが終るまで画面更新を抑制しておき、
書き込み終了後に画面更新を再開するというもので、簡単であるがそれなりの効果がある。

前ページの「Sin 関数の値をセルに書き込む」という例で効果を確認してみよう。
「プログラムを書き始めるまでの準備」を参考に以下の VBA プログラムを実行してみて欲しい。

Sub prog11_5()
    fillfunc -3.14, 3.14, 50000
End Sub

Sub fillfunc(x1 As Double, x2 As Double, nd As Long)
Dim n As Long
Dim x As Double, y As Double, dx As Double
    dx = (x2 - x1) / nd

    Application.ScreenUpdating = False  ' 画面更新抑制
    For n = 0 To nd - 1    ' nd 個のデータを書き込む
        x = x1 + dx * n
        y = Sin(x)
        Sheet1.Cells(n + 1, 1).Value = x
        Sheet1.Cells(n + 1, 2).Value = y
    Next n
    Application.ScreenUpdating = True  ' 画面更新抑制解除
End Sub

このプログラムは -3.14 ～ 3.14 までの sin(x) の値を 50000 個のセルに書き込むプログラムである。

かかる時間の違いがわかりやすくなるよう、書き込むセルの数をわざと多くしている。

赤い字で表示された「Application.ScreenUpdating」に関する 2 行が画面更新に関する設定である。
セルへの書き込みを始める前に Application.ScreenUpdating に False をセットして画面更新を無効にし、
書き込み終了後に Application.ScreenUpdating に True をセットして画面更新を有効に戻してことがわかる。

手元のコンピュータでは

ScreenUpdating の設定なし (画面更新有効)	2.671875[秒]
ScreenUpdating の設定あり (画面更新無効)	2.546875[秒]

のように、ScreenUpdating の設定を行うことで若干の時間が短縮された。
わずかな時間に思えるかもしれないが、Excelのバージョンや、PCの性能によってこの差が大きくなることもあるので注意が必要である。

配列に貯めたデータを直接書き込む

さらに効果的なのが、「配列に貯めたデータを直接書き込む」という方法である。

「多次元配列」、「セル範囲の指定 (Range)」等、これまで取り扱っていない内容を含んでいるが、
学ぶ価値の高い方法であるのでここで取り上げる。

これまでと全く同じ例をこの方法で実行したのが以下である。

Sub prog11_5()
    fillfunc -3.14, 3.14, 50000
End Sub

Sub fillfunc(x1 As Double, x2 As Double, nd As Long)
Dim n As Long
Dim x As Double, y As Double, dx As Double

Dim celldata() As Variant
ReDim celldata(nd - 1, 1)   ' 0...nd-1 および 0...1 で定義された二次元配列を確保

    dx = (x2 - x1) / nd
    
    For n = 0 To nd - 1     ' nd 個のデータを書き込む
        x = x1 + dx * n
        y = Sin(x)
        celldata(n, 0) = x     ' データをあらかじめ二次元配列に全て格納
        celldata(n, 1) = y     ' 配列の添字は 0 から用いていることに注意
    Next n

    Application.ScreenUpdating = False
    
    Range(Cells(1, 1), Cells(nd, 2)) = celldata  ' 実際のセルへの書き込み

    Application.ScreenUpdating = True

    Erase celldata   ' 確保した二次元配列を解放
End Sub

ポイントは赤字で示されている。まず、データを二次元配列にあらかじめ全て格納しておく。
なお、１列にしか書き込まない場合でも「ReDim celldata(nd - 1, 0)」などとして二次元配列を確保してデータを格納する必要がある。

そして、これを一気に書き込みたいセル範囲に書き込むのである。

実際に実行してみると、一瞬で処理が終了することがわかる。次節のプログラムで時間を比較してみよう。

セルへの書き込み時間の比較

以下に挙げるやや長いプログラムを実行すると、異なるセル書き込み方法にかかる時間を計測することができる。
みなさんも「プログラムを書き始めるまでの準備」を参考にコピー＆貼り付けにより実行してみて欲しい。

手元のコンピュータでは、以下のように配列を丸ごと書き込む方法が圧倒的に高速であることがわかった。
多少面倒でも、配列にデータを格納してからセルに書き込む価値は十分あると言えるのではないだろうか。

画面更新有効: 2.59375[秒]

画面更新無効: 2.53125[秒]

配列丸ごと書き込み: 0.0859375[秒]

Sub prog11_5()
    fillfunc -3.14, 3.14, 50000
End Sub

Sub fillfunc(x1 As Double, x2 As Double, nd As Long)
Dim n As Long
Dim x As Double, y As Double, dx As Double

Dim t1 As Double, t2 As Double

Dim celldata() As Variant
ReDim celldata(nd - 1, 1)   ' 0...nd-1 および 0...1 で定義された二次元配列

    dx = (x2 - x1) / nd
    
    For n = 0 To nd - 1   ' nd 個のデータを書き込む
        x = x1 + dx * n
        y = Sin(x)
        celldata(n, 0) = x
        celldata(n, 1) = y
    Next n
    
    t1 = Timer
    For n = 1 To nd
        Sheet1.Cells(n, 1).Value = celldata(n - 1, 0)
        Sheet1.Cells(n, 2).Value = celldata(n - 1, 1)
    Next n
    t2 = Timer
    MsgBox ("画面更新有効: " & t2 - t1 & " [秒]")

    Application.ScreenUpdating = False
    t1 = Timer
    For n = 1 To nd
        Sheet1.Cells(n, 1).Value = celldata(n - 1, 0)
        Sheet1.Cells(n, 2).Value = celldata(n - 1, 1)
    Next n
    t2 = Timer
    Application.ScreenUpdating = True
    MsgBox ("画面更新無効: " & t2 - t1 & " [秒]")

    Application.ScreenUpdating = False
    t1 = Timer
    Range(Cells(1, 1), Cells(nd, 2)) = celldata
    t2 = Timer
    Application.ScreenUpdating = True
    MsgBox ("配列丸ごと書き込み: " & t2 - t1 & " [秒]")
End Sub

セルの内容を配列に読み込む

なお、配列をセルに書き込む方法と対になる、「セルの内容を配列に読み込む」方法についても簡単に触れておこう。
こちらも1セルずつ読み取る方法に比べて圧倒的に高速である。

以下の通りであるが、読み取られた配列は添字が 1 から始まることに注意しよう。

Dim celldata As Variant
celldata = Range(Cells(1, 1), Cells(2, 3)).Value

' データが配列に以下のように格納される
' celldata(1,1),  celldata(1,2),  celldata(1,3)
' celldata(2,1),  celldata(2,2),  celldata(3,3)

←シートへのアクセス／微分方程式の数値的解法 (1) Excel のみの方法→

Excel で学ぶ Visual Basic for Applications (VBA)に戻る