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 the Option Explicit statement is good programming practice. It forces us to declare all variables and constants. You can automatically have VB add this to new modules as they are created by checking the Require Variable Declaration option, which can be found on the Editor tab of the Options dialog. (Select the Options option from the Tools menu to open the dialog.)

When the Option Explicit statement is used, VB generates a compile-time error if it encounters a variable that has not been declared.

3.4.2. A Whole Load of Nothing

An important element of any programming language is its ability to detect and to handle nothing. By nothing, I actually mean "no valid data." Because there are several different types of "nothingness," the VBA language has developed a number of ways to allow you to determine or to assign empty or null values to a variable. An understanding of the differences is important, since each has its own uses and, in the main, they aren't interchangeable.

vbNull

Used with the VarType function to determine if a variable contains Null. For example:

varValue = Null
If VarType(varValue) = vbNull Then

Note that you can't use the constant to assign a Null value. If you assign vbNull to a variable, you actually assign a value of 1 (or "1", if the variable is a string), and the IsNull test will fail.

vbNullChar

Assigns or tests for a null character (as distinct from a Null value); a null character has a value of Chr(0). In other words, vbNullChar is simply the equivalent of assigning Chr(0) to a variable and can test a variable to determine whether its value is a null character.

This constant is useful when passing strings to external libraries that expect a null-terminated string. For example:

sMyString & vbNullChar

vbNullString

Assigns or tests for a zero-length (empty) string. For example, the statement:

strVar1 = vbNullString

is equivalent to:

strVar1 = ""

Null keyword

Assigns a Null value to a variant variable. You can then test the variable for a null value by calling the IsNull function. Note that the code fragment:

varValue = Null
if varValue = Null

returns False, since a Null is False and therefore causes any expression containing Null to return False. The following code fragment shows how to use, and how not to use, the Null keyword:

Dim i As Variant

i = Null
    
If i = Null Then
   MsgBox "It's null" 'this fails
End If
    
If IsNull(i) Then
   MsgBox "It's null" 'this works
End If

Also note that the Null keyword can't be used to assign a Null value to a strongly typed variable; instead, it generates an "Invalid use of Null" error.

vbEmpty

Determines whether a variant has been initialized. For example:

If IsEmpty(varValue) Then

is identical to:

If varValue = vbEmpty then

However, you shouldn't use vbEmpty to assign an empty value to a variant. If you do, you actually assign (or "0" if the variable is a string), and an IsEmpty function call will fail.

Nothing keyword

Used only with object variables to determine either if a variable has a valid object reference, as in:

If objVar Is Nothing Then

or to destroy a current object reference, as in:

Set objvar = Nothing