[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)

---

If you reference the same objects multiple times (ex. Ranges, Worksheets, Workbooks), consider using the "With" statement rather than referencing them individually.

1. Description

2. How to use

With statement example:

Sub Faster_Example()
    With Sheets("Sheet2")
        .Range("D9").FormulaR1C1 = "example"
        .Range("D12").FormulaR1C1 = "demo"
        .Range("D9").Font.Bold = True
        .Range("D12").Font.Bold = True
    End With
End Sub

no "With" statement:

Sub Slow_Example()
    Sheets("Sheet2").Range("D9").FormulaR1C1 = "example"
    Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo"
    Sheets("Sheet2").Range("D9").Font.Bold = True
    Sheets("Sheet2").Range("D12").Font.Bold = True
End Sub

3. Performance Test

I have performed the following test

Sub Test_WithStatement()
'------------------------------------------------------------------------------
'Declare
    Dim i               As Long                     'Loop counter
    Dim MaxIter         As Long                     'Max # of iterations
    Dim iMethod         As Integer                  'Performance method
    Dim cPM             As cPerformanceMonitor      'Performance tracker
'------------------------------------------------------------------------------
'Initialize
    MaxIter = 1000
    iMethod = 5
    Set cPM = New cPerformanceMonitor
'------------------------------------------------------------------------------
'Test1        
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        Sheets("Sheet2").Range("D9").FormulaR1C1 = "example"
        Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo"
        Sheets("Sheet2").Range("D9").Font.Bold = True
        Sheets("Sheet2").Range("D12").Font.Bold = True
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - " & "Without statement"
'------------------------------------------------------------------------------
'Test2
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        With Sheets("Sheet2")
            .Range("D9").FormulaR1C1 = "example"
            .Range("D12").FormulaR1C1 = "demo"
            .Range("D9").Font.Bold = True
            .Range("D12").Font.Bold = True
        End With
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - " & "With statement 1"
'------------------------------------------------------------------------------
'Test3
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        With Sheets("Sheet2").Range("D9")
            .FormulaR1C1 = "example"
            .Font.Bold = True
        End With
        With Sheets("Sheet2").Range("D12")
            .FormulaR1C1 = "demo"
            .Font.Bold = True
        End With
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - " & "With statement 2"
'------------------------------------------------------------------------------
'Exit
    Set cPM = Nothing
End Sub

4. Results

The results are the following:

• 00:00:07 - 143 ms - 595 µs - 200 ns - Without statement
• 00:00:07 - 421 ms - 972 µs - 200 ns - With statement 1
• 00:00:07 - 516 ms - 359 µs - 500 ns - With statement 2

5. Conclusions

Using the "With" statement is slightly slower to process but makes your code easier to read and simplifies.

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)