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

Photo by chuttersnap on Unsplash

 

どうもmMmです!

 

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

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

 

それは・・・・・・

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

 

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

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

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

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

 

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

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

 

また、コピペで使えるコードも用意してます!
説明不要!という場合は目次からコードの箇所まで飛んでください。
自分好みにカスマイズしてお使いください。

 

 

スポンサーリンク

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

 

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

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

 

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

 

 

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

 

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

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

 

VBA 配列による高速化

 

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

行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回程度であれば意味がない高速化方法ですね。

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

 

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

 

Range(“A1:ALL1000”) = my_array

 

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

 

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

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

 

コメント