|   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 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 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 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 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 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, as shown in Figure 7-11. Figure 7-11. 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. | 