Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
542 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file used for this page.
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:
Sub With_Great_Practice_Comes_Great_Efficiency()
End Sub
Sub With_Great_Practice_Comes_Great_Efficiency()
With Range("A2").Interior
End Sub
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
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
The specified cell should have a number of formatting options applied to it.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.