Page 78 - Excel Progamming Weekend Crash Course

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

Session 4 — Syntax and Data in VBA 53

Date Variables

In VBA, the term date is used to refer to both dates and times. Dates are represented numeri-
cally as floating-point numbers. The integer part of the number represents the date as the
number of days since December 30, 1899 (with negative numbers for prior dates), and the
fractional part of the number represents the time of day as a fraction of the 24-hour day.
Fortunately, you never need to deal with these numeric date representations directly because
VBA has a powerful set of tools for working with dates in more conventional formats. These
are covered in Session 8.
The Date data type is designed specifically to hold date information. To set a date value,
you can use any of the commonly accepted formats placed between # (pound) symbols, as
shown here:
Dim day1 As Date, day2 As Date, day3 As Date
day1 = #March 7, 2003#
day2 = #1/1/2003#
day3 = #July 4, 2003#

Object Variables

The Object data type can hold a reference to any object. It is rarely used, however, because
it is preferred to declare variables as the specific type they will hold. Thus:

Dim GenericObject As Object
Dim SpecificObject As Worksheet
The variable GenericObject can hold a reference to any kind of object, while the vari-
able SpecificObject can reference only a Worksheet object. It might seem that use of the
Object data type is better because it provides more flexibility, but this is not the case.
Here’s why.
Use of the Object data type is called late binding. The program does not know what
object type the variable will refer to until the program runs and a reference is actually
assigned to the variable. This slows things down a bit, although not significantly. More
important, it makes the editor’s Auto List feature unavailable.
When you declare a variable as a specific object type, it is called early binding. VBA “knows”
what type the variable is and, while you are editing, can use Auto List to display the object’s
properties and methods. This can be a real convenience.

To enable the Auto List feature, select Tools ➪ Options, select the Editor tab,
and put a check next to the Auto List Members option. With Auto List, when
Tip you type the name of an object variable followed by a period, the Editor dis-
plays a list of that type’s properties and methods, from which you can select.
Declaring a variable as a specific object type is not the same as initializing it. Here’s an
example:
Dim ws As Worksheet