Variable Declaration

To declare a variable means to define its data type. Variables are declared with the Dim keyword (or with the keywords Private and Public, which we will discuss later in this chapter). Here are some examples:

Dim Name As String Dim Holiday As Date Dim Age As Integer Dim Height As Single Dim Money As Currency Dim wbk As Workbook Dim ch As Chart

The general syntax of a variable declaration is:

Dim VariableName As DataType

If a particular variable is used without first declaring it, or if it is declared without mentioning a data type, as in:

Dim Age then VBA will treat the variable as having type Variant. As we can see from Table 5-1, this is generally a waste of memory, since variants require more memory than most other types of variables.

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 variable name. In particular, VBA allows the type-declaration suffixes shown in Table 5-2. (I personally dislike these suffixes, but they do save space.)

Table 5-2. Type-Declaration Suffixes















For instance, the following line declares a variable called Name$ of type String:

Dim Name$ We can then write:

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.

0 0

Post a comment