What are With Statements and How to Use Them - VBA Tutorial

In VBA, a with statement is used to reference an object multiple times within a specific block of code. Reducing typing and creating more readable code.

Contents:

1. What are With Statements, Why Use Them?

2. Examples

2.1 Example - Basic With Statement

2.2 Example - Nested With Statement

What are With Statements? Should You Use Them?

The with statement allows you to specify an object reference once and then use it repeatedly within a block of code. It can make your code more readable and reduce the amount of typing required.

Without a with statement, a block of code may look like this, using the same repetitive code to call the same object over and over again:

 Range("A1:A10").Font.Bold = True
 Range("A1:A10").Font.Color = vbRed
 Range("A1:A10").Interior.Color = vbYellow

This can be shortened and cleaned up using a with statement:

 With Range("A1:A10")
     .Font.Bold = True
     .Font.Color = vbRed
     .Interior.Color = vbYellow
 End With

Here, any line contained in the with statement will use the original range object laid out in the first 'With line"

There are a few reasons why you might want to use the With statement in VBA:

1. Readability: Using the With statement can make your code more readable, especially if you are working with complex objects that have many properties and methods. By specifying the object reference once at the beginning of the block of code, you can avoid repeating it throughout the rest of the code. This can make it easier to understand what is happening in the code, especially when you are working with a large or complex object.

2. Convenience: The With statement can also be convenient to use, as it allows you to access multiple properties or methods of an object without having to specify the object reference each time. This can save you some typing and make your code more concise.

3. Performance: In some cases, using the With statement may be slightly faster than repeatedly specifying the object reference. However, this is generally not a significant difference in performance, and it is usually more important to consider the readability and maintainability of your code.

Overall, the With statement can help to make your code more readable and convenient to work with. However, it's important to use it judiciously and not overuse it, as it can make your code harder to understand if it is not used appropriately.

Examples

Example 1 - Basic With Statement in VBA

Sub with_example()
    Dim rng As Range
    Set rng = Range("A1:A10")

    With rng
        .Font.Bold = True
        .Font.Color = vbRed
        .Interior.Color = vbYellow
    End With
End Sub

In this example, the With statement specifies the range object 'rng' as the object to be referred to within the block of code. The properties Font.Bold, Font.Color, and Interior.Color are all properties of the Range object, and the With statement allows you to access them without having to specify the object reference 'rng' each time.

Example 2 - Nested With Statement in VBA

You can also nest With statements, which allows you to specify multiple object references within a single block of code. For example:

Sub nested_with_example()
    
    Dim ws As Worksheet
    Set ws = ActiveSheet

    With ws
        .Range("A1:A10").Font.Bold = True
        .Range("A1:A10").Font.Color = vbRed
        .Range("A1:A10").Interior.Color = vbYellow

        With .Range("B1:B10")
            .Font.Italic = True
            .Font.Color = vbBlue
            .Interior.Color = vbGreen
        End With
     End With
 End Sub

In this example, the outer With statement specifies the 'ws' object as the object to be referred to, and the inner With statement specifies the Range("B1:B10") object as the object to be referred to within the inner block of code.