Page 79 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 54

54 Friday Evening

After this code, the variable ws exists and is of type Worksheet, but it does not reference
anything — it contains the value Nothing. To initialize the variable it must be assigned a
specific reference, for example:

Set ws = Worksheets.Add
Now, the variable ws refers to the new worksheet that was created by the Add method.

The Set keyword must be used whenever you are assigning an object
reference.
Note
In most cases, you get your object references from collections, either as an existing
collection member or by using the Add method. In some situations, however, there is no
relevant collection, and you must create the object using the New keyword. The syntax is:
Set ObjectVariable = New ObjectName

You can also combine the declaration and initialization in one statement:
Dim ObjectVariable As New ObjectName

Boolean Variables
A Boolean variable can hold a True/False value. Boolean variables (and properties) are used
frequently in VBA programming to hold data that can be on/off, yes/no, and so on. When
you declare a Boolean variable, it is automatically initialized to False. VBA provides the
keywords True and False to use for Boolean values:

Dim UpToDate As Boolean
UpToDate = True

The Variant Type

Variant is VBA’s most flexible data type as well as the default type. This means that if you
declare a variable without specifying a type, it is created as type Variant:

Dim x ‘ X is type variant
You can also explicitly specify the type:
Dim x As Variant

The Variant data type can hold almost any type of data, the exceptions being fixed-
length strings and user-defined types. Thus, a given Variant variable can hold an integer,
a floating-point value, a string, or an object reference. A Variant can even hold an entire
array. The downside is that Variant data requires more memory to store, and more com-
puter power to process, than other data types. This means that you should not use Variant
variables simply as a convenience to avoid having to decide which of the more specific data