「より高速な VBA マクロ」について

下記リンク先のページ
『 Excel 2007 におけるパフォーマンスの改善 』
の「より高速な VBA マクロ」についてのメモ

上記ページの該当箇所では
VBA マクロを高速化するための
５つの基本的なヒントとマクロの例が紹介されています。

５つの基本的なヒントを引用します。

１．マクロの実行中は、画面更新と計算を無効にします。
２．セル単位でループするのではなく、2 次元配列を含むバリアントで、範囲からの データを取得します。
３．Range オブジェクトなどの Excel オブジェクトは、選択したりアクティブ化する のではなく、直接的に参照します。
４．配列を Range に直接的に割り当てることにより、結果を返します。
５．マクロが終了した時点で、画面更新と計算を有効にします。

これらのヒントについて順に見ていきます。

１．マクロの実行中は、画面更新と計算を無効にします。

以下のコードで画面更新と計算を無効にできます。

'画面更新を無効
Application.ScreenUpdating = False
'計算を手動にする/計算を無効
Application.Calculation = xlCalculationManual

このコードをプロシージャの最初に実行すると
VBAであるセルの値を変えたときでも
シートやグラフの見た目は変化しません。

また、あるワークシート関数の参照元セルの値を変えても
自動で計算されません。

２．セル単位でループするのではなく、2 次元配列を含むバリアントで、範囲からの データを取得します。

ようするに
セルの範囲をバリアント型の変数にいれて使うということなので
これについては
以前の記事「 セルの値を配列に格納して使う 」を参照してください。

３．Range オブジェクトなどの Excel オブジェクトは、選択したりアクティブ化する のではなく、直接的に参照します。

選択したり

Range("A1").select
ActiveCell.Value = 20

アクティブ化する

Range("A1").Activate
ActiveCell.Value = 20

のではなく直接的に参照します。

Range("A1") = 20

自分でVBAを直接作っていくと
こういったことはないと思いますが、
マクロ自動記録で作ったコードは
SelectとActivateは必ず多用されています。

４．配列を Range に直接的に割り当てることにより、結果を返します。

これについても
以前の記事「 セルの値を配列に格納して使う 」を参照してください。

５．マクロが終了した時点で、画面更新と計算を有効にします。

以下のコードで画面更新と計算を有効にできます。

'画面更新を有効
Application.ScreenUpdating = True
'計算を自動にする/計算を有効
Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual と
対にして使います。

サンプルコードはこちら

Sub macro101225a()
'Option Base 1で実行
'「より高速な VBA マクロ」サンプルコード

    Dim i As Integer
   
    '画面更新と計算を無効
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
    'Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As String

    'バリアントで、範囲からのデータを取得
    vData = Range("A1:A5") '5x1
   
    '出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData, 1), 1) '5x1
   
    'Variant型の変数に入れた値を処理して出力用配列に入れる
    For i = 1 To UBound(vData, 1)
        'Chr関数で文字に変換
        dOutput(i, 1) = Chr(vData(i, 1))
    Next i
   
    '配列を Range に直接的に割り当てる
    Range("B1").Resize(UBound(dOutput, 1), 1) = dOutput '5x1

    
    '画面更新と計算を有効
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

上のコードを下の状態のシートで実行する。

実行後のシート

数字をChr関数で文字にしました。