201speed

PART2　高速化マクロ作成テクニック
大きなデータベースをExcelVBAで処理するとき時間が掛かり、やっぱりBASICプログラムは遅いなと
ガッカリしたことがありませんか。しかし、ExcelVBAでも高速化のテクニックを駆使すれば数倍、
数十倍早くなることがあります。本章では著者が経験した高速化について紹介します。

本章ではマクロ実行時間を比較していますが、走行時間はPCの性能により大きく変わるので、時間
そのものは参考になりません。どの程度速度がアップしたかの率の方を参考にして下さい。
なお、今回測定に使用したPCはCPU：2.40 GHz、メモリ：2GB、OS：Windows7、Excel：Excel 2010　の
一般的なノートパソコンです。

■2-1マクロ実行中の画面の動き停止
Sheet1のDATAをSheet2や他のブックに貼り付ける等、ワークシートやブックを切り替えるマクロを実行
する場合、画面がめまぐるしく動き画面がちらつき見ずらいと共に速度もかなり遅くなります。
セルにデータを記入するだけであればメモリー上の話しですが、それを見えるように表示する場合は
画面の更新が必要でかなり時間が掛かります。

結果だけを表示するのであれば、画面描画を抑止やシートが見えない状態で実行すればマクロは
高速実行になります。

(1)画面表示更新ストップで実行
ScreenUpdatingプロパティに「False」を設定すると、それ以降の画面表示更新をおこないません。
通常プロシージャの最後の方に「True」を設定し元の常態戻しますが、「True」の設定がもれた場合
でもマクロ実行終了で画面表示更新の普通の状態に戻ります。

サンプルマクロ「Sub 画面停止1」は実行速度比較用に作成したもので、実行結果は画面1のようにA列
とB列に数値を表示します。実行時間はメッセージボックスに表示させ、別マクロの実行結果と比較した。
(メッセージボックスのタイトル欄は「画面停止1s」のように、実行したマクロ名　が表示されます。なお、
sが付いているケースがありますが、これは当初　時間測定機能ありとなしのマクロを2種類作成した
関係ですが、サンプル　はSなしに統一したのでｓは無視して見て下さい）

★　本例の場合元データ作成「14.89秒」→「1.42秒」（画面停止2：画面1参照）
　　　で【約10倍アップ】でした。

画面1　比較用元データマクロ実行例

Sub 画面停止1()
Dim i As Integer, j As Integer
timck = Timer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Select
            Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Select
            Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
Sheets("Sheet1").Select
MsgBox "マクロ処理時間⇒　" & Timer - timck & "秒"
End Sub

本例は比較元サンプルではワザと選択を多くなるように作ってあります。
もし全体ループを一回増やし"Sheet1"と"Sheet2"の選択を各1回（画面停止1参考　マクロ）にすれば
れだけで数十倍早くなりますが、選択操作を少なくする件は2-2 節に記述しました。

サンプルマクロは走行時間測定用の「timck = Timer」および
「MsgBox "マクロ処理時間⇒　" & Timer - timck & "秒"」のステータスの記述がありますが、以降の
説明マクロは掲載時に走行時間測定箇所を削除して記載してあります。

・ScreenUpdatingプロパティに「False」実行例

Sub 画面停止2()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
    Application.ScreenUpdating = False
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Select
            Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Select
            Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
Sheets("Sheet1").Select
    Application.ScreenUpdating = True
End Sub

(2)対象ブックをアイコンにして実行
画面更新に時間が掛かるので、ブックを最小表示のアイコンにすればシートが見えないので多分実行速度が
速くなると思い確認しました。
(実行例は画面2参照）

★　本例の場合元データ作成「14.89秒」→「1.32秒」（画面停止3：画面2参照)　で【約11倍アップ】でした。
　　

画面2　アイコン化で実行例

Sub 画面停止3()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
        ActiveWindow.WindowState = xlMinimized
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Select
            Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Select
            Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
Sheets("Sheet1").Select
    ActiveWindow.WindowState = xlMaximized
End Sub

(3)Excel非表示にして実行
Application.Visible = FalseでExcelもアプリケーションであり消せます（非表示）。
マクロ実行速度としては、前述の2件ほど効果はない。また、こちらはもしマクロ内でエラーが発生した
場合の対処が面倒でありExcelVBAの扱いに慣れている方以外は使用しない方が無難(実行例は画面3）。

★　本例の場合元データ作成「14.89秒」→「3.71秒」（画面停止4：画面3参照）で【約4倍アップ】でした。
　　　

画面3　Excel非表示して実行例

Sub 画面停止4()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
    Application.Visible = False
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Select
            Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Select
            Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
Sheets("Sheet1").Select
    Application.Visible = True
End Sub

■2-2 自動記録の無駄部分の削除
自動記録したマクロは操作がそのまま記録され、それを実行すれば手動操作の自動化が完成であり
大変便利な機能です。ただし、記録されたソースにはデフォルト値で特に改めて指定する必要のない
ステートメントが含まれてます。

プログラムは記述通りに1ステップずつ忠実に実行されるので、余分なデフォルト値再指定の無駄な部分
を削除すれば走行時間は早くなります。
また自動記録では通常、1ステップで範囲を選択して次の1ステップで選択範囲に対してコマンドが実行
されます。この2ステップを1つのステートメントにまとめるとマクロの走行時間はかなり早くなります。

(1)範囲を選択でなくダイレクトに指定して実行
自動記録の「Select」メソッドを削除してダイレクトに場所を指定する。
通常「SelectとSelection」または「SelectとActiveCell」を消して問題ありません。（グラフなどで特定
チャート名を選択した場合は、その後の操作をSelectionで行ったほうが便利のケースもあります）

★本例の場合元データ作成（前述の画面停止1sマクロ)「14.89秒」→
　　　　　　　　　　　　　「0.21秒」（無駄削除1：画面4参照））で【約70倍アップ】でした。

画面4マクロ無駄削除1実行例

Sub 無駄削除1()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
    Sheets("Sheet1").Select
End Sub

【参考221】マクロ1行でセルに罫線を付ける
セルへの罫線付けを自動記録すると、下記「Macro2010」マクロのように記録されます。
約40行と膨大な量ですが、これは斜め線に関する情報なども記録されるためです。
一般的な罫線で太さも標準のものでよければ、「Macro罫線」のように1行で済みます。
(マクロ「Macro罫線消す」はその罫線を消去の例）

Sub Macro2010()
   Range("A1:D9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
・・・・　以下　40ステータスほどあります。
 
------------------------------------------------------------
Sub Macro罫線()
    Range("A1:D9").Borders.LineStyle = xlContinuous
End Sub
 
------------------------------------------------------------
Sub Macro罫線消す()
    Range("A1:D9").Borders.LineStyle = xlNone
End Sub

(2)これだけ覚えればOK「ダイレクトに指定+画面停止」で実行
これはダイレクト指定に、前述の画面更新停止を記載した例です。

★本例の場合元データ作成（前述の画面停止1sマクロ)「14.89秒」→
　　「0.18秒」（無駄削除1：画面5参照）で【約82倍アップ】でした。

画面5マクロ無駄削除2実行例

【補足】各種実行例を紹介してありますが、知識として習得すると自己のスキル　　アップになりましが、
実際のマクロ作成においては高速化の基本は本項目　　の「ダイレクトに指定+画面停止」のみ覚えれ
ば大体問題ありません。

画面停止+ダイレクト記入

Sub 無駄削除2()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
ThisWorkbook.Activate
    Application.ScreenUpdating = False
For i = 1 To 2
    For j = 1 To 1000
        Sheets("Sheet1").Cells(j, i) = Int(100 * Rnd + 1)
        Sheets("Sheet2").Cells(j, i) = Int(100 * Rnd + 1)
    Next j
Next i
    Sheets("Sheet1").Select
    Application.ScreenUpdating = True
End Sub

(3)ダイレクト記入+ブック非表示で実行
本項は、（1）項の画面の動き停止の内容ですが、制御範囲をダイレクトに指定する必要があり、こちら
の項目へ記述しました。
前述の2-1（3）のアプリケーションを非表示に設定する件は、方法は紹介したが実際の使用は避けた
方がよいと記述しましたが、こちらのブック非表示の方は、もしエラー発生などで処理に困った場合は、
メニューの「ウインドウ」「再表示」でブックは表示できるので、安心して使用できます。

★本例の場合元データ作成（前述の画面停止1sマクロ)「14.89秒」→
　　「0.26秒」（無駄削除1：画面6参照）で【約57倍アップ】でした。

画面6マクロ無駄削除3実行例

Sub 無駄削除3()
Dim i As Integer, j As Integer
Rnd (-12345)
Randomize (1)
    bokmane = ThisWorkbook.Name
    ActiveWindow.Visible = False
For i = 1 To 2
    For j = 1 To 1000
        With ThisWorkbook
            .Worksheets("Sheet1").Cells(j, i) = Int(100 * Rnd + 1)
            .Worksheets("Sheet2").Cells(j, i) = Int(100 * Rnd + 1)
        End With
    Next j
Next i
 
Windows(bokmane).Visible = True
    Sheets("Sheet1").Select
End Sub

(4)自動記録の変更箇所以外をを削除して実行
本例はセルに記入されている文字のフォントサイズを「10」に設定した例ですが、ほかの自動記録され
ている項目を削除して実行すると速度がどの程度アップするか確認した例です。

★本例の自動記録マクロ（無駄削除4)「1.61秒」→　「0.20秒」（無駄削除5：画面7参照）で【約8倍アップ】。
　

画面7マクロ無駄削除4実行例

Sub 無駄削除4()
Dim i As Integer, j As Integer
ThisWorkbook.Activate
    Application.ScreenUpdating = False
For j = 1 To 1000
            Cells(j, 1).Select
    With Selection.Font
        .Name = "ＭＳ Ｐゴシック"
        .FontStyle = "標準"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
Next j
    Sheets("Sheet1").Select
    Application.ScreenUpdating = True
End Sub
---------------------------------------------------------
Sub 無駄削除5()
Dim i As Integer, j As Integer
ThisWorkbook.Activate
    Application.ScreenUpdating = False
For j = 1 To 1000
        Cells(j, 1).Select
    With Selection.Font
        .Size = 10
    End With
Next j
    Sheets("Sheet1").Select
    Application.ScreenUpdating = True
 
 Cells(j, 1).Font.Size = 10　サイズのみ指定はこれでよい
End Sub

【戻る】　　　　【Top画面】　　　【HPへ】