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.1 - Basic Data Variables

Topic 4.1.4 - Module-Level Variables

So far in this lesson, we've declared and used variables within a single subroutine. This part of the lesson explains how to make variables available to every procedure in a single module.

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 completed code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above. In the VBE you'll find a copy of the subroutine we've used in the previous parts of this lesson:

The subroutine has four variables declared within it. Note the inclusion of the Option Explicit statement at the top of the module, meaning that we must explicitly declare each variable that we want to use.

Separating a Long Procedure into Smaller Parts

In earlier parts of this course we've created separate subroutines and called them from a main routine to break a long procedure into smaller parts. Here we'll use the same approach to create a separate subroutine to calculate the BMI for each character.

Calculating the BMI takes only the single line of code shown highlighted here.

Start by creating a new subroutine called CalculateBMI and copy and paste the line of code shown above into it:

The complete subroutine is shown here.

Now replace the original line of code in the ProcessBMIList subroutine with a call to the new subroutine:

The relevant part of the ProcessBMIList subroutine should now look like this.

Running the Procedure

Now try running the ProcessBMIList subroutine. You should immediately encounter an error message:

The error message appears as shown here. The name of the WeightKg variable will also be highlighted in the CalculateBMI subroutine.

The error message shown above indicates that the WeightKg variable has not been declared. Of course, we have declared the variable, just not in a way that allows the CalculateBMI subroutine to access it. When you declare a variable within a subroutine, it is only accessible within that subroutine. The variable's scope is said to be local to the procedure:

All four variables are declared within a single subroutine. Their scope is local to the ProcessBMIList subroutine.

Extending the Scope of a Variable

One way to solve this problem is to extend the scope of the required variables so that they can be accessed by every subroutine in the module. Both the ProcessBMIList and CalculateBMI subroutines need to be able to access the WeightKg, HeightM and BMI variables. Start by cutting the Dim statements for these variables from the ProcessBMIList subroutine:

Cut the three variable declarations shown highlighted here.

Paste the cut lines of code at the top of the module below the Option Explicit statement but before the start of any subroutine:

Paste the cut lines of code and tidy up the formatting so that the top of the module looks like this.

We've now extended the scope of the three variables to the level of the module. Try running the ProcessBMIList subroutine again to show that everything works:

Click the top button on the worksheet to check that the code now works.

Debugging with Module-Level Variables

Module-level variables behave a little differently in the Locals Window. Try stepping through the ProcessBMIList procedure using the F8 key:

By default, the Locals Window only shows variables whose scope is local to the procedure you are currently stepping through.

You can click the + symbol shown next to the module name in the Locals Window to show variables scoped to the module:

After clicking the + symbol next to the module name, you'll see a list of module-level variables.

You may also notice that module-level variables retain the last values they contained, even when the code has stopped running. Variables with a scope that is local to a procedure will lose their values when the procedure ends.

Using the Private Keyword

Rather than using the Dim keyword to declare a module-level variable, you can use the Private keyword instead. This is optional but is a good way to show, at a glance, that a variable has a wider scope than a local variable in a subroutine:

The Private keyword indicates that a variable is only available to procedures within the same module.

To practise using module-level variables:

Using the same workbook, create a new subroutine called CalculateBMIBand:

Sub CalculateBMIBand()

End Sub

Copy the code which calculates the BMI band from the ProcessBMIList subroutine and paste it into the CalculateBMIBand subroutine:

Sub CalculateBMIBand()

BMIBand = Switch( _

BMI < 18.5, "Underweight", _

BMI < 25, "Healthy weight", _

BMI < 30, "Overweight", _

BMI >= 30, "Obese")

End Sub

In the ProcessBMIList subroutine, replace the code you have just copied with a call to the CalculateBMIBand subroutine:

Sub ProcessBMIList()

Dim BMIBand As String

Range("A3").Select

Do Until ActiveCell.Value = ""

WeightKg = ActiveCell.Offset(0, 1).Value

HeightM = ActiveCell.Offset(0, 2).Value

CalculateBMI

'Call the new subroutine here

CalculateBMIBand

ActiveCell.Offset(0, 3).Value = BMI

ActiveCell.Offset(0, 4).Value = BMIBand

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Run the ProcessBMIList subroutine and confirm that you see an error message:

Click OK to clear the error message.

Move the Dim statement for the BMIBand variable from within the ProcessBMIList procedure to the top of the module, below the Option Explicit statement:

Option Explicit

Private WeightKg As Double

Private HeightM As Double

Private BMI As Double

Dim BMIBand As String

Sub ProcessBMIList()

Optionally, change the Dim keyword to the Private keyword:

Option Explicit

Private WeightKg As Double

Private HeightM As Double

Private BMI As Double

Private BMIBand As String

Sub ProcessBMIList()

Run the ProcessBMIList subroutine to check that everything works:

Click the first button on the worksheet to ensure that everything is working.

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

Files Needed

Completed Code

The Example Workbook

Separating a Long Procedure into Smaller Parts

Running the Procedure

Extending the Scope of a Variable

Debugging with Module-Level Variables

Using the Private Keyword

Head office

London

Manchester