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:

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.


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

Thanks to VBA, the data type conversion of undeclared variables is automatic. This process might seem like an easy way out, but remember that you sacrifice speed and memory - and you run the risk of errors that you may not even know about.

Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits:

■ Your programs run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows the data type, it doesn't have to investigate, and it can reserve just enough memory to store the data.

■ You avoid problems involving misspelled variable names. This assumes that you use Option Explicit to force yourself to declare all variables (see the next section). Say that you use an undeclared variable named CurrentRate. At some point in your routine, however, you insert the statement CurentRate = .075. This misspelled variable name, which is very difficult to spot, will likely cause your routine to give incorrect results.


To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

Option Explicit

When this statement is present, VBA will not even execute a procedure if it contains an undeclared variable name. VBA issues the error message shown in Figure 8-1, and you must declare the variable before you can proceed.

>4 fh* (di ][Ih Inmi Jtnut fWuf K ■ [doJi J

hkflu tjJp

tip it

Hfl'ki 4 OAH 1 n r4 4t< ;! 7. <

_ v -r - A


m Upm

L^j- LiSD XxplSC-LT

3>JD HliJUpi/WflHCf it BB< 12.M f ' 41 .A ¡¡»¡■I:* * + v Erd Jui

m Upm


L^j- LiSD XxplSC-LT

3>JD HliJUpi/WflHCf it BB< 12.M f ' 41 .A ¡¡»¡■I:* * + v Erd Jui

Ccrtipfc cLr

Figure 8-1: VBA's way of telling you that your procedure contains an undeclared variable.

Tip To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, enable the Require Variable Declaration option in the Editor tab of the VBE

Options dialog box (choose Tools c Options). I highly recommend doing so. Be aware, however, that this option does not affect existing modules.

0 0

Post a comment