VBA With

Publication Date :

Blog Author :

Download FREE VBA With Excel Template and Follow Along!
VBA With Excel Template.xlsm

Table Of Contents

arrow

With Statement in Excel VBA

A With statement is used in VBA to access all the properties and methods of a mentioned object. Therefore, we need to supply which VBA object we are referring to first, close the With statement with End With, then inside this statement. Then, we can perform all the property changes and methods of the mentioned object.

Below is the syntax of With Statement in VBA.

With      End With

The object is nothing but a range of cells or cells we are referring to, and then we can change the properties and perform all the methods associated with that specific cell or cell.

How to Use With Statement in Excel VBA?

Below are some examples of using With statements in Excel VBA.

Example #1

Assume you have a certain value in the A1 cell. We have entered the text as "Excel VBA" in cell A1.

Excel VBA With Statement Example 1.1

Now, we need to do some tasks for this cell, i.e., formatting in excel.

We want to change the font size, name, and interior color, insert a border, etc. Typically, we refer to the cell using VBA RANGE object.

Code:

Sub With_Example1()

    Range ("A1")

End Sub
Excel VBA With Statement Example 1.2

Now, we access this cell's "font" property to change the font size.

Excel VBA With Statement Example 1.3

Under the FONT property, we access the Size property and enter size by putting an equal sign.

Code:

Sub With_Example1()

    Range("A1").Font.Size = 15

End Sub
Excel VBA With Statement Example 1.4

Now similarly, we do other formatting tasks, as shown below.

Code:

Sub With_Example1()

    Range("A1").Font.Size = 15
    Range("A1").Font.Name = "Verdana"
    Range("A1").Interior.Color = vbYellow
    Range("A1").Borders.LineStyle = xlContinuous

End Sub
Excel VBA With Statement Example 1.5

It will do all the mentioned tasks, but if you look at the code to perform every formatting activity. We have supplied the cell address every time. It makes the code look lengthy and time-consuming.

Now, we will use VBA With a statement to reduce the entry of cell addresses every time. Open WITH statement in Excel VBA and supply cell address.

Code:

Sub With_Example1()

    With Range("A1")

End Sub
Excel VBA With Statement Example 1.6

Inside the With statement, put a dot to see all the properties and methods of cell A1.

Excel VBA With Statement Example 1.7

The first formatting activity is changing font size, so access FONT. Under this, access the SIZE property.

Code:

Sub With_Example1()

    With Range("A1")
         .Font.Size = 15

End Sub
Example 1.8

Similarly, supply other formatting codes and close VBA With statement.

Code:

Sub With_Example1()

    With Range("A1")
         .Font.Size = 15
         .Font.Name = "Verdana"
         .Interior.Color = vbYellow
         .Borders.LineStyle = xlContinuous
    End With

End Sub
Example 1.9

Run the code to see all the formatting in the mentioned object, i.e., cell A1.

VBA With Example1 - Output

So, all the formatting applies to the cell. Look how cool this technique is.

Example #2

If you want to change all the properties related to the font, you can mention the cell and FONT property.

Code:

Sub With_Example2()

    With Range("A1").Font

    End With

End Sub
Example 2.1

Inside the VBA With Statement, we can see the IntelliSense list. It will show properties and methods related to FONT property only.

Example 2.2

We can perform any set of activities with this now.

Code:

Sub With_Example2()

    With Range("A1").Font
         .Bold = True 'Font will be Bold
         .Color = vbAlias 'Font color will be Alias
         .Italic = True 'Font will be italic style
         .Size = 20 ' Font size will be 20
         .Underline = True 'Font will be underlined
    End With

End Sub
Example 2.3

It will show the result of this below.

VBA With Example 2- Output

Example #3

The below code will access only cell border-related properties.

Code:

Sub With_Example3()

    With Range("B2").Borders
         .Color = vbRed 'Border color will be red
         .LineStyle = xlContinuous 'Full border
         .Weight = xlThick 'Thick border
    End With

End Sub
Example 3.1

The result of this code is as follows.

VBA With Example 3 - Output

Things to Remember

  • A With statement is used to minimize the code.
  • We need to supply the object first for the With statement.
  • Once we supply the specific object, we can access only that object's properties and methods.