VB & VBA in a Nutshell: The Language

3.4. Declaring Variables and Constants

As was mentioned earlier, VBA supports a default data type, which means that, unlike many other programming languages, VBA allows the implicit declaration of variables. As soon as you use a variable or constant name within your code, VBA does all the necessary work of allocating memory space, etc., and the variable is considered to be declared.

However, it's good programming practice (and one that will save you endless hours of grief) to explicitly declare any variables and constants you want to use by using the Dim, Private, or Public statements. Their syntax is:

Dim VariableName As datatype
Private VariableName As datatype
Public VariableName As datatype

If you have a number of variables to declare, you can do this on a single line by separating them with commas, as in the following Dim statement:

Dim iRefNo As Integer, iAnyVar As Integer

By explicitly declaring variables in this manner, you can reduce the number of bugs in your code caused by spelling errors, perhaps the most common of programming errors. Once declared, a variable name is available to you in the IntelliSense statement completion drop-down list, which means that you should never have a misspelled variable again!

For full details of how to use the Dim, Private, and Public statements, see their entries in Chapter 7. There is further discussion later in this chapter about how the declaration of variables affects their scope and lifetime.

3.4.1. Option Explicit

Using ...