An Excel add-in is nothing more than an Excel workbook that was saved as an add-in by selecting 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 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 Tools menu, select Add-Ins, then 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
ThisWorkbookobject will always refer to the add-in, not to the user's workbook. Use theActiveWorkbookobject instead. -
You cannot refer to sheets in the
ActiveWorkbookwith CodeNames. -
You should always put toolbars, 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 such as toolbars.
-
Holding down the Shift key will not prevent add-in workbook events from running (holding down the Shift key will prevent a normal .xls file from running, however).
-
If you need to see or work with the add-in workbook again to incorporate updates or modifications, go into the VBE while the add-in is installed and, from the Properties window, select the
IsAddinproperty 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 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.
Figure. Project Explorer with ThisWorkbook selected for an add-in named Number Manager.xla
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.
If you want the Super Code menu item added, say, before the format menu item, you can use this code:
Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall( )
Dim iContIndex As Integer
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
'Pass the index of the "Format" menu item number to a variable.
'Use the FindControl method to find its Index number. ID number _
is used in case of customization
iContIndex = Application.CommandBars.FindControl(ID:=30006).Index
'Add the new menu item and set a CommandBarButton variable to it.
'Use the number passed to our Integer variable to position it.
Set cControl = Application.CommandBars("Worksheet Menu Bar") _
.Controls.Add(Before:=iContIndex)
'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
You would not have to change the Workbook_AddinUninstall( ) code in this case.
In these examples, all the menu item code is in Workbook_AddinInstall and Workbook_AddinUnInstall. This is not a problem when the code is adding only one menu item. If, however, you will be adding more than one item and, perhaps, even submenus, you should place the menu item code in a procedure (or two) inside a standard module. Then use some code such as this:
Private Sub Workbook_AddinInstall( )
Run "AddMenus"
End Sub
Private Sub Workbook_AddinUninstall( )
Run "DeleteMenu"
End Sub
In the standard module, put some code such as this:
Sub AddMenus( )
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
'(2)Set a CommandBar variable to the worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
'(3)Return the index number of the Help menu. We can then use _
this to place a custom menu before it.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
'(4)Add a control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)
'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"
'(6)Working with our new control, add a sub control and _
give it a caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control and give it a caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.
'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&xt Menu"
'Add a control to the sub menu just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = "MyMacro2"
End With
On Error GoTo 0
End Sub
Sub DeleteMenu( )
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
End Sub
When using the OnAction property, it is possible that you may encounter problems if there is a macro in the user's workbook that has the exact same name as a macro that resides in your add-in. To play it safe, it is often a good idea to use a method like this:
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = ThisWorkbook.Name & "!MyMacro2"
End With
By doing this, you ensure that Excel knows which macro you want run when the user clicks the button. With these snippets of code, you'll find it easy to distribute and use macros to their fullest potential.