Microsoft Office Tutorials and References
In Depth Information
Table 5-2. Type-Declaration Suffixes
For instance, an integer variable requires 2 bytes, whereas a variant that holds the same integer
requires 16 bytes, which is a waste of 14 bytes. It is not uncommon to have hundreds or even
thousands of variables in a complex program, and so the memory waste could be significant. For
this reason, it is a good idea to declare all variables.
Perhaps more importantly, much more overhead is involved in maintaining a Variant than its
corresponding String or Integer, for example. This in turn means that using Variants typically
results in worse performance than using an equivalent set of explicit data types.
We can place more than one declaration on a line to save space. For instance, the following line
declares three variables:
Dim Age As Integer, Name As String, Money As Currency
Note, however, that a declaration such as:
Dim Age, Height, Weight As Integer
is legal, but Age and Height are declared as Variants, not Integers. In other words, we must
specify the type for each variable explicitly.
It is also possible to tell VBA the type of the variable by appending a special character to the
personally dislike these suffixes, but they do save space.)
Table 5-2. T ype-Declaration Suffixes
For instance, the following line declares a variable called Name$ of type String:
We can then write:
Name$ = "Donna"
Finally, let us note that although Excel allows variable and constant declarations to be placed
anywhere within a procedure (before the item is used, that is), it is generally good programming
practice to place all such declarations at the beginning of the procedure. This improves code
readability and makes housekeeping much simpler.
5.4.2 The Importance of Explicit Variable Declaration
We have said that using the Variant data type generally wastes memory and often results in poorer
performance. There is an additional, even more important reason to declare all variables explicitly.
This has to do with making typing errors, which we all do from time to time. In particular, if we
accidentally misspell a variable name, VBA will think we mean to create a new variable!