How to use With Statement in VBA (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 a 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
  • With: It’s the starting keyword for the statement that tells VBA that you are specifing the object mentioned.
  • Object: Here you need to specify the object that you want to use.
  • Statement: A statement(s) that you want to run with the specified object.
  • 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 statement in depth. Let’s say you want to change, font color, size, and name of the 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:

  • First, type the keyboard “With”.
  • After that, use the VBA Range to specify the cell A1 with using the font object to specify the font object.
    2-use-the-vba-range
  • Next you need to enter the code for all the three properties and the values that you want to apply.
    3-code-for-all-three-properties
  • In the end, use the keyword “End With” to end the statement.
    4-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 into it.

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

So, when you use the “With” statement and specify an object it tells VBA that all the statements that you have written needs to run with the object mentioned.

It not only gives you a structured code but also makes a macro run faster and gives it a 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 do 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, like I have in the following example:

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

  • First, you need to declare a variable to use a worksheet object.
    7-steps-to-write-nested
  • After that, as I said, you need to use For Each Loop. So, you need to write a code with the loop that can write loop through all the worksheets.https://excelchamps.com/wp-content/uploads/2020/05/8-write-code-with-the-loop.png
    8-write-code-with-the-loop
  • 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.
    9-use-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