The Sample Workbook

Extract and open the file 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 two parts of this lesson:

This version of the subroutine contains four variables, each with its own Dim statement.

What Happens When You Use Declared Variables

Declaring a variable provides your program with information about how much memory should be allocated to hold a value and what type of data it should expect.  You can see this information in the Locals Window when you step through a procedure:

Press F8 to begin stepping through a procedure. The Locals Window shows the list of variables along with their current values and data types (choose View | Locals Window from the menu if you can't see this window).

Using Non-Declared Variables

In VBA, you don't need to declare variables in order to use them.  Let's temporarily remove the variable declarations from the procedure.  Start by selecting the four Dim statements:

Select at least part of each line containing a Dim statement.

You can now choose to comment out the selected lines of code:

Click this tool to comment out the selected lines. If you can't see the toolbar, choose View | Toolbars | Debug from the menu.

Your code should now look like this:

The four variable declarations should be shown in green, indicating that they are comments and won't be treated as real code.

You can test that the procedure still works by returning to Excel and clicking the button on the worksheet:

Click the first button to prove that the procedure still works.

What Happens When You Use Non-Declared Variables

Your program behaves a little differently when you use non-declared variables.  Press F8 to begin stepping through the procedure and look in the Locals Window:

When you begin the procedure, the Locals Window still displays the list of four variables. As the program doesn't know what data type to expect, it uses the Variant data type.

By default, VBA assumes that any words in a procedure that aren't recognised VBA keywords must be variables.  It then automatically allocates memory to store a value using the Variant data type.

Although we've commented out the variable declarations, our code still refers to their names. These names are obviously not recognised VBA keywords so they are implicitly treated as variables.

This can cause some issues in the programs that you write:

• The Variant data type can store any type of value which means you have no control over the type of information you assign to a variable.  You might inadvertently assign some text to a variable that you intended to hold only numbers.
• VBA needs to identify the sub-type of a Variant variable when a value is assigned to it.  This adds a small overhead to performance.
• The sub-type of a Variant variable can change during the course of a subroutine, leading to some difficult-to-identify bugs.
• The amount of memory allocated to a Variant variable may be more than is needed to store the value assigned to it.

Some of these problems are more important than others, but it's clear that it's best practice to explicitly declare every variable that you intend to use.  Let's uncomment the four Dim statements we commented out earlier:

Select at least part of each of the four lines.

You can then uncomment the lines by clicking the tool shown below:

Click this tool to uncomment the selected lines of code.

Misspelling Variable Names

Ensuring that you declare each variable is good practice, but it doesn't avoid one major problem with undeclared variables.  You can easily create new undeclared variables accidentally by misspelling variable names in your code.  Try deliberately misspelling the WeightKg variable in the line of code shown below:

In the last line of the code shown here, we've changed the spelling of WeightKg to WieghtKg.

Return to Excel and click the button to run the subroutine:

The subroutine will run, but it won't produce the expected result.  Every character has a BMI of 0.

The problem becomes clear when you return to the VBE and press F8 to begin stepping through the procedure:

Although we've explicitly declared only four variables, the Locals Window shows that there are five. The WieghtKg variable was created automatically when we misspelt the WeightKg variable.

The WieghtKg variable is never assigned a value in the subroutine, but we reference this empty variable to calculate the BMI which results in the wrong answer for every character.

Forcing Explicit Variable Declaration

The solution to this problem is to tell VBA that we don't want to allow non-declared variables in the module.  To do this, type Option Explicit at the top of the module, above any subroutines:

Type these two words at the top of the module. The horizontal line will appear automatically after you move the cursor to another line of code.

Option Explicit means that you must explicitly declare every variable that you want to use.  Try running the subroutine again using the misspelt WieghtKg variable:

This time, instead of running the procedure and creating the wrong result, the code stops immediately and displays an error message. Even more usefully, the misspelt variable name is highlighted in blue in the code. Click OK to proceed.

You can correct the spelling of the highlighted variable and then run the procedure again:

Edit the code to correct your spelling and then run the subroutine to check that it now works properly.

Enabling Option Explicit for Future Modules

Forcing explicit variable declaration is clearly a good thing!  You can alter a setting in the VBE to ensure that each new module will automatically have Option Explicit added to the top.  To do this, choose Tools | Options... from the menu:

On the Editor tab of the dialog box, check the box next to Require Variable Declaration and click OK.

You can try inserting a new module to check that Option Explicit appears automatically:

The setting won't affect any existing modules but new modules will have Option Explicit added automatically.

We'll be using explicitly-declared variables in many of the procedures we'll write in later parts of this course, so you'll get plenty of practice!