Declaring and scoping Variables

If you read the preceding sections, you now know a bit about variables and data types. In this section, you discover how to declare a variable as a certain data type.

If you don't declare the data type for a variable 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. For example, if a variable is a variant data type and contains a text string that looks like a number (such as "143"), you can use this variable for string manipulations as well as numeric calculations. VBA automatically handles the conversion, which may seem like an easy way out — but remember that you sacrifice speed and memory.

Before you use variables in a procedure, it's an excellent practice to declare your variables — that is, tell VBA each variable's data type. Declaring your variables makes your program 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 a variable's data type, it doesn't have to investigate and can reserve just enough memory to store the data.

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

0 0

Post a comment