Declaring variables

Understanding automation : How is automation coding structured? : Declaring variables

Declaring variables

In VBA, the construction for declaring variables is as follows:

Dim foobar As Integer

The built-in data types are Byte, Boolean, Integer, Long, Single, Double, String, Variant, and several other less-used types including Date, Decimal, and Object.

Variables can be declared anywhere within the body of a function, or at the top of the current module. However, it is generally a good idea to declare a variable before it is used; otherwise, the compiler interprets it as a Variant, and inefficiencies can be incurred at run time.

•

Booleans take False to be 0 and True to be any other value, although converting from a Boolean to a Long results in True being converted to a value of –1.

•

To get more information about one of the built-in data types, type it in the Code window of the Macro Editor, select it, and then press F1.

Data structures can be built by using the following VBA syntax:

Public Type fooType

item1 As Integer

item2 As String

End Type

Dim myTypedItem As fooType

The items within a variable declared as type fooType are accessed by using dot notation:

myTypedItem.item1 = 5

Declaring strings

Using strings is much simpler in VBA than in C. In VBA, strings can be added together, truncated, searched forwards and backwards, and passed as simple arguments to functions.

To add two strings together in VBA, simply use the concatenation operator ( & ) or the addition operator ( + ):

Dim string1 As String, string2 As String

string2 = string1 & " more text" + " even more text"

In VBA, there are many functions for manipulating strings, including InStr(), Left(), Mid(), Right(), Len(), and Trim().

Declaring enumerations

To declare an enumeration in VBA, use the following construction:

Public Enum fooEnum

ItemOne

ItemTwo

ItemThree

End Enum

•

By default, the first item in an enumerated type is assigned a value of 0.

Declaring arrays

To declare an array in VBA, use parentheses — that is, the ( and ) symbols:

Dim barArray (4) As Integer

The value defines the index of the last item in the array. Because array indexes are zero-based by default, there are five elements in the preceding sample array (that is, elements 0 thru 4, inclusive).

Arrays can be resized by using ReDim. For example, the following VBA code adds an extra element to barArray but preserves the existing contents of the original five elements:

ReDim Preserve barArray (6)

Upper and lower bounds for an array can be determined at run time by using the functions UBound() and LBound().

Multidimensional arrays can be declared by separating the dimension indexes with commas, as in the following VBA example:

Dim barArray (4, 3)