【VBA】高速化するための設定＆高速テクニックまとめ～地道に学ぶExcelVBA～

高速化について
画面更新・自動計算・イベントをオフにする
セルへのアクセスを工夫する
型の宣言を見直す
- 演算用の変数は、Variant型で宣言しない
- CreateObjectを乱用しない（参照設定を使用する）
プログラムを見直す
その他
関連リンク

高速化とは

高速化とは、マクロの処理速度をできるだけ速くすることをいいます。同じ結果を得る場合でも、記述の仕方や処理の方法によって実行時間は大きく変わることがあります。一度の短縮はわずかでも、細かい工夫を積み重ねることで、全体として大きな差につながります。特に大量のデータを扱う場面では、その効果が顕著に現れます。
つまり「同じ実行結果を保ちながら、より短時間で完了させる」ことを目指すのが、高速化の考え方です。

具体的には、Application.ScreenUpdating = False や Application.Calculation = xlCalculationManual などが代表的な手法です。

処理速度を計測する関数について

本記事の実行速度の測定には、ChatGPT が作成した処理時間計測プログラムをベースに、著者が一部改修したものを使用しています。

処理時間計測プログラム

Option Explicit

Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (ByRef lpPerformanceCount As LongLong) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (ByRef lpFrequency As LongLong) As Long
Private tStart As LongLong
Private freq  As LongLong

'=== 計測開始 ===
Public Sub StartTimer()
    QueryPerformanceFrequency freq
    QueryPerformanceCounter tStart
End Sub
'=== 計測終了 ===
Public Function EndTimer() As Double
    Dim tEnd As LongLong
    QueryPerformanceCounter tEnd
    EndTimer = (tEnd - tStart) / freq
End Function

'=== 計測開始 ===
Public Sub StartTimer2()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Call StartTimer
End Sub
'=== 計測終了 ===
Public Function EndTimer2() As Double
    EndTimer2 = EndTimer
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Function

処理速度を計測するPCについて

本検証は以下の環境で実施しました。
・OS　： Windows 11 Home 64bit
・CPU ： Intel Core i7-13700H
・メモリ： 64GB
・Excel ： Excel 2021（64ビット）
・ストレージ：SSD

画面更新・自動計算・イベントをオフにする

画面更新をオフにする

画面更新をオフにすると処理速度は向上しますが、その間は画面表示が更新されません。
しかし、画面更新をオンにするまで、Excelの画面はプログラム実行前の画面で止まってしまいます。処理が終わったら必ずオンに戻してください。

・画面更新をオフにする
　Application.ScreenUpdating = False

・画面更新をオンにする
　Application.ScreenUpdating = True

処理時間計測プログラム

Sub SampleSw1_1()
    Dim i As Long
    Dim j As Long
    
    '高速化有り
    Call StartTimer
    Application.ScreenUpdating = False
    For i = 1 To 100000
        Cells(1, 1) = "test"
    Next i
    Application.ScreenUpdating = True
    Debug.Print "高速化有りの処理時間：" & EndTimer

    '高速化無し
    Call StartTimer
    For i = 1 To 100000
        Cells(1, 1) = "test"
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	1.4205399	1.6741159	15.15%
2	1.3845993	1.6660323	16.89%
3	1.4173410	1.6838649	15.83%
4	1.3821443	1.6497702	16.22%
5	1.3851057	1.6616701	16.64%
平均	1.3979460	1.6670909	16.14%

数式の自動計算を手動計算にする

数式の自動計算を「手動計算」に切り替えると、処理速度が速くなります。特に大量のデータや複雑な関数が含まれている場合に効果があります。
ただし、手動計算モードでは数式が自動で更新されないため、セルの値を正しく取得したい場合は再計算をする必要があります。通常は処理の前に手動計算へ切り替えて、処理後に自動計算へ戻すという流れで利用すると安全です。

・手動計算に切り替える
　Application.Calculation = xlCalculationManual

・自動計算に切り替える
　Application.Calculation = xlCalculationAutomatic

・Excel関数を再計算をさせる
　Application.Calculate

処理時間計測プログラム

Sub SampleSw1_2()
    Dim i As Long
    Dim pos As Long
    
    Application.Calculation = xlCalculationAutomatic
    Range("B2") = "=SUM(A1:A3)"
    
    '高速化有り
    Call StartTimer
    Application.Calculation = xlCalculationManual
    For i = 1 To 50000
        pos = 1 + (i Mod 3)
        Cells(pos, 1).Value2 = i
    Next i
    Application.Calculation = xlCalculationAutomatic
    Debug.Print "高速化有りの処理時間：" & EndTimer

    '高速化無し
    Call StartTimer
    For i = 1 To 50000
        pos = 1 + (i Mod 3)
        Cells(pos, 1).Value2 = i
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.5671387	5.6736257	90.00%
2	0.5152963	5.7171905	90.99%
3	0.5321325	5.7282477	90.71%
4	0.5204083	5.7085501	90.88%
5	0.5189289	5.7617017	90.99%
平均	0.5307809	5.7178631	90.72%

イベント処理を無視する

イベント処理を無視すると処理が速くなります。
例えば、セルに値を入力したときに「Changeイベント」などが発生しますが、大量のセルを書き換える場合、イベントが何度も呼び出されるため処理が遅くなります。
そのため、処理の最初にイベントを無効化し、処理後に有効化するのが定石です。

・イベント処理を無視する
　Application.EnableEvents = False

・イベント処理を有効にする
　Application.EnableEvents = True

処理時間計測プログラム

Sub SampleSw1_3()
    Dim i As Long
    
    '高速化有り
    Call StartTimer
    Application.EnableEvents = False
    For i = 1 To 100000
        Cells(1, 1) = "test"
    Next i
    Application.EnableEvents = True
    Debug.Print "高速化有りの処理時間：" & EndTimer

    '高速化無し
    Call StartTimer
    For i = 1 To 100000
        Cells(1, 1) = "test"
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	11.1401710	12.2889293	9.35%
2	10.9743245	12.5128511	12.30%
3	11.1802355	12.7379807	12.23%
4	11.1243595	12.7045851	12.44%
5	11.1202148	12.3725024	10.12%
平均	11.1078611	12.5233697	11.3%

上記をまとめたプログラム

処理前に、画面更新・自動計算・イベントをまとめてオフにする（無視する）関数を自作して、処理前に実行すると処理速度が速くなります。
処理後は、忘れずにオンに戻しましょう。

処理時間計測プログラム

Sub SampleSw1_4()
    Dim i As Long
    Dim pos As Long
    
    Range("B2") = "=SUM(A1:A3)"
    
    '高速化有り
    Call StartTimer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = 1 To 100000
        pos = 1 + (i Mod 3)
        Cells(pos, 1).Value2 = i
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Debug.Print "高速化有りの処理時間：" & EndTimer

    '高速化無し
    Call StartTimer
    For i = 1 To 100000
        pos = 1 + (i Mod 3)
        Cells(pos, 1).Value2 = i
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	1.0298484	11.4858580	91.03%
2	1.0246696	11.1956542	90.85%
3	1.0243684	11.2789648	90.92%
4	1.0308921	11.1631750	90.77%
5	1.0350833	11.2740616	90.82%
平均	1.0289727	11.2795427	90.88%

セルへのアクセスを工夫する

Rangeでなく、Cellsを使用する

Rangeでなく、Cellsを使用すると処理速度が上がります。
「Range("A1")」のようにセルを指定すると、Excelは内部で文字列を解析し、セル番号に変換してからアクセスします。一方、「Cells(1, 1)」は行・列番号で直接指定できるため、この変換処理が不要で高速です。

余談ですが、範囲の指定はCellsだけでは指定できないため、Rangeを使用してください。単純にセルを1つずつ処理する場面ではCellsを使うと効率的です。

処理時間計測プログラム

Sub SampleSw2_1()
    Dim i As Long

    '高速化有り
    Call StartTimer2
    For i = 1 To 100000
        Cells(1, 1) = "test"
    Next i
    Debug.Print "高速化有りの処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 1 To 100000
        Range("A1") = "test"
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.8930020	1.1515426	22.45%
2	0.8988134	1.2238455	26.56%
3	0.9699078	1.1764129	17.55%
4	0.9038942	1.1397787	20.70%
5	0.8977761	1.1903896	24.58%
平均	0.9126787	1.1763939	22.42%

Value2を使用する

セルに値を代入する際、省略すると既定で「.Value」が使われます。
ただし「.Value」は Currency型や Date型などで変換を行うため、わずかに処理が遅くなります。一方、「.Value2」は変換を行わずにそのまま代入するため、処理速度が速いのが特徴です。
大規模なデータ処理では .Value2 を利用すると高速化につながります。

処理時間計測プログラム

Sub SampleSw2_2()
    Dim i As Long

    '高速化有り
    Call StartTimer2
    For i = 1 To 100000
        Cells(1, 1).Value2 = "test"
        Cells(1, 2).Value2 = "test"
    Next i
    Debug.Print "高速化有りの処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 1 To 100000
        Cells(1, 1).Value = "test"
        Cells(1, 2).Value = "test"
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	1.9112907	1.9158434	0.24%
2	1.8981648	1.9387174	2.09%
3	1.9210030	1.9226822	0.09%
4	1.9181470	2.0000912	4.10%
5	1.9174275	1.9235584	0.32%
平均	1.9132066	1.9401785	1.39%

セルの書式設定などは範囲で行う

セルの書式設定は、1つずつループで処理するよりも、範囲をまとめて指定した方が高速化できます。繰り返し処理に時間がかかるうえ、Excelとのやり取りを大幅に減らせるためです。

処理時間計測プログラム（連続したセルの範囲）

Sub SampleSw2_3()
    Dim i As Long
    
    '高速化有り
    Call StartTimer2
    Range("A1:A1000").Font.Bold = True
    Range("A1:A1000").Interior.Color = vbGreen
    Debug.Print "高速化有りの処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 1 To 1000
        Cells(i, 1).Font.Bold = True
        Cells(i, 1).Interior.Color = vbGreen
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0102399	0.0344948	70.31%
2	0.0007346	0.0317830	97.69%
3	0.0004802	0.0342265	98.60%
4	0.0005268	0.0324309	98.38%
5	0.0005576	0.0323106	98.27%
平均	0.0025078	0.0330492	92.41%

先の例では「連続した範囲」のセルをまとめて処理しました。
一方で、離れたセルをまとめて書式設定する方法は大きく分けて2種類あります。

①．Rangeでカンマ区切りにする方法
例：Range("A1,C1")
→ 処理速度は速いですが、指定文字列が 256文字を超えると、エラー(1004)になる

②．Unionでまとめる方法
例：Union(Range("A1"), Range("C1"))
→ 少し遅めですが、文字数制限がなく、大量のセルでも安全に使えます

大量セルを扱う場合は、安全性を優先してUnionを使うのがおすすめです。

処理時間計測プログラム（連続していないセル）

Sub SampleSw2_3Ex()
    Dim i As Long
    Dim arr(65) As String
    Dim pos As String
    Dim rng As Range
    
    '変更対象のセルを配列に入れる
    For i = 0 To 65
        arr(i) = Cells(1 + i, 1 + (i Mod 2)).Address
    Next i
    
    '高速化（Join）
    Call StartTimer2
    '変更対象のセルをカンマ区切りにして、Rangeで操作できるようにする
    pos = Join(arr, ",")
    pos = Replace$(pos, "$", "")
    If Len(pos) < 256 Then
        Range(pos).Font.Bold = True
        Range(pos).Interior.Color = vbGreen
        Debug.Print "Rangeを使用した高速化：" & EndTimer2
    Else
        'Rangeのカッコ内が256字を超えると、エラーになるので回避する
        Debug.Print "Range内の指定文字列が256字を超えました"
    End If
    
    '高速化（Union）
    Call StartTimer2
    For i = 0 To 65
        If rng Is Nothing Then
            Set rng = Range(arr(i))
        Else
            Set rng = Union(rng, Range(arr(i)))
        End If
    Next i
    rng.Font.Bold = True
    rng.Interior.Color = vbGreen
    Debug.Print "Unionを使用した高速化：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 0 To 65
        Range(arr(i)).Font.Bold = True
        Range(arr(i)).Interior.Color = vbGreen
    Next i
    Debug.Print "高速化無しの処理時間 ：" & EndTimer2
End Sub

※ループを65回から、66回に変更すると”pos”が256字を超えます。

【実行結果】

回数	高速化：Union	高速化：Range	高速化：無	削減効率
1	0.0010520	0.0004616	0.0024424	56.93%
2	0.0009192	0.0006938	0.0030247	69.62%
3	0.0013058	0.0006371	0.0023224	43.78%
4	0.0011292	0.0004974	0.0035700	68.37%
5	0.0009945	0.0004491	0.0022665	56.13%
平均	0.0010800	0.0005478	0.0027250	60.36%

※ 削減効率は、「高速化：Union」と「高速化：無」の比較です。

セルの値を「範囲」と「配列」で操作する

セルの範囲を配列として取得したり、逆に配列（二次元配列）をセル範囲にまとめて書き込むことができます。
この方法は、1セルずつループで読み書きするよりも圧倒的に高速です。Excelシートへのアクセス回数を大幅に減らせるため、数千件～数万件のデータ処理で特に効果を発揮します。

処理時間計測プログラム（範囲への書き込み）

Sub SampleSw2_4_Write()
    Dim arr(1 To 50000, 1 To 1) As String
    Dim i As Long
    
    '変更対象のセルを配列に入れる
    For i = 1 To 50000
        arr(i, 1) = "test"
    Next i
    
    '高速化有り
    Call StartTimer2
    Range("A1:A50000") = arr
    Debug.Print "高速化有りの処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 1 To 50000
        Cells(i, 1) = arr(i, 1)
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0362597	0.4551644	92.04%
2	0.0346937	0.4553564	92.39%
3	0.0352217	0.4777394	92.63%
4	0.0380850	0.4487863	91.52%
5	0.0390539	0.4591532	91.50%
平均	0.0366628	0.4592400	92.02%

読み取りの場合のプログラム及び実行速度です。

処理時間計測プログラム（範囲の読み取り）

Sub SampleSw2_4_Read()
    Dim arr1() As Variant
    Dim arr2(1 To 50000, 1 To 1) As Variant
    Dim i As Long
    
    '高速化有り
    Call StartTimer2
    arr1 = Range("A1:A50000")
    Debug.Print "高速化有りの処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 1 To 50000
        arr2(i, 1) = Cells(i, 1)
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0104468	0.0857119	87.81%
2	0.0100527	0.0987028	89.82%
3	0.0088658	0.1014339	91.26%
4	0.0101674	0.0852221	88.07%
5	0.0120880	0.0888262	86.39%
平均	0.0103241	0.0919790	88.78%

型の宣言を見直す

演算用の変数は、Variant型で宣言しない

Variantで変数を宣言すると実行速度が遅くなります。
これは、変数への代入時に何の型のデータが格納されるかを判断してから、データを格納するためです。また、Variant型の変数にまつわるエラーが発生した時、的確なエラーメッセージが表示されません。
特別な理由がない限り、Variant型は避けるのが基本です。

処理時間計測プログラム

Sub SampleSw3_1_Long(ByVal i As Long, ByVal j As Long)
    Dim buf As Long
    buf = i * j
End Sub
Sub SampleSw3_1_Variant(ByVal i As Variant, ByVal j As Variant)
    Dim buf As Variant
    buf = i * j
End Sub

Sub SampleSw3_1()
    Dim i As Long
    
    '高速化有り
    Call StartTimer2
    For i = 1 To 1000000
        SampleSw3_1_Long i, 1
    Next i
    Debug.Print "高速化有りの処理時間：" & EndTimer2
    
    '高速化無し
    Call StartTimer2
    For i = 1 To 1000000
        SampleSw3_1_Variant i, 1
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0345867	0.0842177	58.93%
2	0.0393238	0.0829095	52.57%
3	0.0388548	0.0817719	52.48%
4	0.0377164	0.0843544	55.29%
5	0.0343196	0.0819154	58.10%
平均	0.0369603	0.0830338	55.49%

CreateObjectを乱用しない（参照設定を使用する）

Object型で変数を宣言し、後から CreateObject で型を設定すると、速度は遅くなります。
これは「後期バインディング」と呼ばれ、型が実行時に決定されるためです。一方、参照設定を行い、変数の宣言時に型を指定する方法は「早期バインディング」と呼ばれ、コンパイル時に型が決まるため処理が速く、補完やエラーチェックの利点もあります。

処理時間計測プログラム

'早期バインディング
Sub SampleSw3_2_Fast()
    '参照設定で「Microsoft Scripting Runtime」にチェックを入れること！
    Dim fso As FileSystemObject  
    Dim filePath As String
    Dim fileExists As Boolean
    filePath = "C:\Users\Public\TestFolder\sample.txt"
    Set fso = New FileSystemObject
    fileExists = fso.fileExists(filePath)
    Set fso = Nothing
End Sub
'後期バインディング
Sub SampleSw3_2_Slow()
    Dim fso As Object
    Dim filePath As String
    Dim fileExists As Boolean
    filePath = "C:\Users\Public\TestFolder\sample.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    fileExists = fso.fileExists(filePath)
    Set fso = Nothing
End Sub

Sub SampleSw3_2()
    Dim i As Long
    
    '高速化有り
    Call StartTimer2
    For i = 1 To 10000
        SampleSw3_2_Fast
    Next i
    Debug.Print "高速化有りの処理時間：" & EndTimer2
    
    '高速化無し
    Call StartTimer2
    For i = 1 To 10000
        SampleSw3_2_Slow
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	1.6463741	1.7565716	6.27%
2	1.7093624	1.7534302	2.51%
3	1.6078526	1.7722415	9.28%
4	1.6226602	1.7728392	8.47%
5	1.6569233	1.7526690	5.46%
平均	1.6486345	1.7615503	6.41%

プログラムを見直す

文字列操作の関数の返す型を指定する

Replace関数やTrim関数など一部の文字列関数は、通常、Variant型を返します。
関数名の後ろに「$」を付けると String型で返すようになり、型判定の処理が不要になるため、高速化できます。

処理時間計測プログラム

Sub SampleSw4_1()
    Dim i As Long
    Dim str As String
    
    '高速化有り
    Call StartTimer2
    For i = 1 To 100000
        str = Replace$("test", "t", "")
        str = Left$("090-1234-5678", 3)
        str = Mid$("090-1234-5678", 5, 4)
        str = Right$("090-1234-5678", 3)
    Next i
    Debug.Print "高速化有りの処理時間：" & EndTimer2
    
    
    '高速化無し
    Call StartTimer2
    For i = 1 To 100000
        str = Replace("test", "t", "")
        str = Left("090-1234-5678", 3)
        str = Mid("090-1234-5678", 5, 4)
        str = Right("090-1234-5678", 3)
    Next i
    Debug.Print "高速化無しの処理時間：" & EndTimer2
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0423193	0.0484459	12.65%
2	0.0418243	0.0491961	14.98%
3	0.0404206	0.0517744	21.93%
4	0.0479333	0.0588546	18.56%
5	0.0430535	0.0515864	16.54%
平均	0.0431102	0.0519715	17.05%

Select や Activate を乱用しない

Select や Activate を使うと、VBA は一度そのセルを「選択状態」にしてから操作を行います。
つまり「選択する」という無駄な処理が毎回挟まるため、特にループ処理で繰り返すと速度低下が顕著になります。また、ユーザー操作に依存するため「対象が正しく選択されていないとエラーになる」などの不安定要因にもなります。
そのため、「Range("A1").Value = 1」のように、直接指定する書き方を心掛けた方が、速くて安全です。

処理時間計測プログラム

Sub SampleSw4_2()
    Dim i As Long
    Dim arr(100) As String
    
    '変更対象のセルを配列に入れる
    For i = 0 To 100
        arr(i) = Cells(1 + i, 1 + (i Mod 3)).Address
    Next i
    
    'Select / Active 不使用
    Call StartTimer2
    For i = 0 To 100
        Range(arr(i)).Font.Bold = True
        Range(arr(i)).Interior.Color = vbGreen
    Next i
    Debug.Print "Select / Active 不使用の処理時間：" & EndTimer2
    
    '高速化無し（Select）
    Call StartTimer2
    For i = 0 To 100
        Range(arr(i)).Select
        Selection.Font.Bold = True
        Selection.Interior.Color = vbGreen
    Next i
    Debug.Print "Select の処理時間：" & EndTimer2

    '高速化無し（Activate）
    Call StartTimer2
    For i = 0 To 100
        Range(arr(i)).Activate
        ActiveCell.Font.Bold = True
        ActiveCell.Interior.Color = vbGreen
    Next i
    Debug.Print "Activate の処理時間：" & EndTimer2
End Sub

【実行結果】

回数	Select / Active 不使用	低速 (Select)	低速 (Activate)	削減効率
1	0.0075406	0.8039573	0.7941685	99.05%
2	0.0062256	0.8450336	0.8725052	99.29%
3	0.0075544	0.7890638	0.7818316	99.03%
4	0.0078616	0.7731739	0.7758445	98.99%
5	0.0072320	0.7843496	0.7701325	99.06%
平均	0.0072828	0.7991160	0.7988960	99.09%

オブジェクトに複数回接続する時、変数を使う

何度も同じオブジェクトにアクセスする場合は、オブジェクト変数に代入してから使うと処理が速くなります。
これは、毎回「どのシート？どのセル？」と探す手間を省けるからです。

・変数を使用しない場合
　ThisWorkbook → ActiveSheet → Range("A1") と毎回アクセスする

・変数を使用する場合
　ActiveSheet → Range("A1") とアクセスするため、手順を減らせる

さらに、処理速度の向上だけでなく、コードが短くなり可読性の向上などのメリットがあります。なお、オブジェクトをまとめて扱うもう一つの方法として「Withステートメント」もあります。これは「特定のオブジェクトに対する処理を一括で書く」方法で、若干ですが高速化に役立ちます。

「Setを使用した場合」、「Setを使用しない場合」、「Withを使用した場合」で処理速度を比較してみます。

処理時間計測プログラム

Sub SampleSw4_3()
    Dim i As Long
    Dim ws As Worksheet
    
    'Set による高速化
    Call StartTimer2
    Set ws = ThisWorkbook.ActiveSheet
    For i = 0 To 100000
        ws.Cells(1, 1) = "test1"
        ws.Cells(2, 1) = "test2"
    Next i
    Set ws = Nothing
    Debug.Print "Setを使用した処理時間 ：" & EndTimer2

    'With を使用した場合
    Call StartTimer2
    With ThisWorkbook.ActiveSheet
        For i = 0 To 100000
            .Cells(1, 1) = "test1"
            .Cells(2, 1) = "test2"
        Next i
    End With
    Debug.Print "Withを使用した処理時間：" & EndTimer2
    
    '高速化無し
    Call StartTimer2
    For i = 0 To 100000
        ThisWorkbook.ActiveSheet.Cells(1, 1) = "test1"
        ThisWorkbook.ActiveSheet.Cells(2, 1) = "test2"
    Next i
    Debug.Print "高速化無しの処理時間  ：" & EndTimer2
End Sub

【実行結果】

回数	高速化/Set	With	高速化：無	削減効率※
1	1.8132278	2.1451037	2.2357883	18.90%
2	1.8197145	2.1559621	2.2107075	17.69%
3	1.8500110	2.1778031	2.2248063	16.85%
4	1.8568450	2.1637495	2.2528655	17.58%
5	1.8498699	2.2412573	2.2091632	16.26%
平均	1.8379336	2.1767750	2.226666	17.46%

※ 削減効率は、「高速化/Set」と「高速化：無」の比較です。

Excel関数で、集計・抽出を行う

VBAでは、Excelに用意されているワークシート関数を使用することができます。
特に、セル範囲のデータを加工・検索する処理では、これらの関数を活用することで処理速度を大幅に向上できる場合があります。ただし、VLOOKUP 関数などの一部の関数は、エラー処理を組み込んでおく必要があります。

処理時間計測プログラム

Sub SampleSw4_4()
    Dim i As Long
    Dim buf1 As Variant
    Dim buf2 As Variant
    Dim searchData As Variant
    
    '検索するデータ
    searchData = 1000
    
    '仮のデータを入力させる
    For i = 1 To 2000
        Cells(i, 2) = i
        Cells(i, 3) = "C" & i
    Next i
    
    '高速化あり
    Call StartTimer2
    On Error Resume Next
    buf1 = Application.WorksheetFunction.VLookup(searchData, _
                                    Range("B1:C2000"), 2, False)
    If buf1 = "" Then
        buf1 = "検索データなし"
    End If
    On Error GoTo 0
    Debug.Print "高速化有りの処理時間：" & EndTimer2()

    '高速化なし
    Call StartTimer2
    For i = 1 To 2000
        If Cells(i, 2) = searchData Then
            buf2 = Cells(i, 3)
            Exit For
        End If
    Next i
    If buf2 = "" Then
        buf2 = "検索データなし"
    End If
    Debug.Print "高速化無しの処理時間：" & EndTimer2()
End Sub

【実行結果】

回数	高速化：有	高速化：無	削減効率
1	0.0000603	0.0025650	97.65%
2	0.0000610	0.0023492	97.41%
3	0.0000590	0.0023471	97.49%
4	0.0000559	0.0023452	97.62%
5	0.0000552	0.0023468	97.65%
平均	0.0000583	0.0023910	97.56%

※ データが４，５件の場合、For文の方が速くなる場合があります。

その他

処理途中でDebug.Printしすぎない

プログラム中に Debug.Print を書くと、イミディエイトウィンドウに変数などの値を出力できます。テストやデバッグで非常に便利な機能ですが、出力のたびに画面描画が行われるため、処理が大きく遅くなる点に注意が必要です。
特に反復処理の中に「Debug.Print」を入れると、膨大な出力が発生して処理速度が極端に低下してしまいます。これは、VBE画面を閉じていても同じです。
そのため、「Debug.Print」は『必要な箇所だけに限定して使う』、『ループ中では件数を絞る』などの工夫をすると、安全に活用できます。

処理時間計測プログラム

Sub SampleSw5_1()
    Dim i As Long
    Dim num(1 To 3) As Long
    
    '高速化有り
    Call StartTimer2
    For i = 0 To 1000
        num(1) = i: num(2) = i: num(3) = i
    Next i
    Cells(1, 1) = "Debug.Print無しの処理時間：" & EndTimer2
    
    '高速化有り
    Call StartTimer2
    For i = 0 To 1000
        num(1) = i: num(2) = i: num(3) = i
        Debug.Print "num(1) = " & num(1) & _
                    ", num(2) = " & num(2) & _
                    ", num(3) = " & num(3)
    Next i
    Cells(2, 1) = "Debug.Print１回の処理時間：" & EndTimer2

    '高速化無し
    Call StartTimer2
    For i = 0 To 1000
        num(1) = i: num(2) = i: num(3) = i
        Debug.Print "num(1) = " & num(1)
        Debug.Print "num(2) = " & num(2)
        Debug.Print "num(3) = " & num(3)
    Next i
    Cells(3, 1) = "Debug.Print３回の処理時間：" & EndTimer2
End Sub

【実行結果】

VBE表示した状態で、VBEの実行ボタンより実行した場合

回数	表示０回	表示１回	表示３回	削減効率※
1	0.0000144	1.5005702	4.4245209	99.99%
2	0.0000282	1.5833733	4.3154215	99.99%
3	0.0000430	1.5405794	4.4271376	99.99%
4	0.0000142	1.5580501	4.3378995	99.99%
5	0.0000410	1.5564772	4.3513737	99.99%
平均	0.0000282	1.5478100	4.3712710	99.99%

VBEを閉じた状態で、シート上のボタンからプログラムを実行した場合

回数	表示０回	表示１回	表示３回	削減効率※
1	0.0000302	1.3383131	3.9010706	99.99%
2	0.0000147	1.3108430	3.9023848	99.99%
3	0.0000303	1.3559714	3.9367906	99.99%
4	0.0000148	1.3269674	3.9245624	99.99%
5	0.0000307	1.2987340	3.8792400	99.99%
平均	0.0000241	1.3261660	3.9088100	99.99%

※ 削減効率は、「表示０回」と「表示３回」の比較です。

値渡しと参照渡しの処理速度の違い

自作関数では、呼び出し時に値を受け取るための「引数」を指定できます。引数の渡し方には「値渡し（ByVal）」と「参照渡し（ByRef）」の２通りがあります。ここでは、値渡しと参照渡しで、処理速度に違いがあるかを、Long型・Double型・String型の３種類のデータ型で比較してみます。

処理時間計測プログラム

Sub SampleSw5_2_ByRef_Long(ByRef i&, ByRef j&)
    Dim buf&
    buf = i * j
End Sub
Sub SampleSw5_2_ByVal_Long(ByVal i&, ByVal j&)
    Dim buf&
    buf = i * j
End Sub

Sub SampleSw5_2_ByRef_Double(ByRef i#, ByRef j#)
    Dim buf#
    buf = i * j
End Sub
Sub SampleSw5_2_ByVal_Double(ByVal i#, ByVal j#)
    Dim buf#
    buf = i * j
End Sub

Sub SampleSw5_2_ByRef_String(ByRef i$, ByRef j$)
    Dim buf$
    buf = i & j
End Sub
Sub SampleSw5_2_ByVal_String(ByVal i$, ByVal j$)
    Dim buf$
    buf = i & j
End Sub

Sub SampleSw5_2()
    Dim i As Long
    
    'ByRef + Long
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByRef_Long i, 1
    Next i
    Debug.Print "ByRef+Longの処理時間：" & EndTimer2
    
    'ByVal + Long
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByVal_Long i, 1
    Next i
    Debug.Print "ByVal+Longの処理時間：" & EndTimer2
    
    'ByRef + Double
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByRef_Double 1.2345, 5.6789
    Next i
    Debug.Print "ByRef+Doubleの処理時間：" & EndTimer2
    
    'ByVal + Double
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByVal_Double 1.2345, 5.6789
    Next i
    Debug.Print "ByVal+Doubleの処理時間：" & EndTimer2
    
    'ByRef + String
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByRef_String "ABC", "CDE"
    Next i
    Debug.Print "ByRef+Stringの処理時間：" & EndTimer2
    
    'ByVal + String
    Call StartTimer2
    For i = 1 To 100000
        SampleSw5_2_ByVal_String "ABC", "CDE"
    Next i
    Debug.Print "ByVal+Stringの処理時間：" & EndTimer2
End Sub

【実行結果】

Long型

回数	ByRef／Long	ByVal／Long	比較
1	0.0042009	0.0033418	79.55%
2	0.0069627	0.0034163	49.07%
3	0.0063656	0.0032605	51.22%
4	0.0059286	0.0035241	59.44%
5	0.0059131	0.0040137	67.88%
平均	0.0058742	0.0035113	59.77%

Double型

回数	ByRef／Double	ByVal／Double	比較
1	0.0050191	0.0037712	75.14%
2	0.0050649	0.0036585	72.23%
3	0.0051443	0.0036384	70.73%
4	0.0049380	0.0037541	76.02%
5	0.0050920	0.0036761	72.19%
平均	0.0050517	0.0036997	73.24%

String型

回数	ByRef／String	ByVal／String	比較
1	0.0127373	0.0156570	122.92%
2	0.0136751	0.0163442	119.52%
3	0.0128056	0.0155236	121.23%
4	0.0128125	0.0152671	119.16%
5	0.0134993	0.0148518	110.02%
平均	0.0131060	0.0155287	118.49%

警告を表示させないと速くなる？

Application.DisplayAlerts = False とすると、ファイル保存や削除時の確認メッセージを表示しない設定にできます。元に戻す場合は True を指定します。
「この設定で処理が速くなるのでは？」とよく思われますが、結論としては速くなりません。むしろ、フラグの切替処理にオーバーヘッドがあるため、頻繁に切り替えると逆に遅くなります。
そのため、使いどころは「ブックを閉じるときに保存確認を出さない」など、ユーザー操作を省く目的に限定するのが安全です。速度向上を目的に使うのは避けましょう。

処理時間計測プログラム

Sub SampleSw5_3()
    Dim i As Long
    Dim j As Long
    Dim buf As String
    
    '通常通り処理をする
    Call StartTimer
    For i = 1 To 100000
        buf = "test"
    Next i
    Debug.Print "DisplayAlertsを切り替えない場合の処理時間：" & EndTimer

    '警告などを表示しない設定で処理をする
    Call StartTimer
    Application.DisplayAlerts = False
    For i = 1 To 100000
        buf = "test"
    Next i
    Application.DisplayAlerts = True
    Debug.Print "DisplayAlertsを切り替えた場合の処理時間：" & EndTimer
End Sub

【実行結果】

回数	警告の操作なし	警告オフに切り替え	比率
1	0.0027504	0.0088009	319.99%
2	0.0023799	0.0074404	312.63%
3	0.0028898	0.0081828	283.16%
4	0.0029439	0.0074425	252.81%
5	0.0033926	0.0086217	254.13%
平均	0.0028713	0.0080977	282.02%