Excel Microsoft VBA

高速化処理のテクニックをマスターしよう!【VBA入門講座#5】

サブロー
こんにちは。サブローです。

『Microsoft Excel』(以下Excel、エクセル)の中の機能としてある[VBA] について基本的な説明をしていきます。

仕事の改善や個人的な知識向上に役立ててください(^o^)

第5回目の今回は

・VBAの高速化処理

について解説していきますので、よろしくお願いします。

前回までのVBAに関するINDEXは下記を参照してくださいね。

↓サブログ内部リンク(VBA用INDEX)↓

Excel VBA講座-目次(INDEX)

サブローExcel VBAに関する説明を今後増やして行く予定なので、各リンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 目次(INDEX) 講座No内容 ...

続きを見る

Excel VBA高速化 4選!!

 

#1 描画停止

[OFF] ⇨ Application ScreenUpdating False  ⇨描画停止
[ON] ⇨ Application ScreenUpdating True  ⇨描画開始

 

描画の停止によって実行時間は確実に早くなりますが、画面がまったく動かないので、あまりにも長いとフリーズやループしているんじゃないかと思ってしまいます。

#2イベントの停止

[OFF] ⇨ Application EnableEvents False  ⇨イベント抑制
[ON] ⇨ Application EnableEvents True  ⇨イベント開始

EnableEventsをFalseにしている間、ワークシートイベントが起こりません。。マクロによるセルの変更でWorksheetイベントが誘発されないようにOFFにするのが本来の使い方であるため、マクロ実行中もイベントの発生が必要な場合は使用できません。またイベントが何も無い状態でも、抑制モードにしておくと若干の高速化が期待できます。

 

#3 自動計算処理の停止

[OFF] ⇨ Application xlCalculationManual  ⇨手動計算
[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)


 


 

速度検証の結果、下記になりました!やはり描画停止が時間短縮度合いが大きいですね!


《前回の復習~入門講座#4~》
『VBA関数』の使い方【VBA入門講座#4】

サブロー
それでは、今回はここまで。最後までお読みいただきありがとうございました!

2020年3月1日公開



~INDEX一覧~

エクセル関数の勉強 1

サブローエクセル/Excel関数の一覧にしています。本ブログ内で記事にしているものは、講座の列にリンク付けを随時行います(^o^) ※使用頻度のランクに関しては、個人的な主観でランク付けをしています。 ...

2

サブロー危険物取扱者試験 乙4種対策講座の目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 【目次/INDEX】 講座ID分類内容リンク 1共通#1『危険物取扱者試験』の ...

3

サブローQC(品質管理)検定に関する説明が増やして行く予定なのでリンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 モグゾー対象級を記載していますが、あく ...

4

サブローExcel VBAに関する説明を今後増やして行く予定なので、各リンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 目次(INDEX) 講座No内容 ...

-Excel, Microsoft, VBA
-,

Copyright© サブログ , 2020 All Rights Reserved Powered by AFFINGER5.