Variable Declaration

Variable Declaration

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

Dim Name As StringDim Holiday As DateDim Age As IntegerDim Height As SingleDim Money As CurrencyDim 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 Ageand Height are declared as Variants, not Integers. In other words, we must specify the type for each variable explicitly.