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

 

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

 

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

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

 

コメント

  1. かしゅ より:

    mMmさま

    配列による高速化
    活用できるとすばらしいですが、まだまだ理解できてません。

    次のケースだと、どのように配列で書き換え出来ますか?
    宜しくお願いします。

    Sub 5行コピーする毎に1行開ける()

    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

    • mMm より:

      かしゅさん

      ブログを見ていただきありがとうございます!

      配列を理解するのは、なかなか難しいですよね。
      質問の内容から察するに、データが5行ごとで区切られていると思われます。
      また、コピーして別シートへの貼り付ける処理をしたいと見受けられます。

      ただ、コピぺの回数が20万回という尋常でない数字なので、データ構造をきれいにしたほうがいいと思います。
      具体的には1行開けずにひと塊のデータとして扱うことを考える。

      今のままで配列を使っても、20万回の参照が発生するのでそこまで高速になりません。
      配列の置き換えといった意味で回答できずにすいません。

      • かしゅ より:

        mMmさま

        ご返事ありがとうございます。かしゅ です

        回数に関しては改善効果をベンチマークするための一例です。
        1000回でも、200回でも構いません。

        実際のデータは、行方向に5フィールドで1組のかたちで並んでます。
        データ数は数百組とお考えください。

        これらのデータに対して、任意の計算式を追加して6フィールド目を
        追加したいのです。

        編集する方法として、例えば「行の挿入」ではあまりに重た過ぎます。
        なので別のシートを追加し、そちらに上の行から順番にコピーして
        行った方が断然速いと考えて、今はそのような処理を行っています。
        (まぁ実際断然速いです。。)

        しかし、この処理を稼働しているOSがWin7ということも影響して
        速度が安定しません。

        10秒以内で終わることもあれば1分かかるときもあり、もっと速く、
        例えば一瞬で処理が終われる方法はないかと模索していました。

        (Win10へ移行、という選択は今の段階では無しでお願いします)

        配列を応用した圧倒的な速度改善、という術(すべ)で解消できるなら
        是非ご教授してほしいです。

        宜しくお願いします。

        • mMm より:

          かしゅさん

          結論から言うと、このデータ構造やしたいことを考えた場合、配列で一瞬で処理させるのはかなり難しいと思います。

          実際どれ程のデータがあるかわかりませんが、配列化有効なのはセルへの参照回数が少ないときです。

          5行で一組のデータで、6行目の計算式を入れるといったシーンでは、あまり効果は見込めないでしょう。

          5行ではなく、全てのデータを一組として扱えるようにデータ構造を変えて、出力(ペースト)も少なくできれば、配列は効果はあります。

          今のままでも調子が良いときは、10秒以内で処理が完了するとのことなので、本記事の冒頭に紹介している簡単に高速化をまだお試ししていないなら使って見てください。

          • かしゅ より:

            mMmさま

            ご返事ありがとうございます。かしゅ です

            やはり難しいですか、、

            分かりました。
            ありがとうございます。