エクセルのマクロ機能。
VBAが得意でなくてもレコード機能なんかもあるので、作業の効率化を図るために導入してるよって方も多いんじゃないでしょうか。
マクロを使うとき、思ったより動作が重くて「こんなはずじゃないのにな・・・」なんて思ったことはありませんか？
この記事では「エクセルのマクロが重い原因を探して劇的に軽くする方法」について解説します。
VBAが得意でなくても簡単に適用できて、効果が出やすい3つの方法に厳選しましたので、ぜひチェックしてみてください。
この記事の内容
1. 動作が重いマクロのサンプル
こんにちは。エクセルマン・ブリーダーのしもむぎ(@re_znd13)です。
以下のサンプルのVBAコードを見ながら、マクロの動作が重い（遅い）原因や軽くする（速くする）方法を解説していきます。
1.1 ベースとするサンプルのVBAコード
このVBAコードの内容は以下のようになります。
Sub 重いマクロ()
'【A】部
Dim st, ft, t As String
Dim i As Long
'【B】部
st = Timer
'【C】部
Range("A:A").ClearContents
'【D】部
For i = 1 To 10000
Cells(i, 1).Select
ActiveCell.Value = i ^ 2
Next i
'【E】部
ft = Timer
t = ft - st
MsgBox t
End Sub【A】部
変数の宣言です。
st、ft、tはマクロの処理時間を計測するために用意した変数です。
iは繰返し動作をさせるための変数です。
【B】部
最初にst（スタートタイム）を記録します。時間の記録にはTimer関数を使います。
【C】部
A列を選択して内容をクリアします。
【D】部
繰返し操作の部分です。
iが1～10000となるまで、『セル(i,1)選択→セルにi^2の値を入力（A列の1行目から10000行目にi^2の値を入力）』という操作を繰返します。
【E】部
繰返し操作が終わったらft（フィニッシュタイム）として時間を記録します。
stとの差をこのマクロの処理時間tとして計算し、メッセージボックスに表示させます。
1.2 エクセルのワークシート上での計算
上記マクロを実行するエクセルのワークシートにおいては、B、C、D列でA列を用いた計算をさせています。
2. マクロが重い原因を探す2つの視点
それではまず、マクロが重い原因を探しましょう。
VBAのコードを眺めたり実行したりして、重い原因にピンとくるにはある視点が必要です。
2.1 画面表示の切り替わりがありますか？
上記VBAコードを実行してみると、エクセルの画面が目まぐるしく切り替わっていくことがわかります。
これ、重い原因のひとつです。
具体的に注意しなければならないのは、(a)セルの選択や移動、(b)ワークシートの移動などが主です。
今回のサンプルを見直すと【D】部にセルを選択してそのセルに値を入力する記述があります。
繰返しを行っていますので、A列を1行目から10000行目まで、10000回もセル選択を行っていることになります。そのたびにエクセルの画面が切り替わることになってしまうのです。
こういった記述がある場合、ふたつの対策が考えられます。
【セルの選択や移動を極力させないこと】と【画面更新を停止すること】です。
詳細な内容は次章にて解説します。
2.2 ワークシート上の計算がありますか？
今回のサンプルのワークシートでは、A列にマクロで値を入力し、B、C、D列ではすでに計算式が入力されているという状況を作りました。
B、C、D列はA列に値が入力された時点で自動的に計算を行います。
これも、重い原因のひとつです。
【ワークシート上の自動計算を停止すること】で改善を図れる可能性があります。
詳細は次章にて解説します。
3. マクロを劇的に軽くする3つの方法
前章で、マクロを重くしている原因の見つけ方とその対策のヒントを紹介しました。
この章では対策の詳細を解説します。
3.1 セル選択や移動をさせない方法と効果
重い原因のひとつ、『画面表示の切り替わり』の対策として、セル選択や移動をさせない方法があります。
セルを選択してからそのセルに値を入力するという記述は、特にレコード機能でマクロを記録した場合にありがちなんですが、セルの選択は必ずしも必要ではありません。
以下のVBAコードを見て下さい。
【D1】部のような記述をすることで、アクティブセルを移動せずにセルの値を直接制御することができます。
■セル選択をさせないVBAコード
Sub 軽くするマクロ1()
Dim st, ft, t As String
Dim i As Long
st = Timer
Range("A:A").ClearContents
'【D1】部
For i = 1 To 10000
Cells(i, 1).Value = i ^ 2
Next i
ft = Timer
t = ft - st
MsgBox t
End Sub■セル選択をさせない効果
ベースと対策後の処理時間比較を行います。
メッセージボックスに表示された時間を記録して3回平均を取りました。
ベースの41.7秒に対して、対策後は8.3秒。約20%に縮めることができました。
3.2 画面更新を停止する方法と効果
重い原因のひとつ、『画面表示の切り替わり』の対策として、画面更新を停止する方法があります。
最も有名な方法になるかと思いますが、マクロのレコード機能でVBAを記述させた場合には出てこないので、ぜひ知っておいてください。
以下のVBAコードを見て下さい。
【B1】部のような記述をすることで、画面更新を停止できます。これを記述しないのとTrueとしておくのとは同義です。
■画面更新を停止するVBAコード
Sub 軽くするマクロ2()
Dim st, ft, t As String
Dim i As Long
'【B1】部
Application.ScreenUpdating = False
st = Timer
Range("A:A").ClearContents
For i = 1 To 10000
Cells(i, 1).Select
ActiveCell.Value = i ^ 2
Next i
ft = Timer
t = ft - st
MsgBox t
End Sub■画面更新を停止する効果
ベースと対策後の処理時間比較を行います。
メッセージボックスに表示された時間を記録して3回平均を取りました。
ベースの41.7秒に対して、対策後は10.2秒。約25%に縮めることができました。
3.3 ワークシート上の自動計算を停止する方法と効果
重い原因のひとつ、『ワークシート上の自動計算』の対策として、自動計算を停止する方法があります。
これも、マクロのレコード機能でVBAを記述させた場合には出てこないので、ぜひ知っておいてください。
以下のVBAコードを見て下さい。
【B1】部のような記述をすることで、自動計算を停止できます。繰返し操作時には自動計算を停止しておいて、繰返し操作が終わってから【E1】部のような記述をしてワークシート上の計算をさせます。
■自動計算を停止するVBAコード
Sub 軽くするマクロ3()
Dim st, ft, t As String
Dim i As Long
'【B1】部
Application.Calculation = xlCalculationManual
st = Timer
Range("A:A").ClearContents
For i = 1 To 10000
Cells(i, 1).Select
ActiveCell.Value = i ^ 2
Next i
'【E1】部
Application.Calculation = xlCalculationAutomatic
ft = Timer
t = ft - st
MsgBox t
End Sub
■自動計算を停止する効果
ベースと対策後の処理時間比較を行います。
メッセージボックスに表示された時間を記録して3回平均を取りました。
ベースの41.7秒に対して、対策後は38.8秒。対策１や２ほどの効果はないものの、約93%に縮めることができました。
4. 3つの方法を組み合わせた場合の効果
前章で、重いマクロを軽くする３つの方法を紹介しました。
この章では組み合わせの効果について検証してみます。
３つの方法がありましたので、その中から２つを組み合わせる場合と３つとも組み合わせる場合とで、４パターンの組み合わせ方がありますね。
ベースと4パターンそれぞれの処理時間比較を行います。
これに関しても、メッセージボックスに表示された時間を記録して3回平均を取りました。
ベースの41.7秒に対して、もっとも効果があったのは３つとも組み合わせた場合になりました。その効果、なんと約100倍速くなっています。
そしておもしろい結果となったのは２つの組み合わせ。
対策１と２は単体でそれなりの効果がありましたが、１＋２よりも２＋３、１＋３の方が効果的であることがわかります。１＋３に至っては３つとも組み合わせた場合とほぼ同等の効果が確認できます。
■対策１～３を組み合わせたVBAコード
３つとも組み合わせた場合のVBAコードも紹介しておきます。
Sub 軽くするマクロ123()
Dim st, ft, t As String
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
st = Timer
Range("A:A").ClearContents
For i = 1 To 10000
Cells(i, 1).Value = i ^ 2
Next i
Application.Calculation = xlCalculationAutomatic
ft = Timer
t = ft - st
MsgBox t
End Sub5. まとめ
エクセルのマクロを軽くする方法はいかがでしたか？
もっとも有名なのは『画面更新を停止する方法』ではありますが、それより効果的なものもありました。
個人的にはセルの選択や移動をムダにさせないことが大切だと思います。コードとしても見やすくなりますよね。
今回の３つの方法は比較的簡単にできるものですが、劇的に軽くすることができますので、ぜひ活用してください。
