Module 4 - Variables, Parameters and Functions
Lesson 4.2 - Object Variables
Topic 4.2.1 - Basic Object Variables

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.

Files Needed

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

Completed Code

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

The Example Workbook

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

Click the button to play another exciting round of the game!

 

Why Use Object Variables?

Open the VBE and find the Roll_Again subroutine in Module1:

Roll again

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:

  • If anything changes in the structure of the worksheet which affects the positions of the referenced cells, we will have a lot of cell references to change in our code.
  • Using lots of different cell references in the code doesn't make it intuitive or easy to read.

We can solve these problems by storing references to the important cells in object variables.

Declaring an Object Variable

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:

Declare

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:

Set type

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:

Second variable

Give the second variable a sensible name and use the Range class to set its type.

 

Setting a Reference to an Object

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:

Set

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:

Set reference

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 player2

Set a reference to cell D2 as shown here.

 

Using an Object Variable

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:

Delete

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:

Use object variable

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:

Colour cell

Complete the instruction as shown here.

 

Use the same technique to change the colour of the cell referenced by the Player2ScoreCell variable:

Second cell

Add the instruction as shown here.

 

Replacing Other References

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:

Select code

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:

Replace

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:

Information

Using the Replace feature has saved us from making six changes manually.

Repeat the process to replace any reference to Range("D2") with Player2ScoreCell:

Replace D2

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:

Replaced

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 button

Click the button a few times to make sure that the game behaves as expected.

 

To practise using object variables:

  1. Using the same workbook, add two new object variables to the Roll_Again subroutine:

Sub Roll_Again()

 

Dim Player1ScoreCell As Range

Dim Player2ScoreCell As Range

 

'new variables

Dim Player1ResultCell As Range

Dim Player2ResultCell As Range

  1. Add code to set a reference to cell B3 in the Player1ResultCell variable:

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")

  1. Add code to set a reference to cell D3 in the Player2ResultCell variable:

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")

  1. In the remaining part of the subroutine, replace any reference to Range("B3") and Range("D3") with the relevant object variable name:

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

  1. Return to Excel and click the button on the worksheet several times to check that the game works.
  2. Save and close the workbook.
This page has 0 threads Add post