Page 272 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Auto Routines

In versions of Excel prior to Excel 97, special macros such as Auto_Add and Auto_Remove
were called when an add-in was installed or removed. These macros are stored in a module
associated with the workbook rather than in the ThisWorkbook object. Although these
macros continue to work even in this version of Excel, you should use the corresponding
Workbook events.
Other auto routines you might encounter when working with older Excel applications are
Auto_Open and Auto_Close, which correspond to Workbook_Open and Workbook_Close,
and Auto_Activate and Auto_Deactivate, which correspond to Workbook_Activate and
Workbook_Deactivate.

You can see the values stored in the AddIn object by writing a macro similar to the one shown
in the following program listing. This macro uses a For Each statement to loop through each
AddIn object in the AddIns collection and then insert the value into the current worksheet.

Sub GetAddIns()
DimaAsAddIn
DimiAsLong

i=1
For Each a In Application.AddIns
i=i+1
Cells(i,1)=i-1
Cells(i, 2) = a.Application.Name
Cells(i, 3) = a.CLSID
Cells(i, 4) = a.Creator
Cells(i, 5) = a.FullName
Cells(i, 6) = a.Installed
Cells(i, 7) = a.Name
Cells(i, 8) = a.Parent.Name
Cells(i, 9) = a.Path
Cells(i, 10) = a.progID
Next a

End Sub
Chapter 11
Because the Application and Parent properties return object references that can’t be displayed
in a cell, the Name property associated with each of those objects is displayed. Figure 11-5
shows the worksheet after running the GetAddIns macro.

246
Part 4: Advanced VBA