VBA With Statement (Excel VBA)

With statement is a widely used statement in VBA. With statement is used to execute one or more statements on an object or on a user defined type. Also it is mandatory to use “End With'' after the statements. So this is the Syntax of the With statement.

With object      Statements End With

Now let’s look at how to use With statements in VBA projects. Let’s consider this sample form.

Suppose that we want to create the drop down list of the “Gender” combobox. We can do that using a With statement as follows. Name of the combobox is cboGender.

Private Sub UserForm_Initialize()      With cboGender          .AddItem "Male"          .AddItem "Female"      End With End Sub

According to above code, items are added to the dropdown list when the form is initialized. So this is what will happen when you click on the dropdown icon of the “Gender” combobox.

So in the above example code, we referred to the combo box once as we used the With statement. So if we rewrite that code without a With statement we have to refer to it twice like this.

Private Sub UserForm_Initialize()      cboGender.AddItem "Male"      cboGender.AddItem "Female" End Sub

So if we want to add lots of items to a combobox and if we don’t use a With statement then we have to refer to the combobox many times. But if we use With statement we can refer to the combobox once and add any amount of items to the list.

We can also use the With statement for objects related to the worksheets as well. First let’s look at how to delete a sheet using a With statement. Suppose we have a workbook with 5 sheets like this.

Following is the macro which uses the With statement to delete a worksheet. In this example macro is used to delete “Sheet4”.

Sub DeleteSheet() Dim WS As Worksheet     Set WS = Worksheets("Sheet4")     Application.DisplayAlerts = False     With WS          .Delete     End With     Application.DisplayAlerts = True End Sub

This would be the result after running the macro.

Next let’s see how we can use the With statement for Range objects. We will use Range(“C5”) for this example.

Below subroutine can change the value and can do some small changes to the formatting of the range.

Sub RangeExample()      With Range("C5")          .WrapText = True          .ColumnWidth = 25          .Value = "VBA With Statement - www.excelvbasolutions.com"      End With End Sub

Here is the result of the above macro.

Also you can use nested With statements in your subroutines as well. Here is an example.

Sub NestedWithStatementExample()      With Range("B3")          .Value = "Test"          With .Font              .Name = "Calibri"              .Size = 16              .Bold = True              .Underline = xlUnderlineStyleSingle              .Color = vbRed          End With      End With End Sub

This is the result of the above example subroutine.

Note that you have to use dot(.) before the object in the inner With statement. So in the above example we used

With .Font

Instead of

With Font