Using Variables Without Declaring Them

Unless you specify otherwise, Visual Basic allows you to create variables simply by using them in your code (other programming languages, such as C and Pascal, require that you explicitly declare variables before you use them). The following example assigns the value of cell A1 to the variable tempVal.


tempVal = Worksheets(1).Range("A1").Value

This is called an implicit variable declaration. When you use this feature, Visual Basic automatically creates a variable and gives it the Variant data type. Variables with the Variant data type can contain any data, including strings, numbers, and dates. Until you assign a value to the new Variant variable, it has the special value Empty. As soon as you assign the Variant variable a value, it takes the data type of the assigned value. You'll learn more about Variant variables, data types, and the Empty value later in this chapter.

A Variant variable works well in this example because you may not know what kind of information cell A1 contains. Keep in mind that although it's convenient to allow Visual Basic to create variables whenever you use them, this can lead to subtle errors in your macro if you misspell a variable name, as shown in the following example.


Sub SwapCells()
    With Worksheets(1)
        tempVal = .Range("a1").Value
        .Range("a1").Value = .Range("a2").Value
        .Range("a2").Value = temVal
    End With
End Sub

At first glance, it looks as if this procedure should swap the contents of cells A1 and A2, but if you look carefully, you'll see that the tempVal variable is misspelled the second time it's used. Visual Basic doesn't know that you made a mistake and assumes that you meant to introduce a new variable, temVal. Visual Basic creates this new (empty) variable and assigns the value of cell A2 to it. After you run this procedure, the value that was in cell A2 has been moved into cell A1, and cell A2 is empty.