Photo by chuttersnap on Unsplash
どうもマサヤです！
VBAを触って最初に動いたときは感動しましたよね！
でも、触り慣れてくるとある問題にぶつかります。
それは・・・・・・
VBAの実行速度が遅い！重い！・・・挙句の果てには応答なしのまま動かない。
データが多くなればなるほど遅く、重くなってしまいます。
特にセルを参照・計算しているVBAは顕著に遅くなります。
しかもVBAが遅い状況でExcelや他のアプリを触るとExcelがほぼ固まります。
そのため、実行中はPCをほぼ操作できません。
この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回程度であれば意味がない高速化方法ですね。
あと、気を付けたいのはセル範囲から配列へは高速化できたものの、配列からセルへ書き出す際に、一つ一つのセルに対して書き出すコードを書くと遅くなるので注意が必要です。
この場合は動画で使った下記のサンプルコードのように、配列からセル範囲へ一括で書き出すコードにすれば速く処理できます。
と、言ったものの一括のセル範囲に書き出すシーンはあまりないかもしれません）笑
ただ、セル範囲で書き出す方が間違いなく速いので意識してみましょう。
ツールのレイアウトを変えれるなら是非検討してみてくださいね。
コメント
ｍMｍさま
配列による高速化
活用できるとすばらしいですが、まだまだ理解できてません。
次のケースだと、どのように配列で書き換え出来ますか？
宜しくお願いします。
Sub ５行コピーする毎に１行開ける()
For i = 2 To 1000001 Step 5
With Worksheets(“Sheet1”)
.Range(.Cells(i, 1), .Cells(i + 4, 100)).Copy _
Worksheets(“Sheet1 (2)”).Table(i + (i – 2) / 5, 1)
End With
Next
End sub
かしゅさん
ブログを見ていただきありがとうございます！
配列を理解するのは、なかなか難しいですよね。
質問の内容から察するに、データが5行ごとで区切られていると思われます。
また、コピーして別シートへの貼り付ける処理をしたいと見受けられます。
ただ、コピぺの回数が20万回という尋常でない数字なので、データ構造をきれいにしたほうがいいと思います。
具体的には1行開けずにひと塊のデータとして扱うことを考える。
今のままで配列を使っても、20万回の参照が発生するのでそこまで高速になりません。
配列の置き換えといった意味で回答できずにすいません。
ｍMｍさま
ご返事ありがとうございます。かしゅ です
回数に関しては改善効果をベンチマークするための一例です。
1000回でも、200回でも構いません。
実際のデータは、行方向に５フィールドで１組のかたちで並んでます。
データ数は数百組とお考えください。
これらのデータに対して、任意の計算式を追加して６フィールド目を
追加したいのです。
編集する方法として、例えば「行の挿入」ではあまりに重た過ぎます。
なので別のシートを追加し、そちらに上の行から順番にコピーして
行った方が断然速いと考えて、今はそのような処理を行っています。
（まぁ実際断然速いです。。）
しかし、この処理を稼働しているＯＳがWin7ということも影響して
速度が安定しません。
１０秒以内で終わることもあれば１分かかるときもあり、もっと速く、
例えば一瞬で処理が終われる方法はないかと模索していました。
（Win10へ移行、という選択は今の段階では無しでお願いします）
配列を応用した圧倒的な速度改善、という術（すべ）で解消できるなら
是非ご教授してほしいです。
宜しくお願いします。
かしゅさん
結論から言うと、このデータ構造やしたいことを考えた場合、配列で一瞬で処理させるのはかなり難しいと思います。
実際どれ程のデータがあるかわかりませんが、配列化有効なのはセルへの参照回数が少ないときです。
5行で一組のデータで、6行目の計算式を入れるといったシーンでは、あまり効果は見込めないでしょう。
5行ではなく、全てのデータを一組として扱えるようにデータ構造を変えて、出力(ペースト)も少なくできれば、配列は効果はあります。
今のままでも調子が良いときは、10秒以内で処理が完了するとのことなので、本記事の冒頭に紹介している簡単に高速化をまだお試ししていないなら使って見てください。
ｍMｍさま
ご返事ありがとうございます。かしゅ です
やはり難しいですか、、
分かりました。
ありがとうございます。