Module 1 - Getting Started in VBA
Lesson 1.4 - How VBA Works
Topic 1.4.6 - The With Statement

When you want to apply several methods or properties to the same object, you can find that you have to write the same bit of code many times.  While of course you can copy and paste to save time, there is a better solution as this part of the lesson explains!

Repeating code

This problem is especially common when applying formatting options to an object.

 

Files Needed

You can click here to download the file used for this page.

Completed Code

You can click here to download a file containing the sample code.

To begin, download and extract the file linked in the Files Needed section above.  Open the VBE and locate the subroutine called Not_Using_With in Module1:

Repeat code

In this subroutine we've had to begin each line by referring to the Range("A1").Font property.

 

Let's write an alternative version of this procedure in a more efficient way.  Start by creating a new subroutine below the existing one in the module:

New sub

Feel free to choose a different name for the procedure.

 

Now add an instruction which begins a With statement:

Begin With

You begin a With statement by referring to the object that you want to avoid repeatedly typing.

 

On the next line you can begin the instruction by typing a full stop.  The VBE will assume that the instruction begins with the object that you referenced in the first line of the With statement:

Enter full stop

Entering a full stop will display the IntelliSense list showing the methods and properties of the Range("A1").Font object.

 

You can complete the rest of the instruction as normal:

Complete instruction

Here we've set the Bold property of the cell's font to True.

 

You can write multiple lines, each beginning with a full stop:

More lines

Each line below the With statement assumes that you're referring to the Range("A1").Font object.

 

You can continue this process until you have applied all the methods and properties you need:

Multi properties

Here we've changed five properties of the object but only referred to the object once.

 

The final step is to add an End With statement:

End with

The End With statement signals the end of your ability to begin instructions with a full stop.

 

You can run the procedure to test that it works:

Result

The result of running the procedure.

 

To practise using a With statement:

  1. Using the same module, create a new subroutine:

Sub With_Great_Practice_Comes_Great_Efficiency()

 

End Sub

  1. Begin a With statement which refers to the Interior of Range("A2"):

Sub With_Great_Practice_Comes_Great_Efficiency()

 

With Range("A2").Interior

 

End Sub

  1. Add a set of instructions to alter the formatting of the cell.  Begin each instruction with a full stop:

Sub With_Great_Practice_Comes_Great_Efficiency()

 

With Range("A2").Interior

.Color = rgbBrown

.TintAndShade = 0.5

.Pattern = xlPatternVertical

.PatternColor = rgbOrange

.PatternTintAndShade = 0.5

 

End Sub

  1. Add an End With statement to finish the block of instructions:

Sub With_Great_Practice_Comes_Great_Efficiency()

 

With Range("A2").Interior

.Color = rgbBrown

.TintAndShade = 0.5

.Pattern = xlPatternVertical

.PatternColor = rgbOrange

.PatternTintAndShade = 0.5

End With

 

End Sub

  1. Run the subroutine to check the results:
End result

The specified cell should have a number of formatting options applied to it.

 
  1. Save and close the workbook.
This page has 0 threads Add post