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.5 - Public Variables

In the previous part of the lesson you saw how to extend the scope of a variable from a single subroutine to an entire project. This part of the lesson explains how to make variables available to every module in a project.

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 file linked to in the Files Needed section above. In the VBE you'll find a copy of the code we created in the previous part of this lesson:

There are four module-level variables declared as Private at the top of the module. The ProcessBMIList subroutine calls the CalculateBMI and CalculateBMIBand subroutines.

Organising Code into Different Modules

In large and complex projects you'll find it helpful to organise related procedures into different modules. To demonstrate this, we'll create a module to hold the subroutines which calculate the BMI and the BMI band. Start by inserting a new module into the project:

Right-click in Project Explorer window and choose Insert | Module to create a new module.

Check that the new module contains the Option Explicit statement:

If you've followed the previous parts of this lesson you should find that Option Explicit appears automatically. If not, you can simply type it in.

Change the name of the new module so that it describes the code it will contain:

Change the module name in the Properties window. Here we've called it BMICalculations.

Double-click on Module1 to return to it, then cut the CalculateBMI subroutine to the clipboard:

Select the code then right-click and choose Cut.

Double-click on the BMICalculations module and paste the cut code into it:

Paste the subroutine into the new module, below Option Explicit.

Return to the Excel window and click the first button on the worksheet to run the ProcessBMIList subroutine:

Click the Calculate BMI button to test your code.

You'll immediately receive an error message:

The error message indicates that the CalculateBMI subroutine doesn't have access to the WeightKg variable.

Click OK on the error message shown above and stop the procedure running.

Creating Public Variables

Now that the CalculateBMI subroutine is in the BMICalculations module, it doesn't have access to the Private variables declared in Module1:

The CalculateBMI subroutine needs access to the first three variables shown here. The scope of these variables is currently limited to Module1.

To make the required variables available to another module we can change their declarations from Private to Public:

Changing the variables to Public extends their scope to every module in the project.

You can use the word Global instead of Public to declare public variables but this is an old-fashioned technique. You should use Public to declare public variables in any new code that you write.

Return to Excel and click the Calculate BMI button on the worksheet to test that your code works:

Click the button to run the code.

Using a Separate Module for Public Variables

In a large and complex project, you may find it useful to arrange all of your public variables into a single module. Insert a new module into the project and assign a sensible name to it:

Here we've called the module PublicVariables.

Now cut the three public variables from Module1:

Cut these three variables from Module1.

Paste the cut code into the PublicVariables module:

The PublicVariables module should contain only this code at this point.

Try running the ProcessBMIList subroutine again to check that the code still works.

Watching Public Variables

One disadvantage to placing public variables in a separate module is that they don't appear in the Locals Window when you're debugging your code:

The Locals Window displays only those variables that are declared in the module in which the active code is running.

You can use the Watch Window to keep track of the contents of public variables:

Right-click the name of a public variable and choose Add Watch...

On the dialog box that appears, choose to watch the value of the selected variable in all modules:

Choose (All Modules) from the drop down list shown here, then click OK.

Begin stepping through the ProcessBMIList procedure in Module1 by clicking within the subroutine and pressing F8repeatedly:

You'll see the value of the watched variable in the Watch Window regardless of which module is active as you step through your code. If you can't see this window, choose View | Watch Window from the menu.

To practise using public variables:

Using the same workbook, open Module1 and cut the CalculateBMIBand subroutine to the clipboard:

Cut this subroutine from Module1.

Open the BMICalculations module and paste the subroutine into it:

Paste the cut code at the bottom of the module.

Return to Module1 and attempt to run the ProcessBMIList subroutine:

You should see this error message appear. Click OK and stop running the procedure.

In Module1, change the declaration of the BMIBand variable from Private to Public:

Make the BMIBand variable Public.

Run the ProcessBMIList subroutine to check that the code works.
Cut the declaration of the BMIBand variable from Module1 and paste it into the PublicVariables module:

Paste the variable declaration at the bottom of the list in the PublicVariables module.

Run the ProcessBMIList subroutine again to check that everything works.
Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS