『Microsoft Excel』(以下Excel、エクセル)の中の機能としてある[VBA] について基本的な説明をしていきます。
仕事の改善や個人的な知識向上に役立ててください(^o^)
第5回目の今回は
･VBAの高速化処理
について解説していきますので、よろしくお願いします。
前回までのVBAに関するINDEXは下記を参照してくださいね。
↓サブログ内部リンク(VBA用INDEX)↓
-
-
目次
Excel VBA講座-目次(INDEX)
サブローExcel VBAに関する説明を今後増やして行く予定なので、各リンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 スラスラ読めるExcel VBA ...
続きを見る
Excel VBA高速化 4選！！
#1 描画停止
[ON] ⇨ Application ScreenUpdating True ⇨描画開始
描画の停止によって実行時間は確実に早くなりますが、画面がまったく動かないので、あまりにも長いとフリーズやループしているんじゃないかと思ってしまいます。
#2イベントの停止
[ON] ⇨ Application EnableEvents True ⇨イベント開始
EnableEventsをFalseにしている間、ワークシートイベントが起こりません。。マクロによるセルの変更でWorksheetイベントが誘発されないようにOFFにするのが本来の使い方であるため、マクロ実行中もイベントの発生が必要な場合は使用できません。またイベントが何も無い状態でも、抑制モードにしておくと若干の高速化が期待できます。
#3 自動計算処理の停止
[ON] ⇨ Application xlCalculationAutomatic ⇨自動計算
ワークシート上に計算式がまったく無くても、手動計算状態(自動計算をOFF)にすると若干の高速化が期待できます。途中でエラーが発生してマクロが止まると自動計算に戻らずに手動計算のままになるので、予期しない不具合になる可能性があるので注意が必要です。
#4 できるだけSELECT(セルの選択)を避ける
セルを選択(SELECT)する行為でも1回程度であれば気になりませんが、何回も選択する行為をすれば｢ちりも積もれば山となる｣です。
ムダな選択(SELECT)行為は減らしましょう。
高速化の時間検証
検証用で適当にマクロを組んでみました。
･マクロの流れ
①｢元SHEET｣のA2セルを選択して、コピー
②｢結果SHEET｣のA2セルを選択して、貼付け
③｢元SHEET｣のB2セルを選択して、コピー
④｢結果SHEET｣のB2セルを選択して、貼付け
⑤以降3行目から100行目まで、同じ処理を繰り返す。
･その他
｢結果SHEET｣のC列はA列×B列の計算式が2行目から100行目まで入っている。
｢結果SHEET｣で何からしらのセル変更があると、E1セルに更新日が入る
<構文>
※細かい説明は省きます。
｢標準モジュール｣に作ります。
Sub 高速検証()
Dim i As Integer
Dim speed As Single
Dim starttime As Single
Application.ScreenUpdating = False '描画停止
Application.EnableEvents = False 'イベント抑制
Application.Calculation = xlCalculationManual '手動計算
starttime = Timer
For i = 2 To 100
Sheets("元SHEET").Select
Cells(i, 1).Select
Selection.Copy
Sheets("結果SHEET").Select
Cells(i, 1).Select
ActiveSheet.Paste
Sheets("元SHEET").Select
Cells(i, 2).Select
Selection.Copy
Sheets("結果SHEET").Select
Cells(i, 2).Select
ActiveSheet.Paste
Next
Application.ScreenUpdating = True ’描画開始
Application.EnableEvents = True ’イベント開始
Application.Calculation = xlCalculationAutomatic ’自動計算
speed = Timer - starttime
MsgBox "処理時間は" & speed & "秒です"
End Sub
<Worksheetイベント確認用>
･セルの変更が発生すれば、E1セルに今日の日付を入力します。
｢Microsoft Excel Objects｣の｢結果SHEET｣の中に作ります。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("E1").Value = Date
End Sub
<SELECT(選択行為)を減らす場合>
基本構文の青マーカー部を下記に変更します。
Sheets("元SHEET").Cells(i, 1).Copy Sheets("結果SHEET").Cells(i, 1)
Sheets("元SHEET").Cells(i, 2).Copy Sheets("結果SHEET").Cells(i, 2)
速度検証の結果、下記になりました！やはり描画停止が時間短縮度合いが大きいですね！
下のVBA講座も是非ご覧下さい！！
関連
前回のVBA講座(第4回)
『VBA関数』の使い方【VBA入門講座#4】
関連
次回のVBA講座(第6回)
