Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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!

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:

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:

Feel free to choose a different name for the procedure.

Now add an instruction which begins a With statement:

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:

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:

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:

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:

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:

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:

The result of running the procedure.

To practise using a With statement:

Using the same module, create a new subroutine:

Sub With_Great_Practice_Comes_Great_Efficiency()

End Sub

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

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

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

Run the subroutine to check the results:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Head office

London

Manchester