Segment 3: Creating an Excel Add-in

This post is the first segment of the three-segment series: An Introduction to Excel Macros and VBA.

Storing Macros

Macros can be stored in several places. One of the most common places is in your Personal Macro Workbook (an option when recording a macro). However, with this method the macro only works with your user login on your computer. There are a few ways to share your macro with others. The macros can be recorded within the active workbook (as done previously) and saved to the workbook or as an Excel Add-in.

To save the macro within the workbook or create an Excel Add-in, select Save As from your active workbook. To save the recorded macros within the workbook, save the file as an “Excel Macro-Enabled Workbook (*.xlsm).” To create an Add-in, save the file as an “Excel Add-in (*.xlam).”

Saving as an Add-in File

Download the file CreatePareto Macro.xlsm and open it. Once opened, select Save As. From the drop down list (for Save as type:), chose to save the file as an Excel Add-in (*.xlam). Notice that the folder where you are saving your file has changed. Add-ins are stored by default in this folder, but it is not necessary to save it here. In fact, it can make finding the file very difficult. Save the file in a location more familiar to you.

Notice that your opened file is still labeled as an *.xlsm file. When saving as an Excel Add-in, Excel only saves a copy of the active file. That means if you make any changes to the file and save it, the changes won’t be saved to the Add-in file.

Navigating an Excel Add-in

Excel Add-ins can become very difficult to work with because of their unique saving properties. However, there is a way around this. First, close the *.xlsm file and open your recently saved *.xlam file. Next, open the VBA Editor (Alt + F11) and navigate to VBAProject (CreatePareto Macro.xlam) in the side bar. Click the plus next to Microsoft Excel Objects and select ThisWorkbook. Locate the property “IsAddin” and select False from the drop-down menu.

You will notice that the workbook CreatePareto Macro.xlam is once again open as a spreadsheet in the main Excel window. The workbook contains only one sheet, named “Settings.” You may have noticed that the VBA code references this sheet. The code used is:

ThisWorkbook.Sheets(“Settings”)

The difference between syntax is simple: referencing ThisWorkbook allows references to the Add-in’s sheets and referencing ActiveWorkbook allows references to the workbook currently open to the end user.

Having access to the Add-in workbook presents a tremendous benefit for Excel Add-ins. Settings can be stored in a worksheet within the workbook and retrieved during macros. Notice that one setting, called NewSheet, is already stored in the worksheet. Cell A1 contains the label (for organization only) and cell B1 contains the value, which is referenced several times in the macro code.

In the orginial ParetoAddin.xlam there was a Settings button that allowed the user to choose whether to create pareto charts in the active sheet or in a new sheet. When the user selects one of the options, then clicks Save, cell B1 in the Settings worksheet  is updated. When the user reopens the Settings dialog, the current setting is pulled from cell B1 and displayed. The Pareto Chart and Settings buttons are not included in this file, because the remainder of this lesson teaches how to create them.

Creating Groups and Buttons in the Excel Ribbon

Creating custom groups and buttons in Office 2007 and 2010 is very simple. Doing so requires the use of a free, open-source tool, created by the Microsoft Office User Experience team. The program, called “Custom UI Editor For Microsoft Office,” is available from openxmldeveloper.org. If you are unable to download the file from the link, search for the file on the site. Install the program or speak with the professor about using a computer that already has the program installed.

Launch the UI Editor from the start menu and notice the simplicity of the program’s interface. Although it appears simple, the UI Editor introduces a new concept that won’t be covered here: XML. For a great tutorial on learning to customize the ribbon with the UI Editor, visit http://www.rondebruin.nl/ribbon.htm. This lesson will only guide the student through the process and will not teach the student to create other ribbon buttons, groups, or tabs.

Click the open button from the UI Editor and open your add-in file (CreatePareto Macro.xlam). Right click the file on the left pane and select Office 2007 Custom UI Part. Although selecting 2007 limits some newer features, it provides functionality within Excel 2007 and 2010. Another file, called customUI.xml should appear below CreatePareto Macro.xlam. Insert the following code into the right pane:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

     <ribbon>

           <tabs>

                <tab idMso="TabInsert">

                     <group id="groupPareto" label="Pareto Chart Tool" insertAfterMso="GroupInsertChartsExcel">

                           <button id="buttonPareto" label="Pareto Chart" size="large" onAction="RunPareto" image="ParetoButton" />

                           <button id="buttonSettings" label="Settings" size="large" onAction="ShowSettings" imageMso="TableDesign" />

                     </group>

                </tab>

           </tabs>

     </ribbon>

</customUI>

Take a look at the code and notice the group and buttons it creates. The Settings button uses Microsoft image (Mso) named “TableDesign.” This image is already built into Excel. However, the Pareto Chart button uses a custom image. To add this image to the file, right click customUI.xml and select “Insert Icons….” Open the file ParetoButton.png.

There is one more important element of the XML code. Notice the each button has an onAction property. The onAction property references the macro, contained within the Add-in file, to be referenced when the button is clicked. The Pareto Chart and Settings button reference RunPareto and ShowSettings, respectively. These two macros must be added to the file for it to be functional. Save the changes made in the UI Editor and close the program.

Reopen “CreatePareto Macro.xlam” and make sure the new group appeared in the Insert tab (the file ParetoAddin.xlam should not be open). Open the VBA Editor (Ctrl + F11) and navigate to Module1 in “CreatePareto Macro.xlam.” You will need to add the RunPareto and ShowSettings macros to the Module. Here is the code:

Sub RunPareto(control As IRibbonControl)

     Call CreatePareto

End Sub

Sub ShowSettings(control As IRibbonControl)

    If ThisWorkbook.Sheets("Settings").Range("B1") = True Then

        Settings.NewSheet = True

    Else

        Settings.inActiveSheet = True

    End If

   

    Settings.Show

End Sub

Notice that all the RunPareto code does is call the subroutine CreatePareto. Technically the contents of CreatePareto could be included here. However, the ShowSettings code does not launch a subroutine. It does two things. First, it pulls the previously saved settings from the Settings sheet and sets the dialog to display those settings. Second, it displays a UserForm.

Creating UserForms

To create the settings dialog, you must create a UserForm. Right click on VBAProject (CreatePareto Macro).xlsm and select “Insert,” then “UserForm.” Rename the UserForm “Settings” and build it to look similar to the Settings dialog in the ParetoAddin.xlam file. Use Option Buttons to allow the user to only select one option and use Command Buttons to create the Cancel and Save buttons.

Adding Functionality to a UserForm

By clicking on each button, you can assign properties. It is generally a good idea to make the Save button default, so that if the user hits Enter the settings are saved. Make sure to name the buttons appropriately, so that they are easy to reference in code. Name the option to create Pareto Charts in a New Sheet as “NewSheet” and the other option “inActiveSheet” (they are referenced in code so the naming is critical).

Each button requires code to function. To assign code to the Cancel button, double click on it. A code window will open, allowing you to enter code. Enter Me.Hide. This closes the window without saving any changes. To return to the UserForm creator, navigate to Settings within Forms of the VBAProject. Right click Settings and select “View Object.” Double click the Save button and enter the following code:

'Saves Pareto Chart setting in Settings sheet (Cell B1)

If NewSheet = True Then

    ThisWorkbook.Sheets("Settings").Range("B1") = True

Else

    ThisWorkbook.Sheets("Settings").Range("B1") = False

End If

'Save and Hide

ThisWorkbook.Save 'Saves Add-in "workbook"

Me.Hide 'Closes settings window

This code is what makes the settings dialog work. Notice the If… Then… Else statement used. NewSheet refers to the option in the UserForm and when it is True (selected), the code stores “True” in cell B2. If it is not True (aka False), the code saves “False.” The Add-in is then saved (saving the settings) and the UserForm is closed. If you struggle with creating the UserForm or its accompanying code, take a look at the ParetoAddin.xlam file.

Test the New Add-in

With the added buttons in the Ribbon and the new UserForm, the Excel Add-in is ready for use. Test it out and make sure everything works properly. If anything is broken, troubleshoot your setup until you can resolve the problems.

Deploy the Add-in

There are a few ways to deploy an Add-in in a business environment. The Excel Add-in file can be provided to everyone who will use the Add-in. Each user can add the Add-in through Excel. If updates are made to the Add-in, however, they will not be passed on to the installed Add-ins. To avoid this, the Add-in file can be installed on a network location. Refer to the post “Network Add-in Deployment.doc” for instructions on this method.