Chapter 14: VBA Programming for Pros

Whether you are creating Add-Ins, accessing Smart Tags, using Web services, calling API functions from DLLs, or wishing to control external programs via ActiveX automation, this chapter provides the requisite know-how. It is geared expressly to experienced VBA programmers.

14.1 Add-Ins

Excel comes equipped with a host of so-called add-in files. These files are located in the directory OfficDirectory\Office\Library and have the task of extending the functionality of Excel at a number of points. They can be activated and deactivated with the add-in manager. Since loading an add-in function requires a certain amount of memory, you should activate only those add-ins that you truly need. Add-in files can be recognized by the suffix *.xla .

In addition to the add-in files supplied with Excel, the command FILESAVE AS offers the possibility of saving your own Excel files in add-in format. (The add-in format is the last entry in the file type list.) The suggested location in which to save such a file is Userdirectory\Application Data\Microsoft\AddIns . This means that the add-in is available only for the current user . (If you wish to make an add-in generally available, you should save it in the Library directory mentioned above.)

Add-in files differ from normal *.xls files in the following ways:

• The (user-defined) functions contained therein are available to all other workbooks. It is not necessary (as it is otherwise ) for the name of the add-in file to be prefixed or to provide a reference. The function call is effected directly with Functionname , not in the form filename!functionname . Access to these functions is, of course, possible only when the add-in file has previously been loaded. (This is the concern of the add-in manager.)

• The worksheets of the add-in are invisible and cannot be made visible.

• The property IsAddin of the object ThisWorkbook contains the value True .

Caution  

In contrast to Excel 5 and 7 the program code of add-ins is unrestrictedly viewable in the development environment. There the code can be edited and saved. If you do not wish the user to see the code to your add-in, it must be password protected via TOOLSPROPERTIESPROTECTION .

Tip  

Add-ins can be easily converted again into normal files. Simply load the add-in file, open the properties window to "this workbook" in the development environment, and set IsAddin to False.With this the worksheets become visible, and the file can be saved in Excel with SAVE AS as a normal *.xls file.

Applications of Custom Add-Ins

• You can write an add-in with a collection of new tables or VBA functions that you use frequently. As soon as the add-in is loaded, these functions are available in other VBA programs and also for use directly in worksheets. The add-in is not visible, that is, neither in menu nor toolbar nor dialog. Such libraries of functions present the fewest difficulties from the programming point of view. The add-in consists exclusively of code in module sheets. Normally, a Workbook_Open procedure is not necessary (unless your functions require some global variables or fields that are initialized in Workbook_Open ).

• You can equip Excel with additional commands or wizards, thereby extending Excel's user interface. For this the add-in must extend the existing menu (usually with the worksheet menu TOOLS), so that the new commands can be invoked. The selection of these commands then leads to the display of a dialog by means of which the further use of the command is governed.

• The third variant consists in using the add-in as "packaging" for freestanding applications. This variant involves the greatest amount of programming effort. Normally, supplementary menu entries do not suffice, and therefore a custom menu or toolbar must be created and managed. In addition, as a rule, the opening and controlling of other Excel files is necessary. Further information on the problems that occur in realizing freestanding add-in programs is given below under "limitations."

Tip  

Among the example programs presented in this book there is one that is predestined to be transformed into an add-in. The euro conversion tool (Chapter 5). Load the *.xls file, save it as a *.xla file as an add-in, and you are done.

Loading Add-In Files With and Without the Add-In Manager

In principle, there are two ways of starting an add-in file: Either open the file like a normal Excel file with FileOpen or invoke the add-in manager (via ToolsAdd-Ins), there click on the Browse button, and select the file. With add-ins corresponding to the first two variants, installation via the add-in manager is the better choice. You can then activate and deactivate the add-in file at will, as with other add-ins. For add-ins belonging to the third variant (custom applications) it is more practical to open the add-in file only when needed, via FileOpen.

Tip  

Normally, only the file name of the selected file is displayed in the add-in manager, not (as with built-in add-ins) a more meaningful name for the add-in together with a brief commentary about its significance.You can alter this state of affairs as follows : Before changing your workbook into an add-in file, execute FilePropertiesSummary and there provide a title and short commentary.

The form for the add-in manager has the drawback that it provides no opportunity to delete an entry from the add-in list. The only way to do this is to delete the relevant *.xla file or rename it and then attempt to activate the add-in. At this moment Excel realizes that the add-in no longer exists and asks whether it may be permitted to delete the entry from the add-in list.

Tip  

An add-in file that has been loaded via FileOpen cannot then be deleted from Excel's memory while it is running. Add-in files are "invisible," and therefore they cannot be activated as an Excel window and then closed. To get around this deficiency you should use the add-in manager to load the add-in.

Using Functions Defined in Add-Ins

User-defined functions that are defined in the code of an add-in can be used directly in worksheets. The functions are displayed in the form InsertFunction in the category "User Defined." The only condition is that the add-in file has been opened (whether via FileOpen or the add-in manager). Functions or procedures that are used only within the add-in but should remain otherwise unavailable must be identified in program code with the keyword Private .

If you wish to use a function (or procedure) in the VBA code of a new workbook, then you must establish a reference to the add-in file (with the command TOOLSREFERENCES). All available functions are then displayed in the object browser. See Chapter 4 on the subject of the object browser and references.

In the example below ( AddInFn.xls and .xla ) the generally accessible function AITest is defined, which multiplies its parameter by 6. The function AITest uses, in turn , the function InternalFunction , which is unavailable to the user of the add-in file.

 ' AddInFn.xls, Module1 Function  AITest  (x As Double) As Double   AITest = 2 * InternalFunction(x) End Function Private Function  InternalFunction  (x As Double) As Double   InternalFunction = x * 3 End Function 

If you create a large function library and wish to distribute it, then you should provide each function with a brief informational text that will be displayed in the function assistant and the object browser. Such texts can be inserted into the object browser with the pop-up menu command PROPERTIES. This command opens a dialog box in which you can also set a reference to a help file.

Event Procedures in Add-Ins

In addition to the procedures Workbook_Open and Workbook_BeforeClose , which are also available in normal workbooks, for add-ins there are two additional procedures that are automatically executed: Workbook_AddinInstall , when an add-in is taken into the add-in list of the add-in manager, and Workbook_Addin- Uninstall , when the add-in is removed from this list. The two procedures are not executed every time the add-in is loaded, but only once upon installation as a permanent addition to Excel and again upon deinstallation. You can use these procedures to carry out one-time preparatory tasks (for example, to copy template files into the template directory) or corresponding cleanup tasks .

Limitations in the Programming of Custom Add-In Applications

Add-in files are completely "invisible." By this is meant that the worksheet or chart sheet of an add-in file cannot be displayed. Add-in files can be made perceptible only through a menu extension, independent menu, toolbar, or dialog.

The data in the worksheets of an add-in file are internally available and can, for example, be transferred via VBA code into a newly opened workbook and there edited. However, it is impossible to translate unchanged an application such as DB_Share.xls (formula and database application) for the car sharing club; see Chapter 11) into an add-in file. The application depends on its worksheets being visible on the computer monitor, where they can be changed by the user, and this is impossible with an add-in file.

There are two ways of solving this problem. The first variant consist in separating the application into two files. One file contains code, menus , and forms and is transmitted as an add-in file. The second file contains the tables in which the user is to work and is opened by the first file via Workbook_Open . In Workbook_Open you can set up event procedures for the newly opened table file (say for changing sheets). This modus operandi has two drawbacks: First, the application depends on the presence of both files, and second, the application is subject to intentional or unintentional changes in the table file.

The second solution consists in leaving all the worksheets in the add-in file. With Workbook_Open you generate a new workbook and copy the relevant worksheets into it. This solution requires somewhat greater effort in programming but in compensation is less subject to breakdown.

In either case there is a restriction to be reckoned with: Although you can change the (invisible) tables in the add-in while it is operational, it is impossible to save these changes. This is inconvenient in applications in which a running serial number (receipt number, data record number) must be maintained and be set to the last valid value when the program is launched. A serial number or other data that you will require at the next launch of the add-in must be saved in a different file.

In sum, add-ins are well suited for the distribution of a set of functions or to extend Excel's user interface (such as the euro conversion tool). However, add-ins seldom represent a usable solution for freestanding applications, such as can be found in Chapters 9 through 13. In this case add-ins offer no recognizable advantages over normal Excel files.

COM Add-Ins

COM add-ins are add-ins that are compiled to DLLs. (DLLs are binary library files.) COM add-ins were introduced with Office Developer 2000 and can be created only with Office Developer. They exhibit a host of differences in comparison to traditional add-ins and in particular, cannot contain worksheets, diagrams, and toolbars . The only significant advantage of COM add-ins is that the code that they contain is well protected, since it is compiled. Against this advantage is arrayed greater complexity in distribution and installation, with the result that COM add-ins have not caught on in practice.