初心者Excelマクロ(VBA)プログラマー・エンジニアで、Excelマクロの処理速度が遅くて悩んでいるなら必見です。
セルへの値の代入回数を減らせば、そのマクロ超高速になるかもしれませんよ！
この記事では、セルへの代入がマクロの実行時間に与える影響を検証しました。
Excelのセルへの入力回数を減らせば早くなる
Excelマクロはセルに対する値の入力回数を減らせば劇的に早くなります。
まずはその根拠を見ていきましょう。
Excelのセル参照回数が多いと遅いのか？
次の３パターンで処理速度を比較しました。
- A列の最大行分、繰り返すだけの処理
- A列を最大行分、１セルずつ値を取得する処理
- A列を最大行分、１セルずつ値を入力する処理
処理速度は次のようになりました。
| # | テストケース | 処理速度（秒） |
| 1 | 繰り返すだけの処理 | 0.019 |
| 2 | １セルずつ値を取得する処理 | 2.699 |
| 3 | １セルずつ値を入力する処理 | 28.257 |
テストに利用したプログラムは以下です。
#1:A列の最大行分、繰り返すだけの処理
Sub demo01()
Dim startTime As Double
Dim endTime As Double
Dim testSheet As Worksheet 'ワークシートの変数を作る
Set testSheet = ThisWorkbook.Worksheets(1) 'Sheet1を代入
Dim i As Long 'for文用
Dim maxRow As Long '最大行
maxRow = testSheet.Rows.Count '最大行を取得
startTime = Timer '開始時刻
For i = 1 To maxRow
'<何も処理しない>
Next
endTime = Timer '修了時刻
Debug.Print "Demo-01処理速度：" & endTime - startTime
End Sub
A列の行数文「for文」を回しただけです。
特に処理は行っていません。
#2:A列を最大行分、１セルずつ値を取得する処理
Sub demo02()
Dim startTime As Double
Dim endTime As Double
Dim testSheet As Worksheet 'ワークシートの変数を作る
Set testSheet = ThisWorkbook.Worksheets(1) 'Sheet1を代入
Dim i As Long 'for文用
Dim maxRow As Long '最大行
Dim temp As String '値格納用
maxRow = testSheet.Rows.Count '最大行を取得
startTime = Timer '開始時刻
For i = 1 To maxRow
'<値を取得するだけ>
temp = testSheet.Cells(i, 1).Value
Next
endTime = Timer '修了時刻
Debug.Print "Demo-02処理速度：" & endTime - startTime
End Sub
A列の値を1セルずつ、変数「temp」に代入しています。
セルの値を参照するだけの処理です。
#3:A列を最大行分、１セルずつ値を入力する処理
Sub demo03()
Dim startTime As Double
Dim endTime As Double
Dim testSheet As Worksheet 'ワークシートの変数を作る
Set testSheet = ThisWorkbook.Worksheets(1) 'Sheet1を代入
Dim i As Long 'for文用
Dim maxRow As Long '最大行
maxRow = testSheet.Rows.Count '最大行を取得
startTime = Timer '開始時刻
For i = 1 To maxRow
'<値を書き込むだけ>
testSheet.Cells(i, 1).Value = i
Next
endTime = Timer '修了時刻
Debug.Print "Demo-03処理速度：" & endTime - startTime
End Sub
A列の1セルずつ値を書き込んでいます。
処理結果
これらdemo01～03までの処理結果がこちらです。
繰り返し処理の回数は、Excelの最大行数である1,048,576回です。
Demo-03の処理速度が最も遅いですね。
マクロが遅い原因はセルに対する入力処理
Excelマクロが遅くなる原因の１つが、セルに対する値の入力処理である事が分かりましたね。
For文を使って、１セルずつ処理をするExcelマクロは処理速度が遅くなって当たり前です。
それでは、この解決策を見ていきましょう。
セルへの入力回数を減らすためのコーディング
Excelシートのセルへ入力回数を減らすにはどうしたらよいのか…答えは「配列」を使う。これに尽きます。
シート全体を配列にぶち込んで処理
それでは、続いて配列を使ったデモを実施してみました。
- まとめてセルの値を参照する（値を取得する）処理
- まとめてセルに値を入力する（値を代入する）処理
処理速度は次のようになりました。
| # | テストケース | 処理速度（秒） |
| 1 | 繰り返すだけの処理 | 0.019 |
| 2 | １セルずつ値を取得する処理 | 2.699 |
| 3 | １セルずつ値を入力する処理 | 28.257 |
| 4 | まとめてセルの値を参照する | 0.164 |
| 5 | まとめてセルに値を入力する | 1.457 |
#2と#4、#3と#5を比較すると劇的に処理速度が改善しています。
テストに利用したプログラムは以下です。
#4:まとめてセルの値を参照する（値を取得する）処理
Sub demo04()
Dim startTime As Double
Dim endTime As Double
Dim testSheet As Worksheet 'ワークシートの変数を作る
Set testSheet = ThisWorkbook.Worksheets(1) 'Sheet1を代入
Dim i As Long 'for文用
Dim maxRow As Long '最大行
Dim buff As Variant '配列用
maxRow = testSheet.Rows.Count '最大行を取得
startTime = Timer '開始時刻
'▼まとめてセルから情報を取得する
buff = testSheet.Range("A1:" & "A" & maxRow)
endTime = Timer '修了時刻
Debug.Print "Demo-04処理速度：" & endTime - startTime
End Sub
(▼)Variant型のbuffという変数にA列のセルを全て代入します。
プログラム自体もFor文を使わないので、簡潔で読みやすいです。
この時、buffの中身は次のようになっています。
一気に取得できて楽ですよね。
#5:まとめてセルに値を入力する（値を代入する）処理
Sub demo05()
Dim startTime As Double
Dim endTime As Double
Dim testSheet As Worksheet 'ワークシートの変数を作る
Set testSheet = ThisWorkbook.Worksheets(1) 'Sheet1を代入
Dim i As Long 'for文用
Dim maxRow As Long '最大行
Dim buff As Variant '配列用
maxRow = testSheet.Rows.Count '最大行を取得
startTime = Timer '開始時刻
'▼まとめてセルから情報を取得する
buff = testSheet.Range("A1:" & "A" & maxRow)
For i = 1 To maxRow
'<配列に値を書き込むだけ>
buff(i, 1) = i
Next
'★A列に配列を纏めて代入する
testSheet.Range("A1:" & "A" & maxRow) = buff
endTime = Timer '修了時刻
Debug.Print "Demo-05処理速度：" & endTime - startTime
End Sub
#4で取得したbuffに対してFor文で値を代入しています。
そして最後に(★)A列にbuffを代入します。
処理結果
それではこちらがdemo04,demo05の処理結果になります。
配列を使う事で処理速度が劇的に改善されましたね。
まとめ
Excelマクロの処理速度が遅くて悩んでいるなら、セルに対する値の代入回数に着目してみましょう。
セルに対する値の代入処理を最小限する事で、Excelマクロの処理速度は格段に改善されます！
「Excelマクロだから遅くて仕方がない」は、ただの言訳！技術不足です！
他にも「初心者のVBAが遅い原因４つを紹介！Excelのセル参照を減らすための知識」などを紹介しています。
コメント