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:

Basic game

Click the button to play the game.

 

Open the VBE and find the Roll_Again subroutine:

Roll again procedure

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:

New procedure

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 parameters

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 code

Cut the six lines of code shown highlighted here.

 

Paste the cut code into the Reset_Game subroutine:

Paste code

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 variable names

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:

Call subroutine

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:

Tooltip

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 variables

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:

Reference range

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:

New 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 code

Cut the two lines which calculate random numbers.

Paste the cut code into the Generate_Scores subroutine:

Paste

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 code

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:

Call subroutine

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:

Test code

Click the button to test the code.

 

To practise using object parameters:

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

Sub Check_Result()

 

End Sub

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

Sub Check_Result(P1Cell As Range, P2Cell As Range)

 

End Sub

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

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

 
  1. 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

  1. 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

  1. Return to the Roll_Again subroutine and call the Check_Results subroutine in place of the code you cut:
Call sub

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.

 
  1. Return to Excel and click the button on the worksheet to test that the code still works.
  2. Save and close the workbook.
This page has 0 threads Add post