Add-Ins

Monitor page
for changes

Creating and Using Excel Add-Ins

XLS

XLA

You must leave at least one worksheet visible. Excel will not allow you to hide or delete all worksheets. Generally that worksheet will be visible when you open the workbook.

None of the worksheets in the .xla will be visible when you open the .xla using File Open.

When you "dirty" a workbook by entering some data, Excel will prompt you to save that workbook. If you opened the workbook and did not "dirty" it, no prompts are displayed when you close the workbook without saving it.

When you "dirty" the .xla workbook via programming (you can’t see the worksheet anyway, so programming is the only way to "dirty" the workbook), Excel will not prompt you to save before you close the workbook.

VBA Sub procedure names will be visible in the Tools Macro list of macros.

VBA Sub procedure names will not be visible in Tools Macro, but you can still manually type in the name and the [Run] button will undim.

VBA Functions will be useable in Excel formulae in any workbook. However, when used in workbooks other than the workbook that contains the function, the formula will have to mention the workbook name. For example, MyBook!MyFunction()

VBA Functions that you have defined in the .xla that you have opened, can be referenced without the filename when you use them in other workbooks.

Users will be able to go to the VBE and see your source code.

Users can bypass automatic events when they open the workbook e.g. the Workbook_Open event, by holding down the Shift key.

Users will not be able to go to see your source code. Note: An Add-In does not automatically have this feature but you should use the VBE’s menu - Tools, VBAProject Properties, select the Protection tab and enable the Lock Project for Viewing checkbox. You’ll need to supply a password for this action.

Users can bypass automatic events.

Users cannot bypass automatic events.

The Property of the Workbook, (use the Properties window) IsAddIn will be set to False.

The Property of the Workbook, (use the Properties window) IsAddIn will be set to True.

XL97 How to Create an Add-in File in Microsoft Excel 97

XL97 How to Convert an Add-in File into a Workbook

Ways to load an Add-In or a plain Workbook

Use File, Open or

Put the file in the ..\XLSTART directory so that it automatically loads when you run Excel. This directory is usually C:\Program Files\Microsoft Office\Office\XLSTART or

Put the file in the directory specified by the Alternate Startup File Location specified in Tools, Options, General. or

Reference and check the file using Tools, Add-Ins

How do you get comfortable with the VBE?

Excel ’97 now uses the same Visual Basic Editor / Development Environment as Word’97, Project’98, Visual Basic 5/6. This consists of several child windows inside a large VBE window.

If you find that you are having trouble "seeing" the windows because some take up the whole span or others "hide", go to the VBE menu, Tools, Options, Docking and set all of the listed windows NOT to dock.

Additionally, there can be several code windows open. If you double click on Sheet1 or Sheet2 or Sheet3 or ThisWorkbook, you will cause a source code window to open for each object. Excel ’97 has class module windows attached to each of these objects. Additionally, if you have created a plain Module and double click on Module1, that will be another window. Be careful of which code window you are creating your code in – keep an eye on the title bar of the code window that you are working on.

See XL97 Changes to Microsoft Visual Basic for Applications