MODULES
LESSONS
TOPICS
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.