VBA・Excel 高速化したい！遅い、重いを配列で一気に解決！

Photo by chuttersnap on Unsplash

どうもマサヤです！

VBAを触って最初に動いたときは感動しましたよね！

でも、触り慣れてくるとある問題にぶつかります。

それは・・・・・・

VBAの実行速度が遅い！重い！・・・挙句の果てには応答なしのまま動かない。

データが多くなればなるほど遅く、重くなってしまいます。

特にセルを参照・計算しているVBAは顕著に遅くなります。

しかもVBAが遅い状況でExcelや他のアプリを触るとExcelがほぼ固まります。

そのため、実行中はPCをほぼ操作できません。

このVBAが遅い問題を解決するには配列が有効です。

今日は配列による高速化の方法を紹介しますよ！

配列化の前に、お手軽に出来る高速化も試してみませんか？

配列以外にも簡単にできる高速化があります。

こちらも併せて使えばより速くなりますよ！

VBA・Excel マクロを簡単に高速化する4つの方法！

簡単に高速化出来るコードあったよな～。マクロ作っているとたまに忘れることありますよね）笑 コード忘れちゃったからコードだけ知りたい！そんなアナタと私のための高速化できるコードを紹介していきます。 忘れた時にどうぞ！コピペしてお使いください。

mmm-program.com

2022.01.21

配列は本当に高速なのか？動画で確認

では、本題の配列による高速化に触れていきましょう！

まずはどれほど速度差があるのか動画で確認してください。

↑の動画で行っている処理は至ってシンプルです。

行1000×列1000に対して数字を書き出しているだけです。

これを配列化したものと、そうでないもの差を動画で確認できます。

最初のVBAは配列を使わず、セルを参照しているコードになります。

2つ目が配列を使ったコードになります。

速度差はご覧いただいたとおりです。

最初のVBA（セル参照）は18.07秒、2つ目のVBA（配列化）は1.33秒です。

実に、約18倍の速度差があります！

なぜ配列を使うと高速なのか？

ただ、数字を書き出すだけでなぜこれほど速度に差があるのか？

VBAが遅くなる要因としてセル参照の回数の多さがあります。

何を隠そうExcelにおいてセルの参照は動作として重い部類に入るんです。

今回、紹介した遅いコードは行1000×列1000に対して数値を書き出してます。

実にセル参照を100万回行ってます。

これに比べて速いコードは、セル参照を2回しかしていません。

約50万倍違います。

配列化が速くなることは必然ですね！

ちなみに今回使ったコードは下記になります。

◆ セル参照が多い、遅いコード ◆

Sub LowOutput() '遅いコードです。

For i = 1 To 1000
    For j = 1 To 1000
        Cells(i, j) = Int(Rnd(10) * 1000)
    Next
Next

End Sub

◆ 配列化した、速いコード ◆

Sub FastOutput() '配列を使った速いコードです。

my_array = Range("A1:ALL1000")

For i = 1 To UBound(my_array)
    For j = 1 To UBound(my_array, 2)
        my_array(i, j) = Int(Rnd(10) * 1000)
    Next
Next

Range("A1:ALL1000") = my_array

End Sub

コピペできる！配列を使った高速化コード

では、コピペできるコードを紹介します！

コードの内容はセル範囲を一括して配列に格納します。

Sub GetArray()

Dim my_array As Variant
Dim s_row, s_col As Long
Dim max_row, max_col As Long

s_row = 1  'データの開始行を設定
s_col = 1  'データの開始列を設定

'行列の最終行を取得
max_row = Cells(Rows.Count, s_col).End(xlUp).Row
max_col = Cells(s_row, Columns.Count).End(xlToLeft).Column

'セル範囲を配列に格納
my_array = Range(Cells(s_row, s_col), Cells(max_row, max_col))

End Sub

関数も作りました！

コードが汚れるから別関数にしたい・・・と私が思ったので作りました！

こちらのほうがスッキリしますね。あと、シート名も選べるように変更してます。

◆ 関数 ◆

Function GetArray(sheet_name, s_row, s_col)

Dim ws As Worksheet
Dim my_array As Variant
Dim max_row, max_col As Long

Set ws = Worksheets(sheet_name)

'行列の最終行を取得
max_row = ws.Cells(Rows.Count, s_col).End(xlUp).Row
max_col = ws.Cells(s_row, Columns.Count).End(xlToLeft).Column

'セル範囲を配列に格納
my_array = ws.Range(ws.Cells(s_row, s_col), ws.Cells(max_row, max_col))

'配列を戻り値へ
GetArray = my_array

'配列の初期化
Erase my_array

End Function

◆ 使い方 ◆

get_array = GetArray(ActiveSheet.Name, 1, 1)  'GetArray(対象シート名, 開始行, 開始列)

コピペしてアナタ好みにカスマイズしてご利用くださいね！

まとめ：より高速化の高みへ

今回の高速化方法をざっくりまとめると・・・

セル参照を極力減らすために配列に格納しましょう！

ということになります。

逆にセル参照が1回・2回程度であれば意味がない高速化方法ですね。

あと、気を付けたいのはセル範囲から配列へは高速化できたものの、配列からセルへ書き出す際に、一つ一つのセルに対して書き出すコードを書くと遅くなるので注意が必要です。

この場合は動画で使った下記のサンプルコードのように、配列からセル範囲へ一括で書き出すコードにすれば速く処理できます。

と、言ったものの一括のセル範囲に書き出すシーンはあまりないかもしれません）笑

ただ、セル範囲で書き出す方が間違いなく速いので意識してみましょう。

ツールのレイアウトを変えれるなら是非検討してみてくださいね。