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.2 - Object Variable Scope

Just as with basic data variables, you can extend the scope of object variables to the level of a module or to an entire project. This part of the lesson reminds you how!

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

Extract and open the workbook linked to in the Files Needed section above. You'll find a version of the random numbers game we used in the previous part of the lesson. Open the VBE and find the Roll_Again subroutine:

The first part of the subroutine is shown here.

We'd like to split parts of this subroutine into separate procedures. This means we need to make sure that all of the procedures have access to the variables they need.

Creating a Separate Procedure

We'll start by creating a new subroutine in the same module which will reset the value and formatting of the score cells:

Create a new subroutine in the same module.

Cut four lines of code from the Roll_Again subroutine as shown in the diagram below:

Cut the four instructions shown highlighted here.

Paste the cut code into the new subroutine you have created:

Paste the cut lines into the Reset_Scores subroutine.

In the Roll_Again subroutine, replace the cut lines of code with a call to the Reset_Scores procedure:

Add a call to the Reset_Scores procedure in the position shown here.

Test your code by running the Roll_Again subroutine to confirm that it no longer works:

The Reset_Scores procedure does not have access to the two variables so you see this error message.

Click OK and stop the code.

Module-Level Object Variables

To make sure that both subroutines can access the variables they need, we'll extend the scope of the variables to the level of the module. Start by cutting the two variable declarations shown below:

Cut the Player1ScoreCell and Player2ScoreCell variables from the Roll_Again subroutine.

Paste the cut variables at the top of the module, below Option Explicit:

Paste the variable declarations between Option Explicit and the start of the Roll_Again subroutine.

Optionally, you can declare the two module-level variables using the Private keyword rather than Dim:

Using Private rather than Dim for module-level variables makes it easy to see at-a-glance that their scope is different to variables declared within a procedure.

Run the Roll_Again procedure to check that your code now works.

You can click the button on the worksheet to check that the code works.

Creating Public Variables

If you have subroutines in different modules which need to access the same variables, you need to extend their scope to the level of the project. You can do this by declaring the variables as Public. Insert a new module into the project and change its name to ScoresAndResults:

Insert a module in the Project Explorer window and rename it in the Properties window.

Create a new subroutine called Create_New_Scores in the new module you have created:

Create a new subroutine as shown here.

Return to Module1 and cut the two lines of code shown in the diagram below:

Cut the two lines which generate the scores.

Return to the ScoresAndResults module and paste the two lines you have just cut into the Create_New_Scores subroutine:

Paste the cut code into the new subroutine.

Return to Module1 and attempt to run the Roll_Again subroutine:

You'll see an error message indicating that the Create_New_Scores subroutine doesn't have access to the variables it needs. Click OK and stop the code.

Return to Module1 and change the declaration of the two variables at the top of the module from Private to Public:

Change the two variables shown here to Public.

Declaring variables as Public makes them accessible to every module in the project. Try playing the game again to ensure that everything now works.

In a project with many public variables it's often worth creating a separate module to hold them. We don't need to do that for this example but feel free to do so if you'd like the practice!

Module-Level Variables

To practise changing the scope of object variables to the level of a module:

In the Roll_Again procedure in Module1, find and cut the two lines shown in the diagram below:

Cut the two instructions shown highlighted here.

Paste the cut code at the bottom of the Reset_Scores procedure in Module1:

Paste the code in the position shown here.

Cut the two variables declared within the Roll_Again procedure:

Cut the Player1ResultCell and Player2ResultCell variables from the Roll_Again procedure.

Paste the variables at the top of Module1, below the existing public variables:

Paste the two variables at the position shown here.

Change the Dim keyword to Private for the two variables you have just pasted:

Declare the variables as Private to indicate that they can be referenced by any procedure in this module.

Run the Roll_Again subroutine to check that your code works.

Project-Level Variables

To practise extending the scope of variables to the level of the project:

In the Roll_Again procedure in Module1, find and cut the code shown selected below:

Cut everything from If to End If, as shown highlighted here.

In the ScoresAndResults module, create a new subroutine called Calculate_Result:

Create a new subroutine as shown here.

Paste the code that you cut from Module1 into the new subroutine:

Paste the entire If statement into the Calculate_Result subroutine.

Return to Module1 and replace the code you have cut with a call to the Calculate_Result subroutine:

Replace the If statement with a call to the subroutine which now contains it.

Change the declaration of the two Private variables at the top of Module1 to Public:

Make sure that the Player1ResultCell and Player2ResultCell variables are Public.

Click the button on the worksheet to test that the code works:

Click the button a few times to make sure that everything works as intended.

Save and close the workbook.

This page has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

MODULES

LESSONS

TOPICS