マクロが遅い原因を排除して処理速度を上げる
マクロの処理速度を上げる方法は色々あります。その中の1つに配列を用いるという方法があります。
これは半分ぐらいは正解なのですがただ配列を使っただけでは処理速度を上げる事はできません。
ではどうやって配列を使うと処理速度を上げることができるのでしょうか。
本記事はこの問題に対する回答となるコードを用意しました。加えて解説も用意しました。
結論としては扱うデータを全て配列に取り込んで作業をする事でマクロの処理速度を上げることができます。
以降で具体的に説明していきます。加えて分かり易く解説する為に3つのポイントを用意しました。
- 配列に格納するデータを決める（配列の数を決める）
- 配列に格納する方法（配列化させる）
- 配列化したデータの取り回し（配列を運用する）
配列は難しいのでシンプルに考える為にやる事を3つに絞りました。
３つのポイントをおさえて作業をすることで扱うデータを全て配列に取り込める様になります。
コードと解説を見ていただく事で配列の使い方と作業を高速化する方法がわかります。
関連記事
「2次元配列を用いたコード」と「配列を使わない」コードの比較をしています。
VBEにコードを貼り付けて実行ボタンを押すだけで差が分かる様にコードを準備しておきました。
この記事の最後で「部分的な配列化で作業が速くなるのか？」と問題提起しています。
今回の記事で回答を用意しています。
データを全て配列に取り込む理由
理由はシンプルです。処理が遅くなる原因を排除したいからです。
「オブジェクトを都度掴みに行く」とは
「オブジェクトを掴む」＝「オブジェクトに関わるコードを書く」という事です。
例えばFor～nextステートメントで1000回ループするコードを組んだとします。
ループの中にRangeオブジェクトに関わる処理を1つ作ります。
Sub sample()
For r = 1 To 1000
Range(Cells(r, 1), Cells(r, 2)) = "こんにちは"
Next
End Subこれだけでも1000回Rangeオブジェクトに関わる事になります。
オブジェクトに関わる回数が多くなるとどうなるのか
1つのプロシージャ内でオブジェクトを掴む回数が1000回ぐらならまだ問題ないです。
扱うデータが1万、10万と増えていくとメモリが対応できなくなり処理速度がガクッと落ちるところがあります。
結果としていつまで経っても「カーソルの表示がぐるぐる状態」のままという事態に陥ります。
対策
処理速度が落ちる問題を解消するにはできるだけオブジェクトを掴まない様にコードを書くしかないです。
つまりデータを都度セルに取りに行くのではなく何らかのかたちで暗記するしかないという事です。
人が行う「暗記する」という行為をPC内で再現する為には「配列を使う」事になります。
検索や演算の都度セルの値を探していたのでは作業が遅くなるので全部記憶（値を配列に格納）します。
そうする事で検索や演算時はセルの値にアプローチする必要はありません。
この様に処理が遅くなる原因を排除する為の手段として配列を使う事になります。
画像を使って対策を再現してみる
対策をイメージしやすいように画像を用意してみました。
まずシートAに検索値が居てシートBにDBが配置された環境を想定します。
この2つのシート間で演算をしてシートXに結果を出力するという作業を構築しました。
この作業をする際に各シートから配列に値を格納してから演算すると処理速度が劇的に速くなります。
背景が水色の場所はシート（オブジェクト）です。背景が黄緑色の場所は配列です。
「背景が黄緑色の場所で仕事をやり切ってしまいましょう」という考え方です。
この方法であれば計3回Rangeオブジェクトと関わるだけで大容量のデータを素早く扱う事が出来ます。
事例
では実際に作業をしてみましょう。画像にならって事例を用意しました。
ワークシート
シートは2枚です。最初のシート（Sheet1）のセルB10からB16が1つ目の配列に格納する値になります。
画像では配列Cの出力先はシートXですが今回はこのシートのセルC10からセルD16とします。
続いて2番目のシート（Sheet2）です。セルB10からセルD16が配列に格納する値になります。
コード
今回も要望がありましたのであえて変数に日本語を使っています。
Sub 作業は配列に依頼する()
'セルに居る値を剥がして配列に収納し配列同士で演算してから値を配列に格納。
'最後にセルに貼り付けるという建付け。
'**************************************************
Dim ws1 As Worksheet 'ワークシート1を格納
Dim ws2 As Worksheet 'ワークシート２を格納
Dim ws1最終行 As Long 'ワークシート1の最終行
Dim ws1最終列 As Long 'ワークシート1の最終列
Dim ws2最終行 As Long 'ワークシート2の最終行
Dim ws2最終列 As Long 'ワークシート2の最終列
Dim 検索値 As Variant 'シート1の検索値を格納する配列
Dim DB As Variant 'シート2のDBを格納する配列
Dim 演算結果 As Variant 'シート1の演算結果を格納する配列
Dim 検索 As Long '配列（検索値）をループするための変数
Dim データ As Long '配列（データ）をループするための変数
'**************************************************
'定義した変数に値をセットしていきます
Set ws1 = Sheet1
Set ws2 = Sheet2
With ws2
ws2最終行 = .Cells(.Rows.Count, 2).End(xlUp).Row
ws2最終列 = .Cells(10, .Columns.Count).End(xlToLeft).Column
DB = .Range(.Cells(10, 2), .Cells(ws2最終行, ws2最終列))
End With
With ws1 'End With は一番最後です
ws1最終行 = .Cells(.Rows.Count, 2).End(xlUp).Row
ws1最終列 = .Cells(10, .Columns.Count).End(xlToLeft).Column
検索値 = .Range(.Cells(10, 2), .Cells(ws1最終行, 2))
演算結果 = .Range(.Cells(10, 3), .Cells(ws1最終行, ws1最終列))
'**************************************************
'ここが演算ブロックです 配列だから十分速いのですが次の記事（連想配列）の
'伏線にしたいのでループを組み合わせてあえて時間のかかる書き方をしています
For 検索 = LBound(検索値) To UBound(検索値)
For データ = LBound(DB) To UBound(DB)
If 検索値(検索, 1) = DB(データ, 1) Then
演算結果(検索, 1) = DB(データ, 2)
演算結果(検索, 2) = DB(データ, 3)
Exit For
End If
Next
Next
'**************************************************
'ここで値の貼付けです
.Range(.Cells(10, 3), .Cells(ws1最終行, ws1最終列)) = 演算結果
End With
End Sub
解説
3つのポイントに当てはめてコードを考えてみましょう。
1_配列に格納するデータを決める
まずは1つ目です。「配列に格納するデータを決める」を見ていきます。
検索する値、検索される値、結果を出力する場所の3つを配列に格納します。
コードの中では「検索値」、「DB」、「検索結果」という変数をVariant型で定義します。
先程ワークシートの画像で説明した値を3つの配列に格納していきます。
2_配列に格納する方法
続いてポイントの2つ目「配列に格納する方法」です。
コード内の24、30、31行目のコードでそれぞれの変数に配列を格納しています。
複数の値でも範囲指定してイコールで配列に代入するとセルの値を全て取り込んでくれます。
このコードを知っていれば配列に値を格納するのは非常に簡単です。
Variantで定義したのは値を格納する際他の型を使うとエラーが出る為です。ご注意ください。
これで配列は作れました。この後演算ブロックに入る前に一度ローカルウインドウを確認してみましょう。
この時点で3つの配列が出来ている事が分かります。この時点では演算結果の値は空なので中身は未展開です。
3_配列化したデータの取り回し
あとは配列「検索値」と配列「DB」でデータの紐付けを行い結果を配列「演算結果」に格納します。
最後に配列「演算結果」の値をSheet1の指定の場所に貼り付ける事で作業完了となります。
ここがポイントの3つ目「配列化したデータの取り回し」になります。
最終的に最初のシートには以下のようにデータが並ぶことになります。
今回は分かりやすさを優先させるために小さいモデルで説明しました。
余裕がある方は扱うデータの量を増やして配列を使うコードと配列を使わないコードを書いてみてください。
実行時間を比較してみると非常に大きな差が出る・・・と思いますがそんなに差が無いはずです。
これで配列を使うと大きなデータでも関係無く処理速度が上がる事がわかるはずです。
まとめ
必要な情報を一旦配列で受けてしまう事で処理速度を上げるという方法を紹介しました。
具体的には一旦値をメモリ上に引き取り演算、処理を行った値をセルに戻すという方法をとっています。
セルと配列の間で「取って入れて出す」を行うのではなく配列同士で「取って入れて出す」を繰り返します。
配列同士の方が情報のピッキング作業が速いので高速でデータを取り回してもらうという作戦でした。
PCのスペックによりますが検索値のデータを十万単位で増やしてもらっても作業は数秒で完了します。
配列はやはり作業が速いです。
「検索値も検索される側の値も膨大な量だった時」以外は本日紹介した仕様で対応出来ます。
上記のような環境では「連想配列」を使う事により作業の高速化が可能となります。
次回記事は「連想配列」を解説します
「連想配列」というのは「キー」と「アイテム」のセットで情報を持つことが出来ます。
上記の様に情報が持てるという事はVLOOKUP関数の代わりを務める事が出来ます。
コード次第では今回のコードよりさらに作業速度を上げる事が出来ます。
DBの行数が多くなっても問題ありません。
本日はここまでとします。連想配列に興味を持たれた方は以下記事をご覧ください。
