[VBA]全セルをVariant配列に入れて処理の高速化(配列化)

概要

大量のセルに数値の入ったExcelシートに対して計算処理などで、シートのセルから値を取り出す時に

hoge = Workdheets("Sheet1").Cells(1, 1)

のように、直接シート上からセルを参照するように書いている人が大半だと思います。

参照セルが数個程度であれば大して問題ないのですが、「A行全体のデータの標準偏差を計算する」とか「A行A列からBB行CC列目の全セルの合計値を計算する」のような、大量のセルに対して行う処理の場合は、シートに直接参照する時のオーバーヘッドがバカにならず結構な処理時間になってしまいがちです。

そこで、最初に全セルのデータをVariant配列に入れてしまう事で、参照先をシートから配列にして処理時間を大幅に削減するのがオススメです。
いわゆる「配列化」というヤツですね。

この記事では、実際に配列化する事でどの程度高速化できるのかを検証していきたいと思います。

実験

今回は、1以上50000以下の乱数を1000万個生成し、その分散を計算するマクロを使って、実行時間の違いを計測していきます。

まずは1000万個の乱数を生成しましょう。

Sub make_data()
    Dim i As Long, j As Long

    For i = 1 To 1000
        For j = 1 To 10000
            '乱数シード初期化
            Randomize

            Cells(i, j).Value = Int(50000 * Rnd + 1)
        Next j
    Next i
End Sub

はい、実験データが完成しました。

次に分散を求めるマクロを作りましょう。
分散は

• 全セルの平均値を計算する
• 全セルに対して「平均値との差の２乗」を求め、その合計値を計算する
• 上記の合計値をデータの総数で割る

事で求めることができます。

実装・測定

配列化なし

まずは配列化を使わずに分散を計算した場合の実行時間を計測してみましょう。

Sub Disp_clac()
    Dim i As Long, j As Long
    Dim Ave, Disp As Currency
    Dim StartTime, StopTime As Single

    '測定開始
    StartTime = Timer

    '平均値の計算
    Ave = 0

    For i = 1 To 1000
        For j = 1 To 10000
            Ave = Ave + Cells(i, j).Value
        Next j
    Next i

    Ave = Ave / 10000000

    '分散の計算
    Disp = 0

    For i = 1 To 1000
        For j = 1 To 10000
            Disp = (Cells(i, j).Value - Ave) ^ 2
        Next j
    Next i

    Disp = Disp / 10000000

    '測定終了
    StopTime = Timer
    StopTime = StopTime - StartTime

    '出力
    MsgBox ("分散は" + Str(Disp) + "。処理時間は" + Str(StopTime) + "秒でした。")
End Sub

合計値が11桁なので、今回は15桁まで扱えるCurrency型で平均値などを計算しています。
実行時間の計測は、http://www.k1simplify.com/vba/tipsleaf/leaf220.html を参考にさせていただきました。

結果[配列化なし]

配列化あり

次に、分散計算の前に配列化を行う場合の実行時間を計測してみます。

配列化の方法は

Dim Table As Variant
Table = Worksheets("Sheet1").UsedRange

が簡単です。
Worksheets("Sheet1").UsedRangeはRange型ですが、これをVariant配列に格納すると自動的に２次元配列として扱われるのです。この時、Cells(1, 1)の値はTable(1, 1)に入るように、配列が定義されます。
配列はVariant型ですので、文字列型が入っているセルがあっても問題ありません。

配列化を行ってから分散を計算するコードは以下のようになります。

Sub Disp_clac_2()
    Dim i As Long, j As Long
    Dim Ave, Disp As Currency
    Dim StartTime, StopTime As Single

    Dim Table As Variant

    Table = Worksheets("Sheet1").UsedRange

    '測定開始
    StartTime = Timer

    '平均値の計算
    Ave = 0

    For i = 1 To 1000
        For j = 1 To 10000
            Ave = Ave + Table(i, j)
        Next j
    Next i

    Ave = Ave / 10000000

    '分散の計算
    Disp = 0

    For i = 1 To 1000
        For j = 1 To 10000
            Disp = (Table(i, j) - Ave) ^ 2
        Next j
    Next i

    Disp = Disp / 10000000

    '測定終了
    StopTime = Timer
    StartTime = StopTime - StartTime

    '出力
    MsgBox ("分散は" + Str(Disp) + "。処理時間は" + Str(StartTime) + "秒でした。")
End Sub

配列化なしのコードとほとんど変わっていません。
Variant配列に格納した後、「cells(i, j).Value」を「Table(i, j)」に置換しただけです。

結果[配列化あり]

配列化なしが46秒だったのに対し、配列化ありでは4.0秒と1/10以下の時間で実行することができました。

まとめ

配列化を行うことで、VBAマクロの実行時間を大幅に減らすこと出来ました。
配列化は比較的簡単に実装できるプログラム高速化の一つですので、積極的に利用してみてください。