マクロを高速化する10の方法

過剰な参照を避ける

オブジェクトのメソッドまたはプロパティを呼び出すときは、OLE コンポーネントの IDispatch インターフェイスを経由する必要があります。これらの OLE コンポーネントの呼び出しには時間がかかるため、OLE コンポーネントへの参照の数を減らすとマクロ コードの速度が向上します。

オブジェクトのプロパティまたはメソッドの呼び出しには、通常、 Object.Methodの表現メソッド  、つまり「.」が使用されます。シンボルはプロパティとメソッドを呼び出すために使用されます。

したがって、メソッドやプロパティの呼び出し回数は「.」の数で判断できます。「。」が少ないほど シンボルを指定すると、コードの実行が速くなります。

たとえば、次のステートメントには 3 つの記号「.」が含まれています。

ThisWorkbook.Sheet1.Range("A1").Value = 100

次のステートメントには記号「.」が 1 つだけあります。

Activewindow.Top = 100

ここでは、記号「.」の数を減らすためのいくつかのトリックを紹介します。より速く走るために。

まず、同じオブジェクトを繰り返し参照する必要がある場合、オブジェクトを変数に設定して呼び出しの数を減らすことができます。たとえば、次のコードでは 1 行につき 2 つの呼び出しが必要です。

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

Sheets("Sheet1")オブジェクトは繰り返し参照する必要があるため 、最初に変数sht に設定すると  、各コードの呼び出しが 1 回だけで済むようになります。

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

次に、一時変数 sht を宣言したくない場合は、  前述のWithステートメントを使用することもできます。次の例に示すように:

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

3 番目に、 ループが多い場合は、プロパティとメソッドをループの外に置くようにします。 ループ内で同じオブジェクトのプロパティ値を再利用する場合、まずループ外で指定した変数にプロパティ値を代入し、その変数をループ内で使用すると高速化できます。次の例に示すように:

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

この例の各ループは、セル Cells(1,2) の Value プロパティを取得します。ループの開始前に Cells(1.2) の Value プロパティを変数に割り当てると、実行が速くなります。次の例に示すように:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

上記のコードは、  ループするたびにThisWorkbook.Sheets("Sheet1")を呼び出します。Withステートメントを使用してThisWorkbook.Sheets("Sheet1") への呼び出しを  ループの外に移動すると、これをより速く行うことができます 。次の例に示すように:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

バリアント型の使用を避ける

通常、初心者は Variant 型変数を使用することを好みます。これには、データが Integer または Long データ型に対して大きすぎる場合でもメモリ オーバーフローの問題が発生することなく、どの型のデータでも使用できるため、複雑さが少ないという利点があります。ただし、Varienmt 型のデータは、指定された他の型よりも多くのメモリ領域 (Integer データの場合は 2 バイト、Long データの場合は 4 バイト、Variant データの場合は 16 バイト) を必要とし、VBA は他の指定された型よりも Variant 型データの処理に多くの時間を必要とします。データの。次の例が示すように。

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

上記のコードでは、8 ～ 13 行目で Variant 変数の加算、減算、乗算、除算の 100 万回の演算を実行し、17 ～ 22 行目で Integer 変数の加算、減算、乗算、除算の 100 万回の演算を実行しています。私のコンピューターでは、Variant 変数の操作には約 0.09375 秒かかり、Integer 変数の操作には約 0.03125 秒かかりました。結果はコンピュータによって異なる場合がありますが、 バリアント変数は整数変数よりも大幅に遅くなります。

このため、 指定されたデータ型を明示的に使用できる場合は、バリアント型変数の使用を避けることをお勧めします。