Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
Declaring variables
If you don’t declare the data type for a variable that you use in a VBA routine, VBA uses the
default data type, Variant . Data stored as a Variant acts like a chameleon: It changes type,
depending on what you do with it.
The following procedure demonstrates how a variable can assume different data types:
Sub VariantDemo()
MyVar = “123”
MyVar = MyVar / 2
MyVar = ”Answer: ” & MyVar
MsgBox MyVar
End Sub
In the VariantDemo procedure, MyVar starts out as a three-character string. Then this string is
divided by two and becomes a numeric data type. Next, MyVar is appended to a string,
converting MyVar back to a string. The MsgBox statement displays the final string: Answer: 61.5 .
To further demonstrate the potential problems in dealing with Variant data types, try
executing this procedure:
Sub VariantDemo2()
MyVar = “123”
MyVar = MyVar + MyVar
MyVar = ”Answer: ” & MyVar
MsgBox MyVar
End Sub
The message box displays Answer: 123123 . This is probably not what you wanted. When
dealing with variants that contain text strings, the + operator performs string concatenation.
Determining a data type
You can use the VBA TypeName function to determine the data type of a variable. Here’s a modified
version of the previous procedure. This version displays the data type of MyVar at each step. You
see that it starts out as a string, is then converted to a double, and finally ends up as a string again.
Sub VariantDemo2()
MyVar = “123”
MsgBox TypeName(MyVar)
MyVar = MyVar / 2
MsgBox TypeName(MyVar)
MyVar = “Answer: “ & MyVar
MsgBox TypeName(MyVar)
MsgBox MyVar
End Sub
 
Search JabSto ::




Custom Search