- Declaring Variables
- Avoiding Variable Errors
- Variable Data Types
- Using Array Variables
- Working with Constants
- The Absolute Minimum
This chapter is from the book
This chapter is from the book
This chapter is from the book
Avoiding Variable Errors
One of the most common errors in VBA procedures is to declare a variable and then later misspell the name. For example, suppose I had entered the following statement in the GrossMargin procedure from Listing 3.1:
totlExpenses = Application.Sum(Range("Expenses"))
Here, totlExpenses is a misspelling of the variable named totalExpenses. VBA supports implicit declarations, which means that if it sees a name it doesn't recognize, it assumes that the name belongs to a new variable. In this case, VBA would assume that totlExpenses is a new variable, proceed normally, and calculate the wrong answer for the function.
To avoid this problem, you can tell VBA to generate an error whenever it comes across a name that hasn't been declared explicitly with a Dim statement. There are two ways to do this:
For an individual module, enter the following statement at the top of the module:
Option Explicit
To force VBA to add this statement automatically to all your modules, in the Visual Basic Editor, select Tools, Options, display the Editor tab in the Options dialog box that appears, and activate the Require Variable Declaration check box.
Activating the Require Variable Declaration check box forces VBA to add the Option Explicit statement at the beginning of each new module. However, it doesn't add this statement to any existing modules; you need to do that by hand.