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 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 to play another exciting round of the game!

Why Use Object Variables?

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:

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:

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.

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:

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.

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:

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.

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:

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:

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

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

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

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

Return to Excel and click the button on the worksheet several times to check that the game works.
Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Why Use Object Variables?

Declaring an Object Variable

Setting a Reference to an Object

Using an Object Variable

Replacing Other References

Head office

London

Manchester