VBA With Statement (With – End With)

Key Points

• Use the VBA With Statement to minimize the code.
• You can use a single object at a time while using the With Statement.

What is VBA With Statement

VBA With is a statement to specify an object for once and then run multiple statements for it. In simple words, by using the “WITH” statement to specify an object, and after that, you can access all the properties and methods in one go. It starts with the “With” and ends with “End With”.

Syntax of VBA With Statement

Following is the syntax that you need to use for using With statement in VBA:

With [OBJECT]
      [Statement]
End With

1. With: It’s the starting keyword for the statement that tells VBA that you are specifying the object mentioned.
2. Object: Here, you need to specify the object that you want to use.
3. Statement: A statement(s) that you want to run with the specified object.
4. End With: It’s the ending keyword for the statement.

Example to understand the VBA With Statement

Let’s take an example to understand with the statement in depth. Let’s say you want to change, the font color, size, and font name of cell A1. For doing all these three things you need to access three different properties of VBA font and the code would be:

Range("A1").Font.Color = RGB(0, 0, 255)
Range("A1").Font.Size = 16
Range("A1").Font.Name = "Consolas"

But here we going to use the With Statement instead of the above code. To use the VBA With Statement, you can use the following steps:

1. First, type the keyboard “With”.
2. After that, use the VBA Range to specify the cell A1 with using the font object to specify the font object.
   
3. Next, you need to enter the code for all three properties and the values that you want to apply.
   
4. In the end, use the keyword “End With” to end the statement.
   

Here’s the code that you have just written.

Sub MyMacro()

With Range("A1").Font
        .Color = RGB(0, 0, 255)
        .Size = 16
        .Name = "Consolas"       
End With

End Sub

Now let’s have a look at both codes and try to understand what is the difference that we have got by using the “With” statement.

In the first code, you have used the font object three times and then further used the property that you want to access and want to make changes to it.

But in the second code, you have used the With statement and specified the font object at once. And then three lines of code within the “With” statement to access all three properties.

When you use the “With” statement and specify an object, it tells VBA that all the statements that you have written need to run with the object mentioned. It not only gives you a structured code but also makes a macro run faster and gives it greater efficiency.

Nested With Statement

I told you in the starting that you can only specify a single object to use the With statement. But there is a way to use more than one or even multiple objects with it. That’s by using VBA LOOPS, and we are going to use FOR EACH LOOP to loop through multiple objects and use the “With”.

Let me tell you an example; you need to loop through all the worksheets and do apply all the following things to all the cells in each worksheet.

• Font Size: 16
• Font: Verdena

And in the workbook, you have five worksheets, as I have in the following example:

Use the following steps to write a nested “With” statement.

1. First, you need to declare a variable to use a worksheet object.
   
2. After that, as I said, you need to use For Each Loop. So, you need to write a code with the loop that can loop through all the worksheets.
   
3. Now, enter the with statement which can apply font setting that we have discussed above. For referrring to all the cells in the worksheet you can use the “Cells” while specifing the object.
   

Here’s the full code that you have just written.

Sub MyMacro()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    With Cells  
        .Font.Size = 16
        .Font.Name = "Verdena"       
    End With   
Next ws

End Sub