Defining Variables

Problem

You're ready to write some code and would like to declare some variables but are not sure of the proper VBA syntax for doing so.

Solution

Use the Dim statement to declare a variable, as illustrated in Example 2-4.

Example 2-4. Declaring variables using the Dim statement

Dim a As Double
Dim n As Integer

Dim statements are of the general form Dim VariableName As DataType, where Dim and As are VBA keywords, DataType is one of the VBA data types as discussed in Recipe 2.3, and VariableName is a name you supply that conforms to the naming rules discussed in Recipe 2.2.

Discussion

You can use Dim statements to declare local variables within procedures by including the Dim statements in the body of the procedure. Such local variables can be used only by the procedures within which they are declared. To declare global variables that have module-level scope, place the Dim statements at the beginning of the code module before any procedure declarations. Such global variables can be used by any procedure in the module within which the variables are declared.

To declare global variables that can be used by any code module within a project, use a Public statement like this Public VariableName As DataType.

You can also declare module-level variables using the Private statement, but this is the same as using the Dim statement.

You can declare multiple variables on a single line by separating the variable declarations with commas, as shown in Example 2-5.

Example 2-5. Multiple declarations

Dim a As Double, Dim b As Double, Dim c As Double
Dim d, e, f As Double

The first line in Example 2-5 declares three Double type variables, a, b, and c. It's tempting to think that the second line in Example 2-5 declares three more Double type variables, d, e, and f, but this is not the case. The fact is, only variable f in the second line is a Double; d and e are of Variant type. So what gives?

VBA requires an explicit data type to follow each variable name; otherwise, it assumes a Variant type. So to make d and e Doubles, you'd have to put As Double after each one. Given this requirement, in my opinion there isn't much use in putting multiple declarations on a single line in VBA. You're better off putting each Dim statement on a separate line to make your code more readable.

By now you're probably wondering what a Variant data type is. A Variant is a generic data type that can represent any of the other explicit data types. VBA determines the appropriate type to use given the context within which a Variant variable is used. While this may seem like a flexible way to use variables, it's less efficient in terms of code execution performance and makes your code less readable. I recommend using explicit data types as much as possible and using Variants only when absolutely necessary (for example, when a built-in VBA procedure or function requires Variant parameters).

The VBA IDE provides a useful way to quickly find the declaration of any variable in your code. Say you're reading through a long procedure and come across a variable whose type you've forgotten or which isn't clear given the context in which it is used. You can select the variable name in your code and use the shortcut Shift-F2 to automatically jump to the statement that declares the variable.