エクセルVBAは2次元配列を使うと高速かつ便利

エクセルで大量のデータを扱う場合、シートの読み込みや書き込みで処理が非常に遅くなる場合がある。1000個程度のデータで問題なく動いているのに実際に使う数十万個のデータだと処理時間が長すぎて実用できないこともある。ここでは処理速度と使いやすさを意識したVBAプログラムのテクニックを紹介したいと思う。

エクセルのマクロやVBAと言うともう十数年と使われている言語であるため、様々な参考書が出ていたり、ネットで紹介されていると思う。ただ、あまり速度まで気にしてサンプルプログラムが書かれていることはあまりない。プログラムの組み方に関してはプログラマの好みでいいと思うが、VBAに関しては組み方によって処理速度が大きく変わるので、作るのであればなるべく最速で動くようにしたい。(エクセルと連携したVBAが特殊ではあるが、仕事などではよく使われるだろう)

現在のパソコンであれば処理速度を意識しなくてもいいんでは?と思うが、エクセルVBAに関してはあくまでもエクセルに付随するものであり、チューニングがされていなかったり、マルチスレッドで動作できなかったりと制限が大きい。データが多ければ多いほど処理時間が膨れるため、ユーザーにとっては使用感が悪くなる。

紹介することは基本的なことではあるが、知っていると知らないではプログラムの作り方が変わってくると思うので参考にしていただければと思う。特にデータベースなどの大量のデータを扱う場合や照合するプログラムを作る場合は効果が大きいと思う。

エクセルVBAで主に処理時間に影響するのは「シート、セルの操作」「繰り返し処理」である。これをいかに少なくするか、である。私のように他の言語からやっている場合、「シート=2次元配列」と見てしまい、セルを1つ1つ見に行くループをつくってしまいがちである。これは処理を遅くしている原因になる。セル1つ1つにアクセスしているのがセルの操作になるし、座標を移すのに繰り返し処理を行っているからだ。

これはセルの読み込み、書き込み全てに影響する。ではどうすれば解決できるかというと、シートを2次元配列にして扱い、読み書きを配列単位で行うことである。1つ1つのセルに対してではなく、Range(範囲)で一度に読み込み、書き込みするという方法である。こうすることによって余計なループもないし、1回のシート指定だけで済む。計算や検索を行うのにも一度メモリ(2次元配列)にしたほうが高速に処理が出来る。

プログラムの流れとしては、シートのデータを2次元配列に読み込み、そのデータで演算し、結果を2次元配列に格納し、シートに貼り付けるというのが高速なプログラムになると思われる。

では実際にプログラムを紹介する。まずはシート全体を2次元配列にするプログラムである。標準モジュールに下記Sheet2Arrayを記入する。内容としてはシート内の最終行列を取得し、Rangeの範囲内を2次元配列にするというものである。

'シートを配列に読み込む
Public Function Sheet2Array(BookName As String, SheetName As String) As Variant

    Dim RowNum As Double
    Dim ColNum As Double

    With Workbooks(BookName).Sheets(SheetName)
        RowNum = .Cells(Rows.Count, 1).End(xlUp).row
        ColNum = .Cells(1, Columns.Count).End(xlToLeft).Column
        Sheet2Array = .Range(.Cells(1, 1), .Cells(RowNum, ColNum))
    End With

End Function

使い方としてはメインフローのデータを読み込みたいところで、

Dim DataBase() As Variant
DataBase = Sheet2Array(ThisWorkbook.Name, "Sheet1")

のようにして使う。1行目で大きさ未定のDataBase配列(Variant型)を定義し、2行目でSheet2Array関数を呼び出してDataBase配列に値が入るようになっている。引数は1つ目がブック名(同じブック内であればThisWorkbook.Nameでブック名が取得できる)、2つ目がシート名である。

これでDataBaseが2次元配列になり、Sheet1の内容が丸々格納されているはずだ。見てお分かりの通り、ループ文はどこにも使われていない。シートの内容をかなり高速で配列化できる。

スポンサーリンク

この2次元配列から必要な部分を抜き取ったり、演算したりする。メモリ上にあるので高速に処理が可能だ。ただし、配列内をループさせるには、どこからどこまでループさせるか把握しておかないといけない。先ほどのDataBase配列はデータが全て詰まっている配列であるのでUbound関数を使っても良いが、どこまでデータが入っているかをチェックする関数を作成したので、そちらでチェックしておくと良いだろう。

下記の関数を標準モジュールに記入する。1列目、1行目をチェックし、空欄になったところから1引いたものをデータ数としている。

'配列の有効列数を求める
Public Function ArrayColumn(ArrayData As Variant) As Double

    Dim i As Double

    For i = 1 To UBound(ArrayData, 2)
        If ArrayData(1, i) = "" Then
            ArrayColumn = i - 1
            Exit For
        End If
    Next

    If ArrayColumn = 0 Then
        ArrayColumn = UBound(ArrayData, 2)
    End If

End Function

'配列の有効行数を求める
Public Function ArrayRow(ArrayData As Variant) As Double

    Dim i As Double

    For i = 1 To UBound(ArrayData, 1)
        If ArrayData(i, 1) = "" Then
            ArrayRow = i - 1
            Exit For
        End If
    Next

    If ArrayRow = 0 Then
        ArrayRow = UBound(ArrayData, 1)
    End If

End Function

これを使って2次元配列内をループさせてみる。

Dim RowNum As Double                              '行数
Dim ColNum As Double                              '列数
Dim i As Double                                   'ループ変数
Dim j As Double                                   'ループ変数

RowNum = ArrayRow(DataBase)                       '行数取得
ColNum = ArrayColumn(DataBase)                    '列数取得

For i = 1 To RowNum
    For j = 1 To ColNum
        Debug.Print DataBase(i, j)                '処理内容
    Next
Next

処理内容のところにIF文や出力内容を入れれば必要なデータのみを抜き出すことが出来る。for Next文を使っているがVBAではインデックス(数字を変数にしてループ)を使ったものだとFor Nextが一番速い。For Each Nextを使ってインデックスループさせると処理速度が急激に遅くなるので注意しよう。

最後に2次元配列をシートに出力する方法を紹介する。先ほどのArrayRow,ArrayColumn関数を使用しているので標準モジュールに追加しておく必要がある。内容としては配列の大きさを求めて、指定したシートに書き込みする処理である。

'配列をシートに書き込む
Public Sub Array2Sheet(ArrayData As Variant, BookName As String, SheetName As String)

    Dim RowNum As Double
    Dim ColNum As Double

    RowNum = ArrayRow(ArrayData)
    ColNum = ArrayColumn(ArrayData)

    With Workbooks(BookName).Sheets(SheetName)
       .Cells.Clear
       .Range(.Cells(1, 1), .Cells(RowNum, ColNum)) = ArrayData
    End With

End Sub

使い方としてはシートに出力したいところで

Call Array2Sheet(DataBase, ThisWorkbook.Name, "Sheet4")

上記のように呼び出す。これでSheet4にDataBase配列の内容が出力されているはずである。引数としては1つ目が出力する2次元配列、2つ目がブック名、3つ目がシート名である。読み込みと同様、ループ文は使っていないので高速で処理ができる。

ちなみに2次元配列であればArray2Sheet関数でデバッグのようにSheetに出力できるのでプログラム作成中にも役立つはずだ。注意する点としては通常配列は(0,0)から始まるため、このままシートに出力すると余分な空白の1行、1列が出来てしまう。これを防ぐため(わかりやすくするためにも)配列を作成する時に(1 to 1000, 1 to 1000)と言う風に範囲を指定してやると良い。

dim test1(1000,500)                             '行0~1000 列0~500 の範囲になる
dim test2(1 to 1000, 1 to 500)                  '行1~1000 列1~500 の範囲になる

自分で作成した2次元配列を出力したい場合は(1 to 1000, 1 to 1000)という感じで範囲を指定して定義しよう。するとシートと座標が一致してわかりやすい。

Excel VBAにはシートを使った検索や、ソート等々があるので高速化させるには付属の機能を使ったほうが高速になる可能性があるが、シートの読み書きをすると速度が落ちるし、プログラムも煩雑になる。結局は昔ながらの2次元配列をfor nextで回すという方法が一番確実で高速になると思われる。

さらに実践的に使いたい方は実践!エクセルVBAで配列を使いこなすを読んでいただけると実用的なプログラムが作れるようになるだろう。また、実践!エクセルVBAでクイックソートを使うを読んでいただけると大体のデータ処理が出来るようになるだろう。

Excel

Posted by kazutomo