Starting Up

There is very little difference in Excel 2007 between a normal workbook and an Add-in. The code and UserForms can be modified in the same manner, and they both offer the same level of protection (locking the Project from view). The two advantages of using an Add-in to hold your tools are that it is invisible within the Excel User Interface, and that it can be loaded using Excel's Add-ins dialog (Office Menu O Excel Options O Add-Ins O Manage: Excel Add-Ins O Go). This chapter uses the term Add-in to mean a container for tools that you're adding to Excel or the VBE. In fact, during the development of the Add-in, you will actually keep the file as a standard workbook, only converting it to an Add-in at the end.

Most Add-ins have a common structure, and the one you develop in this chapter will be no exception:

□ A startup module to trap the opening and closing of the Add-in

□ Some code to add the custom menu items to the command bars on opening and remove them on closing

□ For the VBE, a class module to handle the menu items' Click events

□ Some code to perform the menus' actions

Start with a new workbook and delete all of the worksheets, apart from the first. Press Alt+F11 to switch to the VBE, and find your workbook in the Project Explorer. Select the VBProject entry for it. In the Properties window, change the project's name to aaVBETools2 007. The name starts with the prefix aa, so it always appears at the top of the Project Explorer, nicely out of the way of any other projects you may be developing.

Double-click the ThisWorkbook VBComponent to bring up its code pane and type in the following code:

Option Explicit

Dim moMenuHandler As CMenuHandler

' Subroutine: Workbook_Open

' Purpose: Create a new instance of the ' The class's Initialize event

menu-handling class, sets up the menus.

Private Sub Workbook_Open()

Set moMenuHandler = Nothing Set moMenuHandler = New CMenuHandler End Sub

This code is run when the workbook is opened, and it just creates a new instance of a class module (which you'll create next) and stores a reference to it in a module-level variable. Using this technique, the Class_lnitialize event is run when the workbook is opened. The class is kept alive while the workbook is open and is only destroyed (with Class_Terminate called) when the workbook is actually closed — crucially, after the user has been given the opportunity to cancel the close (whereas the Workbook_BeforeClose event is called before the user's opportunity to cancel the close).

0 0

Post a comment