How to use WITH statement in VBA?

The With statement in VBA (Visual Basic for Applications) is used to perform a series of statements on a specific object without requalifying the name of the object. It’s a useful feature for making your code more readable and efficient, especially when you’re performing multiple operations on the same object.

Here’s how you can use the With statement:

Basic Syntax

VBA
With object
    ' Statements that use properties or methods of the object
End With

object: This is the object you want to perform multiple actions on.

Example Usage

Suppose you’re working with an Excel worksheet and you want to format a range of cells. Without With, you might write:

VBA
Worksheets("Sheet1").Range("A1").Font.Bold = True
Worksheets("Sheet1").Range("A1").Font.Italic = True
Worksheets("Sheet1").Range("A1").Font.Size = 12

With the With statement, this can be simplified to:

VBA
Example Usage:

Suppose you're working with an Excel worksheet and you want to format a range of cells. Without With, you might write:

VBAWorksheets("Sheet1").Range("A1").Font.Bold = True
Worksheets("Sheet1").Range("A1").Font.Italic = True
Worksheets("Sheet1").Range("A1").Font.Size = 12

Nested With Statements

You can nest With statements when working with objects that contain other objects. For example:

VBA
With Worksheets("Sheet1")
    With .Range("A1")
        .Value = "Test"
        .Font.Name = "Arial"
    End With
End With

Improving Readability and Efficiency

  • The With statement makes your code more readable by eliminating the need to repeat the object reference for each property or method call.
  • It can also improve the efficiency of your code, particularly when the object reference is a complex expression that VBA would otherwise need to evaluate multiple times.

Error Handling

As with any object manipulation, ensure proper error handling is in place, especially if there’s a possibility that the object might not exist or be accessible.

Limitation

The only limitation is that you can’t use With to set a reference to an object. You must already have an object to use With.

Using the With statement in VBA is an excellent practice for writing cleaner, more maintainable code, particularly when dealing with objects that have multiple properties or methods you need to access or modify.

Switch the language

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project