Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

This section begins by comparing an XLAM add-in file with its XLSM source file. Later in this chapter, I discuss methods that you can use to optimize the performance of your add-in. I describe a technique that might reduce its file size , which makes it load more quickly and use less disk space and memory.

XLSM and XLAM file size and structure

An add-in based on an XLSM source file is exactly the same size as the original. The VBA code in XLAM files is not optimized in any way, so faster performance is not among the benefits of using an add-in.

XLAM file VBA collection membership

An add-in is a member of the AddIns collection but is not an official member of the Workbooks collection. You can refer to an add-in by using the Workbooks method of the Application object and supplying the add-in's filename as its index. The following instruction creates an object variable that represents an add-in named myaddin.xlam :

Dim TestAddin As Workbook Set TestAddin = Workbooks("myaddin.xlam")

Add-ins cannot be referenced by an index number in the Workbooks collection. If you use the following code to loop through the Workbooks collection, the myaddin.xlam workbook is not displayed:

Dim w as Workbook For Each w in Application.Workbooks MsgBox w.Name Next w

The following For-Next loop, on the other hand, displays myaddin.xlam - assuming that Excel " knows " about it - in the Add-Ins dialog box:

Dim a as Addin For Each a in Application.AddIns MsgBox a.Name Next a

Visibility of XLSM and XLAM files

Ordinary workbooks are displayed in one or more windows . For example, the following statement displays the number of windows for the active workbook:

MsgBox ActiveWorkbook.Windows.Count

You can manipulate the visibility of each window for a workbook by choosing the View Window Hide command or by changing the Visible property using VBA. The following code hides all windows for the active workbook:

Dim Win As Window For Each Win In ActiveWorkbook.Windows Win.Visible = False Next Win

Add-in files are never visible, and they don't officially have windows, even though they have unseen worksheets. Consequently, add-ins don't appear in the windows list when you choose the View Window Switch Windows command. If myaddin.xlam is open , the following statement returns :

MsgBox Workbooks("myaddin.xlam").Windows.Count

Worksheets and chart sheets in XLSM and XLAM files

Add-in files, like normal workbook files, can have any number of worksheets or chart sheets. But, as I note earlier in this chapter, an XLSM file must have at least one worksheet in order for it to be converted to an add-in.

When an add-in is open, your VBA code can access its sheets as if it were an ordinary workbook. Because add-in files aren't part of the Workbooks collection, however, you must always reference an add-in by its name and not by an index number. The following example displays the value in cell A1 of the first worksheet in myaddin.xla , which is assumed to be open:

MsgBox Workbooks("myaddin.xlam").Worksheets(1).Range("A1").Value

If your add-in contains a worksheet that you would like the user to see, you can either copy it to an open workbook or create a new workbook from the sheet.

The following code, for example, copies the first worksheet from an add-in and places it in the active workbook (as the last sheet):

Sub CopySheetFromAddin() Dim AddinSheet As Worksheet Dim NumSheets As Long Set AddinSheet = Workbooks("myaddin.xlam").Sheets(1) NumSheets = ActiveWorkbook.Sheets.Count AddinSheet.Copy After:=ActiveWorkbook.Sheets(NumSheets) End Sub

Creating a new workbook from a sheet within an add-in is even simpler:

Sub CreateNewWorkbook() Workbooks("myaddin.xlam").Sheets(1).Copy End Sub

Accessing VBA procedures in an add-in

Accessing the VBA procedures in an add-in is a bit different from accessing procedures in a normal XLSM workbook. First of all, when you choose the View Macros Macros command, the Macro dialog box does not display the names of macros that are in open add-ins. It's almost as if Excel is trying to prevent you from accessing them.

Because procedures contained in an add-in aren't listed in the Macro dialog box, you must provide other means to access them. Your choices include direct methods (such as shortcut keys, Ribbon commands, and shortcut menu items) as well as indirect methods (such as event handlers). One such candidate, for example, may be the OnTime method, which executes a procedure at a specific time of day.

You can use the Run method of the Application object to execute a procedure in an add-in. For example,

Application.Run "myaddin.xlam!DisplayNames"

Another option is to use the Tools References command in the VBE to enable a reference to the add-in. Then you can refer directly to one of its procedures in your VBA code without the filename qualifier. In fact, you don't need to use the Run method; you can call the procedure directly as long as it's not declared as Private . The following statement executes a procedure named DisplayNames in an add-in that has been added as a reference:

Call DisplayNames

The Macro dialog box does not display the names of procedures contained in add-ins. But what if you'd like to run such a procedure, but the add-in is protected so that you can't view the code to determine the name of the procedure? Use the Object Browser!

To illustrate , install the Lookup Wizard add-in. This add-in is distributed with Excel and is protected, so you can't view the code.

1. Activate the VBE and then select the Lookup.xla project in the Project window.

2. Press F2 to activate the Object Browser.

3. In the Libraries drop-down list, select Lookup. This displays all the classes in the Lookup.xla add-in, as depicted in the following figure.

4. Select various items in the Classes list to see what class they are and the members that they contain.

In the example above, the Lookup_Common class is a module, and its members consist of a number of variables , constants, procedures, and functions. One of these procedures, DoLookupCommand , sounds like it might be the main procedure that starts the wizard. To test this theory, activate Excel and then choose View Macros Macros. Type DoLookupCommand in the Macro Name box and then click Run. Sure enough! You'll see the first dialog box of the Lookup Wizard.

Armed with this information, you can write VBA code to start the Lookup Wizard - assuming, of course, that you can think of a reason to do so.

Function procedures defined in an add-in work just like those defined in an XLSM workbook. They're easy to access because Excel displays their names in the Insert Function dialog box under the User Defined category (by default). The only exception is if the Function procedure was declared with the Private keyword; then the function does not appear there. That's why it's a good idea to declare custom functions as Private if they will be used only by other VBA procedures and are not designed to be used in worksheet formulas.

As I discuss earlier, you can use worksheet functions contained in add-ins without the workbook name qualifier. For example, if you have a custom function named MOVAVG stored in the file newfuncs.xlsm , you would use the following instruction to address the function from a worksheet that's in a different workbook:

=newfuncs.xlsm!MOVAVG(A1:A50)

But if this function is stored in an add-in file that's open, you can omit the file reference and write the following instead:

=MOVAVG(A1:A50)