スポンサーリンク

超パフォーマンス改善!遅いExcelVBA処理を8倍高速化!2次元配列技

●読者対象は以下のような状況を改善したい人

・エクセルVBAの処理時間が長い。

・最初は早かったがデータが段々と多くなり時間がかかるようになった。

 

●この記事の技をマスターすると

・8倍高速なエクセルVBA処理を作れる。

 ※実際は縮まらない処理があり、大体1/4くらいに落ち着くと思われる。

 

 

エクセルVBAで物を作っていてある程度規模が大きくなってくると必ず、処理時間の壁にぶつかる。

初心者向けの解説では1セルずつ処理していくのが基本となっている。この方法はとても遅い。

 

1セルずつでなく一気に処理すれば約8倍の速さで処理できる。

具体的な例を示そう。

 

A列、B列の1~1,048,576行まで値が入っている。

A列、B列の値を少し加工してC列、D列に入れる処理を考える。

f:id:engineer-kiyo:20210116190242p:plain

A列の値の頭に"c"を追加してC列に書き込み

B列の値の頭に"d"を追加してD列に書き込み

f:id:engineer-kiyo:20210116191815p:plain

●まずは1セルずつ処理していく方法

Public Sub OneByOne()

    Dim i As Long

    Dim nLastRow As Long

   

    ' C,D列の値をクリア

    Sheet1.Columns("C:D").Clear

 

    Debug.Print Now & " start"

   

    ' 最終行を取得

    nLastRow = Sheet1.Range("A1").End(xlDown).Row

 

    For i = 1 To nLastRow

        'A列の値を少し加工してC列にコピー

        Sheet1.Range("C" & i).Value = "c" & Sheet1.Range("A" & i).Value

        'B列の値を少し加工してD列にコピー

        Sheet1.Range("D" & i).Value = "d" & Sheet1.Range("B" & i).Value

       

        If i Mod 10000 = 0 Then

            Application.StatusBar = i

            DoEvents

        End If

    Next i

 

    Debug.Print Now & " end"

    Application.StatusBar = ""

End Sub

処理時間は160秒

セルの読み取りに2,097,152回シートにアクセスし、さらにセルに書き込むために2,097,152回シートにアクセスしている。

 

●次に2次元配列を使う方法

Public Sub twoD_array()

    Dim i As Long

    Dim nLastRow As Long

    Dim v1 As Variant

   

    ' C,D列の値をクリア

    Sheet1.Columns("C:D").Clear

 

    Debug.Print Now & " start"

   

    ' 最終行を取得

    nLastRow = Sheet1.Range("A1").End(xlDown).Row

 

    ' ■■■■■■■■■■■■■■■■■■■■■■■

    ' ■ 2次元配列にA,B列の内容を取得         ■

    ' ■ ※1 セル1個ずつ取得するよりも超高速! ■

    ' ■■■■■■■■■■■■■■■■■■■■■■■

    v1 = Sheet1.Range("A1:B" & nLastRow)

 

    ' ■■■■■■■■■■■■■■■■■■■■■

    ' ■ 加工処理                             ■

    ' ■ ※2 メモリ内での処理なので超高速! ■

    ' ■■■■■■■■■■■■■■■■■■■■■

    For i = 1 To nLastRow

        v1(i, 1) = "c" & v1(i, 1)

        v1(i, 2) = "d" & v1(i, 2)

    Next i

 

    ' ■■■■■■■■■■■■■■■■■■■■■■■

    ' ■ 2次元配列の内容をC,D列に挿入         ■

    ' ■ ※1 セル1個ずつ挿入するよりも超高速! ■

    ' ■■■■■■■■■■■■■■■■■■■■■■■

    Sheet1.Range("C1:D" & nLastRow) = v1

 

    Debug.Print Now & " end"

End Sub

 処理時間は19秒

セルの読み取りに1回シートにアクセスし、さらにセルに書き込むために1回シートにアクセスしている。

 

 ということで同じことをやっているにも関わらず処理時間は1/8以下になっている。 

 

私は大体以下のステップで処理を作る。このように作れば高速なExcelVBA処理になる。

※場合によって①はなく2次元配列を自分で定義する場合もよくある。

①エクセルシートから2次元配列を作る

 (1ステップ)

②2次元配列上で加工等を実施する

 (メモリー上での操作なので高速)

③2次元配列をエクセルシートに書き戻す

 (1ステップ)

 

時間のかかるエクセルシートへのアクセスがたったの2回である(これ重要)

エクセルシートへのアクセス回数はコストが高いので最小限にするべき。

 細かいソースの解説はしないが、ソースをじっくり見ていただければわかることだろう。

 

 ●注意点

・セルの数がもっと膨大になるとエラーになる場合がある。その場合10万行ずつ処理する等の対応が必要な場合がある。

 ・この2次元配列を導入する以前にセル結合をまずなくすのが先決である。セル結合があるとこのやり方も本領発揮してくれません。

セル結合しない方がExcelVBAは超高速パフォーマンス! - engineer-kiyoの日記

  実はセル結合ありまくりの状態からこの2次元配列を導入すると処理時間1/64も夢ではない。

事実8時間たっても終わらなかった処理が2分で終わるようになったこともある。←この時は他にもいろいろ改善したんだけども。

 

是非この記事を参考に高速なVBA処理を作り上げて貴重な時間を無駄にしないようにしてほしい。

 

直接教えてほしい方はツイッターから連絡ください。

→@EngineerKiyo