Page 68 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 43

Session 3 — The Excel Object Model 43

Be sure to set the DisplayAlerts property back to True after the Delete
method is called, as in the previous example.
Tip

Referencing Worksheets
You have already seen a couple of ways to reference a specific worksheet in a workbook,
using the reference returned when the worksheet is created or obtaining the reference
from the Worksheets collection. Another useful way to obtain a reference is with the
ActiveSheet property of the Workbook object. This property returns a reference to the
active sheet (worksheet or chart sheet) that is on top, or active, in the specified workbook.
If there is no active sheet, the property returns the special value Nothing (see the sidebar,
“The Usefulness of Nothing.”).

For more on If statements, see Session 6.

Cross-Ref

The Usefulness of Nothing

There are situations where an object variable does not refer to any object: It
refers to nothing, and VBA has the special keyword Nothing to represent this.
An object variable contains Nothing when it has been declared but not yet ini-
tialized (has not been assigned an object reference). Thus:

Dim MyWB As Workbook
‘ At this point MyWB contains Nothing
Set MyWB = Workbooks.Add
‘ Now that it has been initialized, MyWB no longer contains Nothing
The value Nothing is also returned by some object properties under certain
conditions, such as the ActiveSheet property when there is no active sheet.
You can test for an object reference containing Nothing using the Is Nothing
clause in an If...Then statement:

If SomeObjectReference Is Nothing Then
...

End If
Finally, you can (and should) explicitly set an object reference to Nothing
when you are finished using the object:
Set SomeObjectReference = Nothing
When the last reference to an object is destroyed in this way, the memory
occupied by the object is freed up for other purposes.