If you want to start building more complex macros, it is a good idea to learn about Visual Basic variables.

 

Variables can be thought of as place holders for numbers or text. When you declare a variable, you are telling the visual basic interpreter to set aside a space in memory for a number or a text string. The amount of space required for a variable is specified by its type. Some of the important variable types in VBA are:

 

·              String for strings of text data

·              Double for large numbers with or without decimal places

·              Integer for small to moderately sized numbers without decimal places (no fractional parts)

·              Long for larger numbers without decimal places

·              Date for holding date values

·              Variant will hold any data type

 

It is not absolutely necessary to formally declare variables in VBA, but it is good programming practice. If you do not declare a variable explicitly, a VARIANT type will be assigned to the variable when you first use it. Some programmers claim that VARIANT types can be slow when there are a large number of calculations involved.

 

Also, formally declaring a variable can help prevent programming errors. If a variable is not formally declared and it is misspelled somewhere in the macro code, it may be treated as a new variable. This can cause a malfunction in you macro that is extremely difficult to find and correct.

 

If you enter the words Options Explicit at the beginning of your macro, before the very first Sub statement, all variables will have to be formally declared for the macro to run. A misspelled variable will be easily detected because that particular spelling will not have been declared.

 

To declare a variable, you must use the Dim keyword in the following way.

 

Dim variable name As variable type

 

As an example, the following statements declare an Integer, a Double, and a String respectively.

 

Dim smallNumber As Integer

 

Dim bigNumber As Double

 

Dim someText As String

 

Once a variable is declared, you can assign a value to it.

 

The statement smallNumber = 27 would assign the value of 27 to the variable smallNumber.

 

The names smallNumber, bigNumber, and someText are all valid variable names. You can combine numbers and text to form variable names, but a variable name should not start with a number. Also, you cannot use a visual basic keyword (reserved word) as a variable name.

 

The following code declares an Integer variable named smallNumber. The program assigns the value from cell A1 to smallNumber, adds 2 to smallNumber, and then puts the new value held by smallNumber back into cell A1.