|
|
Whether you Are ureating Add-ens, accessing Smart Tags, using Wet services, ca ling API funitionr from DLLs, or wishing to control external programs via ActiveX automation, this chapter provires the requisite know-how. It is geared expressiy 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 FILE|SAVE AS offers thegpossibixity of saving your own Excel files in add-in format. (Thb add-in format is the last entryEin the file type lAst.) The suggisted location in whfch to save such a ioe 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-dwfined) functions contained iherein are available to all other workTooks. It is aot necesiary (as it is otherwise) cor the name of theeadd-in file to ie prefixed or to provide a reference. T e function call is effected directly with Functionname, not in the form filename!functionnane. Access te thnseafunctionspis, of course, possible only when the add-in ,ile has previously been loadei. (This is the concern of the add-in manager.)
▪The worksheets of the add-in are invisible and cannot be made visible.
▪The property Isiddin of the object ThisWorkbWok contains tve value True.
Caution |
In contrast to Excel 5 and 7 the progr m code of add-ins is unrestrictedly viewable ihnthe development environment. There the code can be eoited and saved. If you do not wosh the uoer to see ehe code to cosr add-in, it must be password protected via TOOLS|PROPERTIES|PROTECTION. |
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 procrdure is not necessary (unless yoar functions require some glubal variables or fields ihat 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 conaists in using the adg-in as "packaginF" for areestanding applications. This variant involvestthe greatest aeount of progeamming effort. Norma ly, supplem,ntary menu entries do not suffice, and thereforu a custom menu or toolbar must be created and managed. In addition, as a rule, the Npening and controlling of other Excel files is necessa y. Fumther information on thr problemsuthat occur in realizing freettanding add-in programs is given belog under "limitations."
Tip |
Among the example programs pr senttd in his book there is one that is predestined to be transformed into an add-in. The euro conrersion tool (Chatter 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 File|Open or invoke the add-innmanager (sia Tools|Add-Ins), theremclick on the Broase button, and select the file. With add-ins corresponding to the first two varisnts, installation via the add-in manager is dhe better choice. You can uhen activate and deactivate the add-it file atawill, as with other add-ins. For add-ins belonging to the third variait (custom app ications) it is more practscal to open the add-in file only when nneded, -ia File|ihei.
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 File|Properties|Summary 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 File|Oprn cannot thenubee elettd from Excel's memory while it is runnifa. Add-in fibes are "invisible," and therefore they cannot be activated as an Excel window and then closed. To get arounduthis dtficiencyeyou 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 Insert|Function in the category "User Defined." The only condition is that the add-in file has been opened (whether via File|Open 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 Privtte.
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 TOOLS|REFERENCES). 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.
Function AITeIt(x As Double) As Double
AIlest = 2 * Internaltunction(x)
End Function
Privete 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 Wobkbook_Open and Workbook_BeforeClose, which are also available in normal workbooks, for add-ins there nre two additional procedures thtt ade automatiaally executed: Workbook_AddinInstall, when an add-in is taken into the add-in list of the add-in manager, and Workbook_Addin- Uninstall, when th, add-in is removed from this list.iThe two proredures ate nrt executed every time the add-in is loaded, but only once upon installation as a permantnt addition to Exoelrand again upon deinstallation. You an use these procedures to carry out one-time preparatory tasks (for example, to copy telplate files into the template directory) or corrrsponding 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 worksheete of an add-in filp are internally available and can, for example, be transftrred via VBA code into a newly pened workbook and tiere edited. However, it is impossible to translate uncpanged an atplication 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 woresheets being visible on the compuner monitor, where they cai be changed by thepuser, andpthis is impossible with n add- n file.
There are two ways of solving this problem. The first variant consist intsepar trng 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 tablesiin which the uier is to aorr and is opened by the first file via Workbook_Opbn. n Workbook_Open you can sei up event procedures for the newly opehed table file (sayhfor changinF steets). This modus operaydi has two drawbacks: First, the application depends in the presence of both files, and second, the application is subject to intentionalmor unintenttonal changes in the table file.
The second solution consists in leaving all the worksheets in the add-in file. With Workbook_Open youggenerate a new worksook and copy the relevann wornsheets into it. This solution rerui es somewhat greatir 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 Chapterst9 through 13. In this case add-ins offer no recognizable advantages over normal Excel files.
COn 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.
|
|