MS-Excel / General Formatting

---

Distribute Macros

Although you can distribute a macro along with a workbook, if you want to distribute only the macro's functionality, an Excel add-in is the way to go. An Excel add-in is nothing more than an Excel workbook that was saved as an add-in by selecting the Office button → Save As... → Microsoft Excel Add-in (*.xlam); however, in pre-2007 versions, select File → Save As... → Microsoft Excel Add-in (*.xla).

Once it's saved and reopened, the workbook will be hidden and can be seen only in the Project Explorer via the VBE. It is not hidden in the same way as the Personal.xls file, as this can be seen (and made visible) via View → Unhide (pre-2007, Windows → Unhide).

Once you have completed the workbook you want to use as an add-in, you need to save a copy of it. You can save it to any location you want, but make sure to note where you placed it.

Open any workbook, and on the Office button, select Excel Options → Add-Ins (pre-2007, Tools → Add-Ins), make sure Add-Ins is showing in the Manage: box, and press Go. Click Browse, locate your add-in from where you saved it, select it, and then click OK.

Ensure that your add-in is in the Add-Ins Available: box and that the box is checked. Then click OK to install the add-in. You can save most code to an Excel add-in without too many changes. There are a few issues worth considering, however:

• The ThisWorkbook object will always refer to the add-in, not to the user's workbook. Use the ActiveWorkbook object instead.
• You cannot refer to sheets in the ActiveWorkbook with CodeNames.
• You should always put ribbons, etc, back to the way the user had them originally. There is nothing worse than an add-in that changes all your Excel settings without your knowledge.
• Always include some sort of error handling (yes, most add-ins will cause errors at some time).
• Be very aware that the user might have many sorts of protection applied. Never use code to unprotect any part of the user's workbook. Simply display a message asking the user to unprotect.
• Make full and good use of the worksheet you have in the add-in. We use the worksheet(s) to store user settings.
• Holding down the Shift key will not prevent add-in workbook events from running (holding down the Shift key will prevent a normal Excel file from running, however).
• If you need to look at or work with the add-in workbook again (e.g., to incorporate updates or modifications), go into the VBE while the add-in is installed and, from the Properties window, select the IsAddin property and set it to False. Saving the workbook as an add-in sets this property to True.
• Apply protection to the modules of your add-in by selecting Tools → VBAProject Properties → Protection.
• Once you have installed an add-in in Excel 2007, you can select the Add-Ins tab, then right-click on your add-in and select Add to Quick Access Toolbar. This will add an icon to the toolbar, which when clicked will display the add-in name for selection.

Add a Menu Item

If you aren't using Excel 2007, you won't have the Quick Access Toolbar option, so once you have created your add-in, you will need to make the macros within it easy for the user to run. This is best achieved by using the Workbook_AddinInstall and Workbook_AddinUnInstall events in the private module of the ThisWorkbook object. Simply double-click ThisWorkbook for the *.xla file, and Excel will take you into the private module where the code is placed.

Here is a simple example of the code:

Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall( )

On Error Resume Next 'Just in case
 'Delete any existing menu item that may have been left.
 Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
 'Add the new menu item and set a CommandBarButton variable to it
 Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
 'Work with the Variable
 With cControl
 .Caption = "Super Code"
 .Style = msoButtonCaption
 .OnAction = "MyGreatMacro" 'Macro stored in a Standard Module
 End With
On Error GoTo 0

End Sub

Private Sub Workbook_AddinUninstall( )

 On Error Resume Next 'In case it has already gone.
 Application.CommandBars("Worksheet Menu Bar").Controls("Super Code).Delete
 On Error GoTo 0

End Sub

This is all the code you'll need to add a single menu item (called Super Code) to the end of the existing worksheet menu bar as soon as the user installs the add-in via Tools → Add-Ins. When the Super Code menu item is clicked, a macro (that is within a standard module of the add-in) is run. Remember that the preceding code must be placed in the private module of ThisWorkbook for the add-in.