Page 305 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

5 Create a variable that you can use to refer to the declared Application object in the class
module. This should be a module-level object variable, declared in a regular VBA
module or in the ThisWorkbook object. For example:
Dim ApplicationClass As New AppEventClass
6 Connect the declared object with the Application object. This is often done in a
Workbook_Open procedure. For example:
Private Sub Workbook_Open()
Set ApplicationClass.Appl = Application
End Sub
After you save the workbook, close it, and reopen it to trigger the Workbook_Open event
procedure, the events attached to the Application object will be activated.
Understanding how the events are triggered, as well as the sequence in which they are executed,
is crucial when designing your Excel applications. Use the preceding example to play with
your application and test the firing sequence. Taking time and effort in the planning stages of
your event sequences will save a lot of time and frustration in the development of your cur-
rent and future projects.

Detecting When a Workbook Is Opened
Because Excel only allows one copy of a workbook to be open, it’s important to determine if
the workbook is currently open or needs to be opened. If you do not verify the status of the
file, you will receive an error and your event will stop.

For example, The Garden Company uses a file named Invoice.xls that’s dependent on the
ProductList.xls file. The Invoice.xls file uses a lookup to input the correct product name on
the invoice. It’s crucial for the ProductList.xls file to be open for the Invoice.xls file to operate
properly. To avoid lookup errors, the following function, named WorkbookOpen, was created
to test whether the ProductList.xls file is currently open. The function will return the answer
True if the workbook is open.

Function WorkbookOpen(WorkBookName As String) As Boolean
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Once the function has been added to the ThisWorkbook object within the Invoice.xls file, you
can use the function to evaluate whether the desired workbook is open. The following
If...Then...Else statement, which you could use as a subroutine within a larger procedure, Chapter 12
ensures the ProductList.xls workbook will be open when you need to use it in a procedure.

279
Part 4: Advanced VBA