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'll 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.

It's an excellent habit to declare each variable in a procedure before you use it. 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 Explict 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.

0 0

Post a comment