Chapter 41: Turning Your VBA Code into an Add-In

Overview

Throughout this book, you have built samples as macros that have all been run from the code window or from a command button on the spreadsheet. This is fine if you are writing these just for your own use or for a power user . You know exactly how the code works and how to invoke it because you wrote it.

However, what if you want a wider audience for your macros? This could be in your workplace, or you could even be thinking about producing a commercial application. Other users will not want to mess about running odd bits of VBA code. They probably have no idea how the code modules work, and you probably don't want them viewing your code, because you don't want them to change something and cause a bug. Most likely, users will also want to use it in conjunction with their own spreadsheets.

The answer is to create an add-in. You are probably already familiar with third-party add-ins for Excel. If you use Tools Add-Ins on the spreadsheet menu, you will find a number of these that come with Excel, some of which will be loaded. Fortunately, it is relatively easy to turn your code into an add-in file.

The first thing you need is a proper user interface to call all your procedures. The best way to do this is to add your own menu onto the Excel menu structure. This was explained in Chapter 11.

You will be creating an add-in called Magic, as reflected in the menu and naming conventions in the code.

Assuming that you have entered in all the code from the previous examples and inserted the appropriate UserForms, you must now add in the following code for the menu structure. If you have entered the code into different files, you can use Copy and Paste to collect all your code into one module to insert in a final file. If you have not done all the examples, then you can choose not to include the menu bar command for that particular example. The easiest way to do this is to Rem out the lines you do not need by putting the single quote (') character in front of the line. The line will turn green and will become a comment instead of actual code. For example, if you want to remove the Calculate Range option, you Rem out the following statements:

 .Controls.Add(Type:=msoControlButton).Caption = "Calculate Range" 
 
 .Controls("Calculate Range").OnAction = "range_calculate" 

This removes the line that sets up the option on the menu and the line that provides the action.

 Sub menu() 
 
 Dim newsubitem As Object 
 
 CommandBars("Worksheet menu bar") _ 
 .Controls("Tools").Controls.Add(Type:=msoControlPopup).Caption 
 = "Magic" 
 
 Set newsubitem = CommandBars("worksheet menu bar") _ 
 .Controls("tools").Controls("magic") 
 
 With newsubitem 
 
 .Controls.Add(Type:=msoControlButton).Caption = _ 
 "Absolute Relative Formula" 
 
 .Controls("Absolute Relative Formula").OnAction = 
 "conv_formula" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Calculate Range" 
 
 .Controls("Calculate Range").OnAction = 
 "range_calculate" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Change Values" 
 
 .Controls("Change Values").OnAction = "change_val" 
 
 .Controls.Add(Type:=msoControlButton).Caption = _ 
 "Color Alternate Columns" 
 
 .Controls("Colour Alternate Columns").OnAction = 
 "shade1" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Color Alternate Rows" 
 
 .Controls("Colour Alternate Rows").OnAction = 
 "shade" 
 
 .Controls.Add(Type:=msoControlButton).Caption = _ 
 "Color cells with Formula" 
 
 .Controls("Colour Cells with Formula").OnAction = 
 "col_cell" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Contents to Label" 
 
 .Controls("Contents to Label").OnAction = 
 "contents_to_label" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Copy Hidden  Sheets" 
 
 .Controls("Copy hidden Sheets").OnAction = 
 "hidden_sheets" 
 
 .Controls.Add(Type:=msoControlButton).Caption = _ 
 "Enter Formulae as Notes" 
 
 .Controls("Enter Formulae as Notes").OnAction = 
 "note" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Label to Number" 
 
 .Controls("Label to Number").OnAction = 
 "label_to_number" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Matrix Total" 
 
 .Controls("Matrix Total").OnAction = 
 "matrix_total" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Reverse Label" 
 
 .Controls("Reverse Label").OnAction = 
 "reverse_label" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Search" 
 
 .Controls("Sort Sheets").OnAction = "findsheet" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Sort Sheets" 
 
 .Controls("Sort Sheets").OnAction = "sortsheet" 
 
 .Controls.Add(Type:=msoControlButton).Caption = 
 "Transpose" 
 
 .Controls("Transpose").OnAction = "transpose" 
 
 End With 
 
 End Sub 

Initially, a variable called newsubitem is dimensioned as an object. A new menu item is then added to the Tools menu bar ‚ but notice it is not added as a standard menu item but as a pop-up ( type=msoControlPopup ), which means it can have a submenu beneath it. This submenu will hold all the routines.

The variable newsubitem is set to this menu item. Using the With statement, the individual menu items are added to the submenu. Each one is added as a control button ( type=msoControlButton ), and the OnAction property is set to the name of the subroutine.

Enter the preceding code into a module and run it. There will be an option called Magic on the Tools menu. Select it, and you will see a submenu, as shown in Figure 41-1.

Figure 41-1: Example of creation of a pop-up submenu

Having created this menu, you also need code to remove it. The user of your add-in may not want to retain it on their copy of Excel, and they need a way to remove the menu. This is done by simply deleting the Magic menu using the Delete command:

 Sub remove_menu() 
 CommandBars("Worksheet menu bar").Controls("Tools"). _ 
 Controls("magic").Delete 
 End Sub 

From the user's angle, how will these routines be enacted? When the add-in is installed, the menu structure needs to appear. When the add-in is uninstalled , the menu needs to be removed. Fortunately, there are events built in to take care of this. On the Project tree, open Microsoft Excel Objects and double-click ThisWorkbook . This is the object for the workbook for your add-in. This will default to the Workbook_Open event, but for an add-in, you want everything to happen when the add-in is installed, not when the workbook is opened.

Use the drop-down list on the top right-hand corner of the module window and scroll upward until you see AddinInstall and AddinUninstall . Click AddinInstall and enter the following code:

 Private Sub Workbook_AddinInstall() 
 Call menu 
 End Sub 

This routine calls the menu subroutine that you already coded. Whenever the add-in is installed, the menu subroutine will be called and the new menu structure will be added. One safety feature here is that the add-in can only be installed once; the only other option is to uninstall it. This means that the menu can only appear once ‚ it would confuse the user if they ended up with more than one menu item for the same thing.

Click AddinDeinstall in the drop-down in the top-right corner of the module and add the following code:

 Private Sub Workbook_AddinUninstall() 
 Call remove_menu 
 End Sub 

This calls the subroutine remove_menu , which deletes the Magic menu. This happens when the user uninstalls your add-in so that no trace is left of it.

You almost have a complete add-in. The next step is to name it so that when users install it they can easily identify it. To do this, select Tools VBAProject Properties, and you will see a form called VBAProject Project Properties. Enter the project name ‚ this sets the name of the add-in and the filename, so choose sensibly. You can also choose a description.

You may want to prevent your users from viewing your code and changing it. You can do this by clicking the Protection tab, as shown in Figure 41-2. Check the Lock Project for Viewing box and enter and re-enter your password. Click OK. Do not under any circumstances forget your password! If you do, there is no way to view your code and, unless you have a backup copy without a password on it, all your hard work will be lost.

Figure 41-2: Setting a password to protect your add-in

All that remains to be done is to turn your project into an add-in file. Click the spreadsheet and select File Save As from the spreadsheet menu. On the Save As Type drop-down, there is an option to save as Microsoft Excel Add-In, as shown in Figure 41-3. Select the Addin filter and click OK. Your file will now be saved with an XLA extension instead of an XLS extension.

Figure 41-3: Saving your file as an add-in

Exit Excel and reopen it to test your add-in. Use Tools Add-Ins on the spreadsheet menu to install your add-in. You should see it automatically listed, but if you don't, use the Browse button to locate the file. Figure 41-4 shows the Add-Ins window and the Browse button. Check the box next to the add-in that you have selected.

Figure 41-4: Installing your add-in

Click OK. Your add-in is now installed. Try selecting Tools Magic from the spreadsheet menu, and you should see the new menu options, as shown in Figure 41-1. Select some cells and try out the options to make sure they all work.

Notice that when you select the Window menu from the spreadsheet menu, there is no indication of your add-in being there. This is because the workbook belonging to the add-in exists only virtually. It can be used by the VBA code, as you saw in the example for transposing a range of cells in Chapter 21, but it cannot appear onscreen.

Note ‚  

Once you have created an add-in, you cannot turn it back into an Excel spreadsheet at a later date.

Press Alt-F11 and look at the VBA code screen. You will see an object in the Project tree called Magic.xla , which represents your add-in. If you used password protection, you will be prompted for the password when you double-click it to open it.

You can still edit the VBA code and make changes, add other menu options, and insert new forms. However, now that it is an add-in, the only way to save your changes is by using File Save Magic.xla from the code menu. When you save your changes, make sure that you are saving the right code by clicking the cursor on the add-in within the Project tree. There are also modules for the current workbook, and you can easily save these instead of your add-in data. If in doubt, click the add-in in the Project tree and make sure that the File Save option on the code menu has the name of your add-in.

Try uninstalling the add-in. Use Tools Add-Ins from the spreadsheet menu and locate your add-in in the list. Uncheck the check box and click OK. Now choose Tools from the spreadsheet menu, and you will see that the Magic menu option has disappeared.