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.3 - Parameters

Topic 4.3.3 - Object Parameters

So far in this lesson you've learnt to use parameters to pass basic values such as text and numbers between procedures. This part of the lesson explains how you can use parameters to pass references to objects between procedures.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

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

The Example Workbook

Download and extract the workbook linked to in the Files Needed section above. You'll find a version of the random numbers game that we've used in previous lessons:

Click the button to play the game.

Open the VBE and find the Roll_Again subroutine:

We'd like to extract parts of this procedure into separate subroutines.

To break this long procedure into smaller parts, each separate subroutine must have access to the cells referenced by the Player1Cell and Player2Cell variables.

Defining Object Parameters

We'll begin by creating a new procedure to reset the game. Create a new subroutine called Reset_Game in the same module:

Create a basic subroutine as shown here.

This subroutine needs to reference the cells which contain the score for each player. Define two parameters called P1Cell and P2Cell, each of which should use the Range class:

Define two parameters in the parentheses after the subroutine's name.

Return to the Roll_Again subroutine and cut the section of code shown highlighted in the diagram below:

Cut the six lines of code shown highlighted here.

Paste the cut code into the Reset_Game subroutine:

The Reset_Game subroutine should now look like this.

In the code you have pasted, change the variable names Player1Cell and Player2Cell to match the names of the P1Cell and P2Cell parameters respectively:

Change the variable names to reference the appropriate parameters.

Passing References to Object Parameters

Return to the Roll_Again subroutine and add a call to the Reset_Game subroutine in the position you cut the code earlier:

Enter the subroutine's name or select it from the IntelliSense list.

Type a space after the name of the subroutine to see its list of parameters:

The tooltip shows that the Reset_Game procedure expects references to two Range objects to be passed to it.

You can pass the Range references to the Reset_Game subroutine using the variables declared in the Roll_Again procedure:

Refer to the Player1Cell and Player2Cell variables as shown here.

If the Roll_Again procedure didn't use variables, you could simply refer directly to the cells you want to use:

Here the Player1Cell and Player2Cell variables aren't needed as we've referred to the range objects directly in the call to the Reset_Game subroutine.

As we need to refer to the Player1Cell and Player2Cell variables in other parts of the Roll_Again subroutine we may as well pass these variables to the Reset_Game subroutine as well.

Creating Another Subroutine

We can use the same technique to create a separate subroutine for generating new scores. Create a new subroutine called Generate_Scores in the same module. Define two Range parameters in the same way as for the Reset_Game subroutine:

Define the new subroutine as shown here.

Return to the Roll_Again subroutine and cut the code shown highlighted in the diagram below:

Cut the two lines which calculate random numbers.

Paste the cut code into the Generate_Scores subroutine:

Paste the code into the location shown here.

Edit the two lines you have just pasted to change the Player1Cell and Player2Cell variables to refer to the P1Cell and P2Cell parameters respectively:

Alter the references to the variables so that they refer to the parameter names instead.

Return to the Roll_Again procedure and add a call to the Generate_Scores subroutine in place of the code you cut earlier:

Pass references to the Player1Cell and Player2Cell variables into the parameters of the Generate_Scores subroutine.

Return to Excel and click the button on the worksheet to make sure that your code still works:

Click the button to test the code.

To practise using object parameters:

Using the same workbook, create a new subroutine called Check_Result in Module1:

Sub Check_Result()

End Sub

Define two Range parameters called P1Cell and P2Cell in this subroutine:

Sub Check_Result(P1Cell As Range, P2Cell As Range)

End Sub

Return to the Roll_Again subroutine and cut the entire If statement which checks the game result:

Cut everything from If to End If from the Roll_Again subroutine.

Paste the cut code into the Check_Result subroutine:

Sub Check_Result(P1Cell As Range, P2Cell As Range)

If Player1Cell.Value > Player2Cell.Value Then

Player1Cell.Interior.Color = rgbGreen

Player2Cell.Interior.Color = rgbRed

Player1Cell.Offset(1, 0).Value = "WINNER!"

Player2Cell.Offset(1, 0).Value = "LOSER!"

ElseIf Player2Cell.Value > Player1Cell.Value Then

Player1Cell.Interior.Color = rgbRed

Player2Cell.Interior.Color = rgbGreen

Player1Cell.Offset(1, 0).Value = "LOSER!"

Player2Cell.Offset(1, 0).Value = "WINNER!"

Else

Player1Cell.Interior.Color = rgbOrange

Player2Cell.Interior.Color = rgbOrange

Player1Cell.Offset(1, 0).Value = "DRAW!"

Player2Cell.Offset(1, 0).Value = "DRAW!"

End If

End Sub

Edit the code you have pasted to change references to the Player1Cell and Player2Cell variables to the P1Cell and P2Cell parameters respectively. You may find it quicker to use the Replace feature - choose Edit | Replace... from the menu to do this:

Sub Check_Result(P1Cell As Range, P2Cell As Range)

If P1Cell.Value > P2Cell.Value Then

P1Cell.Interior.Color = rgbGreen

P2Cell.Interior.Color = rgbRed

P1Cell.Offset(1, 0).Value = "WINNER!"

P2Cell.Offset(1, 0).Value = "LOSER!"

ElseIf P2Cell.Value > P1Cell.Value Then

P1Cell.Interior.Color = rgbRed

P2Cell.Interior.Color = rgbGreen

P1Cell.Offset(1, 0).Value = "LOSER!"

P2Cell.Offset(1, 0).Value = "WINNER!"

Else

P1Cell.Interior.Color = rgbOrange

P2Cell.Interior.Color = rgbOrange

P1Cell.Offset(1, 0).Value = "DRAW!"

P2Cell.Offset(1, 0).Value = "DRAW!"

End If

End Sub

Return to the Roll_Again subroutine and call the Check_Results subroutine in place of the code you cut:

Call the Check_Result subroutine at the end of the Roll_Again procedure. Pass references to the Player1Cell and Player2Cell variables to the parameters of the Check_Result subroutine.

Return to Excel and click the button on the worksheet to test that the code still 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

Defining Object Parameters

Passing References to Object Parameters

Creating Another Subroutine

Head office

London

Manchester