VBA With Statement

The With statement allows a group of statements to refer to a single object without needing to repeat the object variable's name. With blocks are useful for avoiding repetition, making code more flexible, making code more readable, simplifying syntax, and controlling the lifetime of objects. To access the object's members inside a With statement, use the dot (".") operator.

Reducing Repetition And Increasing Flexibility

With blocks can make code better by reducing repetition. Reducing repetition makes code easier to write, read, and maintain. Using a with statement, the name of an object only needs to be typed once, read once, and in the case where code needs to be changed, updated once.

Public Sub Example()

    '''Not using With statement

    Range("A1").Value2 = "Hello, World!"
    Range("A1").Interior.Color = vbRed
    Range("A1").Font.Bold = True
    Range("A1").Copy Range("B2")

End Sub

Public Sub Example()

    '''Using With statement

    With Range("A1")
        .Value2 = "Hello, World!"
        .Interior.Color = vbRed
        .Font.Bold = True
        .Copy Range("B2")
    End With

End Sub

Controlling Object Lifetime

With blocks can be used to instantiate an object in-line which terminates when the With block ends. The New keyword or the CreateObject function can be used to instantiate the object.

Public Sub Example()

    With New Collection        
        .Add 1
        .Add 2
        .Add 3
        Debug.Print .Count
    End With

    With CreateObject("Scripting.Dictionary")
        .Add "A", 1
        .Add "B", 2
        .Add "C", 3
        Debug.Print .Count
    End With

End Sub

Nesting With Statements

With statements can be nested inside each other. The dot operator inside a with block only refers to the most immediate containing With block.

Public Sub Example()

    With Range("A1:A10")

        .Interior.ColorIndex = 4

        .Value = WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

        With .Font
            .Bold = True
            .Italic = True
            .Underline = True
        End With

    End With

End Sub