VBAで作ったマクロの高速化①　配列を使う

せっかくエクセル作業の自動化プログラムを作ったのに、実務で使ってみたら思ったより時間がかかったなんてことはありませんか？今回はVBAで作ったマクロの高速化の手段として配列を使う方法を紹介します。

配列を使ってVBAの実行を高速化する

配列を使ってVBAの実行を高速化する

VBAで作成したマクロの処理に時間が掛かる理由は大きく分けて２つあります。

セルの書き込みに時間がかかる
繰り返すことを繰り返していて、トータルでみると何百万回も計算している

今回は前者の対策をします。

セルの書き込みに時間が掛かる例

大量のセルに書き込みを行うと処理に時間が掛かります。事件してみます。下のプログラムを実行すると環境によって違いますが５秒～１５秒の間くらいの時間が掛かります。

Sub 時間計測_1()
    
    Dim i As Long, j As Long
    
    Debug.Print Time
    
    For i = 1 To 100
        For j = 1 To 100
            Cells(i, j) = i * j
        Next j
    Next i
        
    Debug.Print Time
    
End Sub

100 x 100の掛け算表を作成しましたので、10,000個のセルに書き込んだことになります。

私の実行環境では約５秒かかりました。10,000セルで５秒ということは似たような作業を何度か行ったり、行数が増えたり、あるいはその両方をさらに数回繰り返すとなると許容できない時間がかかってくることになります。

ワークシートが見える状態で実行すると、次々にセルに書き込む様子を見ることができます。逆に言うと、目に見える程度の速さしか出ていないことになります。

配列を使ってセルへの書き込み回数を1回にする

配列とは

配列を使ったことがない方のために簡単に配列を説明します。配列とは一つの変数の中に複数の値を格納することです。配列の詳しい説明についてはVBA 配列を徹底的に解説する (全5回)　①イメージをつかむにて解説をしています。しかし、今回の高速化で使い配列については配列を詳しく理解する必要はありません。エクセルシートをそのまま一つの配列に入れたのと同じ感覚で操作できるためです。ただし、シートそのものを格納するわけではありません。配列に代入する範囲の中の値のみを格納します。

実際に配列にシートの範囲の値を代入して具体的に中身を確認した方が理解しやすいので、早速具体的なサンプルを見ていきましょう。

配列を使った高速の具体的なサンプル

それでは、配列を使った高速化を試みます。セルに書き込むところに時間がかかるので、掛け算は配列の中で行い、最後に一回だけシートにペタッと貼り付けます。

普通、範囲を変数に格納する時は、オブジェクトを入れる時のルールでSetを使いますが、今回は値のみを入れる為、Setは不要です。エクセルさんが気を利かせて勝手に二次元配列に置き換えてくれます。このため、シートに書き込むのと同じ感覚で取り扱え、とても便利です。ちなみに、私は心の中ではこの配列の事を「脳内エクセルシート」と呼んでいます。

上のコードを配列を利用したものに書き換えます。

Sub 時間計測_2()
    
    Dim i As Long, j As Long
    Dim arr As Variant '100ｘ100の掛け算表を格納する変数　’★
    
    Debug.Print Time
    
    arr = Range(Cells(1, 1), Cells(100, 100)) 'Setをつけずに範囲を変数に入れる　’★
    
    For i = 1 To 100
        For j = 1 To 100
            arr(i, j) = i * j   '★
        Next j
    Next i
    
    '配列の値ををシートに戻す。範囲を配列にした式の左右を逆にしただけ
    Range(Cells(1, 1), Cells(100, 100)) = arr    '★　
        
    Debug.Print Time
    
End Sub

★のコメントを付けたところが変更点です。普通、VBAで配列を使う場合はVariant型で変数を定義するときに変数の後にカッコをつけます。これが配列である事の合図です。しかし、カッコをつけずにただのVariant型の配列として変数を定義し、SetをつけずにRangeをを格納すると、勝手に二次元配列となります。

End Subの直前でStopと書いてマクロを止め、イミディエイトウィンドウで表示させると下のようになります。

インデックス番号が１から始まるのでワークシートでCells( i , j )と書くのと同じ感覚でarr( i , j )と書く事ができます。このプログラムを実行させると無事に1秒以内で処理を完了する事ができました。

VBAが遅いのではなく、エクセルが重い

よく「VBAは処理が遅いと言われていますが、VBAが遅いわけではありません*1。エクセルは誰でも便利に使えるよう、セルに列の幅や行の高さ、文字の色、背景の色など様々なプロパティ（属性、性質、特徴のようなもの）を持たせていて、それをコンピュータが律義に画面に表示しているから処理に時間が掛かっていました。

VBAによるプログラム高速化の一つの肝は、相手コンピューターが余計な処理をしなくて済むようにしてやればよい、と言う事です。

配列を使って必要な計算を配列内で実行する事により、一回一回シートに書き込む必要がありません。また、それを描画する必要もありません。このように不要な処理をさせないことによって、本来の計算速度を発揮できます。