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
546 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 ...
In the previous lesson you learnt how to use variables to capture values such as strings of text and numbers. This lesson explains how you can use variables to hold references to VBA objects.
You can click here to download the file for this page.
You can click here to download a file containing the sample code.
Extract and open the workbook linked to in the Files Needed section above. You'll find a version of the random numbers game we created in Module 3 Conditions and Loops:
Click the button to play another exciting round of the game!
Open the VBE and find the Roll_Again subroutine in Module1:
The subroutine contains lots of references to cells B2, D2, B3 and D3.
Referring to cells in the manner shown above works well enough, but it does have some shortcomings:
We can solve these problems by storing references to the important cells in object variables.
You begin declaring an object variable in the same way as a basic data type variable. We'll start by declaring a variable to hold a reference to cell B2, i.e. the cell containing the score for player 1:
At the top of the Roll_Again subroutine, write the word Dim, followed by a sensible name for the variable. We've used the name Player1ScoreCell.
Next, we can set set the type of object that the variable will be capable of holding a reference to:
Write the word As, followed by the class of object that you want to store a reference to. We want our variable to hold a reference to a cell in a worksheet so we'll use the Range class to do this.
An object variable can hold a reference to any class of VBA object: a single cell, a group of cells, a worksheet, a workbook, a chart, etc. This part of the lesson uses only the Range class, but later parts of the course will use a variety of other types of object.
Use the same technique to declare a second variable which can hold a reference to the cell which contains the score for player 2:
Give the second variable a sensible name and use the Range class to set its type.
When you're using a basic data type variable you can assign a single value to it such as a piece of text, a number or a date. When you're using an object variable, you assign a reference to an object using the Set keyword. We'll assign a reference to cell B2 to the Player1ScoreCell variable:
Start the instruction with the word Set, followed by the name of the variable and an = operator.
You can then say which object you want to set a reference to:
We want to set a reference to cell B2.
Use the same technique to set a reference to cell D2 in the Player2ScoreCell variable:
Set a reference to cell D2 as shown here.
Once you've set a reference to an object in a variable, you can use the variable's name to refer to that object. We'll replace the two lines of code shown in the diagram below by using our object variables:
Select and delete the two lines shown highlighted here.
Write the name of the Player1ScoreCell variable (or use the IntelliSense list to insert it), followed by a full stop:
After typing a full stop, you'll see a list of properties and methods that can be applied to the class of object thta is referenced by the variable.
Continue writing the instruction to change the colour of the cell referenced by the variable:
Complete the instruction as shown here.
Use the same technique to change the colour of the cell referenced by the Player2ScoreCell variable:
Add the instruction as shown here.
The Roll_Again subroutine contains many other references to cells B2 and D2. We could replace these one-by-one, but it's easier to use the VBE's Replace feature. Start by selecting the part of the subroutine which contains the code you want to replace:
We want to replace some bits of code in the section shown highlighted here.
Next, press Ctrl + H or choose Edit | Replace... from the menu:
In the dialog box enter Range("B2") in the Find What: text box. Enter Player1ScoreCell in the Replace With: text box. Ensure that you have chosen Selected Text in the bottom left corner of the dialog box and then click Replace All.
When you have clicked the Replace All button you'll see a dialog box explaining what has happened:
Using the Replace feature has saved us from making six changes manually.
Repeat the process to replace any reference to Range("D2") with Player2ScoreCell:
Again, make sure that you have chosen Selected Text in the bottom left corner of the dialog box.
Close the Replace dialog box and check that your code looks like the example shown below:
Your replaced code should now look like this.
Return to Excel and click the button on the worksheet to check that the game still works:
Click the button a few times to make sure that the game behaves as expected.
To practise using object variables:
Sub Roll_Again()
Dim Player1ScoreCell As Range
Dim Player2ScoreCell As Range
'new variables
Dim Player1ResultCell As Range
Dim Player2ResultCell As Range
Sub Roll_Again()
Dim Player1ScoreCell As Range
Dim Player2ScoreCell As Range
'new variables
Dim Player1ResultCell As Range
Dim Player2ResultCell As Range
Set Player1ScoreCell = Range("B2")
Set Player2ScoreCell = Range("D2")
'set references in new variables
Set Player1ResultCell = Range("B3")
Sub Roll_Again()
Dim Player1ScoreCell As Range
Dim Player2ScoreCell As Range
'new variables
Dim Player1ResultCell As Range
Dim Player2ResultCell As Range
Set Player1ScoreCell = Range("B2")
Set Player2ScoreCell = Range("D2")
'set references in new variables
Set Player1ResultCell = Range("B3")
Set Player2ResultCell = Range("D3")
Sub Roll_Again()
Dim Player1ScoreCell As Range
Dim Player2ScoreCell As Range
'new variables
Dim Player1ResultCell As Range
Dim Player2ResultCell As Range
Set Player1ScoreCell = Range("B2")
Set Player2ScoreCell = Range("D2")
'set references in new variables
Set Player1ResultCell = Range("B3")
Set Player2ResultCell = Range("D3")
Player1ScoreCell.Interior.Color = rgbGray
Player2ScoreCell.Interior.Color = rgbGray
Player1ScoreCell.Value = WorksheetFunction.RandBetween(1, 10)
Player2ScoreCell.Value = WorksheetFunction.RandBetween(1, 10)
If Player1ScoreCell.Value > Player2ScoreCell.Value Then
Player1ScoreCell.Interior.Color = rgbGreen
Player2ScoreCell.Interior.Color = rgbRed
'code replaced in the next two lines
Player1ResultCell.Value = "WINNER!"
Player2ResultCell.Value = "LOSER!"
ElseIf Player2ScoreCell.Value > Player1ScoreCell.Value Then
Player1ScoreCell.Interior.Color = rgbRed
Player2ScoreCell.Interior.Color = rgbGreen
'code replaced in the next two lines
Player1ResultCell.Value = "LOSER!"
Player2ResultCell.Value = "WINNER!"
Else
Player1ScoreCell.Interior.Color = rgbOrange
'code replaced in the next line
Player1ResultCell.Value = "DRAW!"
Player2ScoreCell.Interior.Color = rgbOrange
'code replaced in the next line
Player2ResultCell.Value = "DRAW!"
End If
End Sub
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.