セルへの値の代入は時間がかかる
対象となるセルが少なければ問題は起こらないのですが、たくさんのセルに対して条件分岐を加えながらセルの値を変更しようとするとかなり時間がかかってしまい、他の操作と合わせた一連のマクロの中で律速になってしまうことがあります。
今回はそんなときに大活躍のセルを配列に入れるVBAコードを紹介します。
セルを配列に入れてスピードアップできる例
例えば仮に以下のシートのようにセルの縦10000行、横1000列のセルにランダムな0～9までの整数が入っているシートがあったと仮定して、これらのセルの中で値が「0」のものを空白にするマクロを作るとします。
その際に最も簡単な手順でVBAコードを作成すると以下のようになると思います。
Dim 開始時間 As Date, 終了時間 As Date
Dim i As Long, k As Long
開始時間 = Timer ’時間の計測
Application.ScreenUpdating = False
For i = 1 To 10000
For k = 1 To 1000
If Cells(i, k) = 0 Then
Cells(i, k) = “”
End If
Next k
Next i
Application.ScreenUpdating = True
終了時間 = Timer ’時間の計測
Debug.Print Round(終了時間 – 開始時間, 3) ’所要時間を書き出す
End Sub
このVBAコードはそれぞれのセルに対してすべて個別に値を取得し、条件に一致するものの値を削除する手順を行うため、とても時間がかかる処理になってしまいます。
そこでこのコードを以下のように変更すると劇的に処理速度がアップします。
Dim 開始時間 As Date, 終了時間 As Date
Dim i As Long, k As Long
Dim c As Variant
開始時間 = Timer ’時間の計測
Application.ScreenUpdating = False
c = Range(Cells(1, 1), Cells(10000, 1000))
For i = 1 To 10000
For k = 1 To 1000
If c(i, k) = 0 Then
c(i, k) = “”
End If
Next k
Next i
Range(Cells(1, 1), Cells(10000, 1000)) = c
Application.ScreenUpdating = True
終了時間 = Timer ’時間の計測
Debug.Print Round(終了時間 – 開始時間, 3) ’所要時間を書き出す
End Sub
このVBAコードはバリアント型の変数を宣言して複数のセルを代入することで、それぞれのセルの値を配列として処理できるようにしたものです。
配列の値の変更はセル自体の値の変更よりも圧倒的に早く、セルに値を戻すのは一括で処理できるため処理速度が上がるという仕組みです。
（日本語変だったらすみません。あまり意味は分からなくてもとりあえずVBAコードが使用できればそれでも良い気がします。）
スポンサーリンク
所要時間の計測
それぞれのコードについて、10回実行した際の所要時間を以下に記載します。
| 回数 | 配列使用しない | 配列使用 |
|---|---|---|
| 1回目 | 58.5(秒) | 11.8(秒) |
| 2回目 | 247.5(秒) | 11.8(秒) |
| 3回目 | 58.6(秒) | 11.8(秒) |
| 4回目 | 59(秒) | 11.8(秒) |
| 5回目 | 247.1(秒) | 11.8(秒) |
| 6回目 | 247.1(秒) | 11.9(秒) |
| 7回目 | 248.7(秒) | 11.9(秒) |
| 8回目 | 53.2(秒) | 11.8(秒) |
| 9回目 | 56.1(秒) | 11.7(秒) |
| 10回目 | 55.8(秒) | 11.9(秒) |
| 平均 | 133.2(秒) | 11.8(秒) |
配列に格納する処理がいかに高速かわかると思います。
また、通常の処理は負荷が大きいのか、処理時間が2パターンに分かれて計測されました。
配列を使用した方は安定した処理速度が期待できるのも大きなメリットです。
セルの配列への代入における注意点
セルを配列に入れる際の注意点は以下の3点です。
①セルの値を格納するために宣言する変数（配列）はバリアント型
セルの値を格納する配列はRange型ではなくバリアント型を宣言します。
②配列のインデックス番号は1から
配列のインデックス番号は通常0からスタートしますが、セルを格納した配列に関しては1がインデックス番号の開始番号となります。
私はこの部分に違和感を感じ、なじむのに時間がかかりました。
③セルの値以外は変更できない
配列に格納しているのはセルの値のみのため、セルの背景色やフォントなどの情報は配列に格納されないので値以外の変更には使用できません。
（と思います。できたらすみません。）
コメント