Creating my macro and inserting it into XLplus
Inserting your macros into the menu structure of XLplus can be done by following some simple steps. This tutorial explains this process in detail. After you finished the tutorial you are able to include your own macros into XLplus.
The tutorial assumes we want to include a macro for the Finance department in XLplus. The tool will get the meaningful name „Finance Tools“.
As said in Using My Own Macros with XLplus the easiest way getting started is using the supplied demo file myOwnMacro.xlam.
1. Open the Add-In file myOwnMacro.xlam.
Assuming you have installed XLplus using default settings the file can be found within the XLplus folder underneath of your My Documents folder.
Pic. 1: Open myOwnMacro.xlam
After the file was opened you can use XLplus tools in order to convert the Add-In file into a workbook. Once it has been converted to a workbook you can edit its sheets and configure your macro for installation.
2. Use the XLplus Add-In Manager in order to convert the loaded Add-In to a Workbook.
Use option Manage Open Excel-Addins from the XLplus Ribbon to start the Add-In Manager.
Pic. 2: Start the Add-In Manager
Select the Add-In myOwnMacro.xlam from the list of all open Add-Ins and use option Convert to Workbook.
Pic. 3: Convert the Add-In to a Workbook
The Add-In Manager will now show the Add-In as converted to a workbook. Since we do not require the Add-In Manager for the moment, we can minimize the dialog.
Pic. 4: Minimize the Add-In Manager
In order to minimize the dialog, click on the minus button at top of the dialog. We now can start editing the workbook. We have to adjust install settings on the following worksheets:
- Install_Settings
- Install_Ribbon
3. Configure Macro Information on worksheet Install_Settings.
Worksheet Install_Settings contains general information about the macro and will be used within the installation dialogs XLplus will display.
Pic. 5: Install_Settings
The first information we have to set is the Add-On Name of our tool. We set this to Finance Tools by overwriting the existent content of cell A2 (Pic. 5 displays the worksheet using R1C1 notation; you can use [Ctrl]+[Shift]+R to toggle the display between R1C1 and A1 notation).
Note: We have to specify Add-On Name on worksheet Install_Ribbon as well (in column Add-On Name). It is crucial to use an identical name on both sheets. In case the names used are not identical XLplus will not be able to successfully uninstall or update your macro.
We can specify a Version Number, too. Version Numbers are useful in case you plan to extend your tool at later time. In our example the Version Number consists of three parts:
- The first number indicates the major release of the macro,
- the second number indicates significant feature extensions to the tool
- and the last number denotes all bug fixes applied.
You can type in any kind of Version Number and do not have to follow this method. Instead you could simply count up your tools using a single number or character.
Next we have to list our Tools. This list is used to successfully uninstall or update our macro.
Each tool of your macro has to be listed in Column C one per row. We will create just one tool and it shall convert Dollars to Euro. The tool will get the English label Dollar To Euro.
Note: The Tool names listed here have to be identical with all tools listed in column objLabel for language English on worksheet Install_Ribbon. This is column K or column number 11. Please assure that the tools entered in both sheets are identical, i.e. that no leading or trailing spaces are entered.
Finally we enter a Description for our macro. In case we do not want to enter a German description we could simply copy over the English description.
4. Configure the Ribbon on sheet Install_Ribbon
We now have prepared installation data of our macro. The next task is creating a menu for our tool. Since our tool is a simple dialog based application we only require a very basic menu. The menu will consist of a container for our current macro and all our future macros related to financial tasks. Finally it will contain a button which will launch our current macro.
Pic. 6: Configuration on sheet Install_Ribbon
Since we just require two rows of data for our tool we first delete all rows except the first two data rows. The data rows start with row 3 - we therefore delete everything below row 4. We can now overwrite existing data and configure our menu.
We first specify an objHierachyID. Following the tips given on Using My Macros with XLplus we will use a number greater than 110 for our tools and place all macros related to Finance under the ID 120 (you can specify any other number instead, provided it is greater than 100).
In order to identify our tools more easily we set objContainer to the label Finance. All finance related tools, which might be developed later, should get this label, helping to easily identify them.
The first entry we create is a container for our Finance Tools – we therefore do not have to specify an Add-On Name.
We can – but do not have to – set a unique ID for this record (in case we do not specify a unique ID or in case the ID is used already XLplus will create a unique ID for us). In the example we set the ID to FinanceID.
The next information is important as it steers rendering the menu later on. We specify 1 for column objIsContainer since this object is a container object. The object is of type menu so we set column objType to menu (this value is case sensitive).
The menu will not get an image, so columns objImage and objMsoImage are left blank.
We can specify that the menu shall show small icons by setting objItemSize to normal. Since this is the default setting we do not have to specify this.
Since our currently edited record is a menu, no Action is associated with it – we leave column objOnAction empty.
We finally set the menu name to be shown for language English and German. Column objLabel for the English language will be set to Finance, whereas column objLabel for German language will be set to Finanzen.
Next we will configure the record for the actual macro.
Since this is a new record we edit the next row of the sheet.
Again we specify 120 as objHierachyID and enter Finance as objContainer.
In column Add-On Name we will enter the Add-Ons name which is used to identify this macro. This Add-On Name has to be identical with the name we defined on worksheet Install_Settings. In our example we will set Add-On Name to Finance Tools.
Again we can but do not have to specify an ID – we set objID to FinanceID_ctrlBtn1.
Now we specify objIsContainer – since this object is a button and therefore no container we set this column value to 0. We advise XLplus to render a button in the menu by setting objType to button.
We want to include an icon in our menu which shall precede our tools name. We have created an icon with dimensions of 24x24 pixels in an image processing software and named it dollar.gif. In case of transparent images you can use GIF or PNG files. Our example image does not use transparency.
We copied our image to the images folder underneath the folder XLplus is installed in.
Since we are going to use an external image we set column objImage to \dollar.gif. XLplus will create a correct path to the image when installing the macro. Of course column objMsoImage will stay empty since we have referenced an image already.
We now have to specify the command to run when clicking on the menu button. In column objOnAction we enter our command which will be: Finance_Tools.DollarToEuro. In this case, Finance_Tools points to a module in our VBA project which contains a sub procedure named DollarToEuro. We will show later how to create this.
Finally, we have to create our labels and tooltips. First we specify the English label of our macro by setting column objLabel to Dollar To Euro.
Note: This label has to be identical with the label you entered in column Tools on sheet Install_Settings. In case they are not identical XLplus will not be able to correctly uninstall or update your Add-In.
On column objSupertip as well as column objScreentip we can specify quick help for users hovering our menu button. We keep it short and specify “This tool converts dollar to Euro” for both columns.
That´s it – we configured our macro.
We can now save our work by either overwriting the demo file or creating a new Add-In. Please see Point 6 for details. Before, we will create the macro.
5. Create the Add-In
We first add a new module to the existing demo project.
In order to do that, we have to open the VBA Editor of MS Excel. This can be done by pressing [Alt]+F11 or using the Ribbon by going to Developer – Visual Basic.
In case you cannot see a Developer Menu on the Ribbon you first have to activate it. To do so open your MS Excel Options, switch to Customize Ribbon and activate the Developer option.
Pic. 7: Customize Ribbon (MS Excel 2010)
Within the VBA Editor identify the project of the file myOwnMacro.xlam. Expand the tree and locate the section Modules.
Pic. 8: Create Modules in the VBA Editor
From the menu select Insert – Module in order to create a new module.
Pic. 9: Name a module in VBA Editor
Select the module inserted within the tree. In the properties pane on the left hand side the name of the module is given. Overwrite the name with Finance_Tools. This is the module name we specified in sheet Insert_Ribbon within column objOnAction.
Afterwards insert a procedure into this module by selecting menu option Insert – Procedure.
Pic. 10: Create a Procedure within the VBA Editor
In the following dialog enter a name for the procedure. The name has to be DollarToEuro, as we have specified on sheet Insert_Ribbon in the objOnAction column. We can reference a procedure in a module by concatenation of both names using a dot as separator, i.e. Finance_Tools.DollarToEuro.
Pic. 11: Naming a procedure in the VBA Editor
The dialog will create a procedure stump which we can fill with our commands. In our example we will show a simple message box.
Pic. 12: Sample application
We now have created our macro and configured it for being installed into XLplus. We finally have to save our work.
6. Saving our new macro
We can simply overwrite the demo file with our new code – since we adjusted the configuration, only our new tool will be installed within XLplus. The drawback of this method is that we will loose all of our configuration examples of the original demo file. You therefore might prefer to save the new macro under a new name.
In order to overwrite the demo Add-In we restore the XLplus Add-In Manager. In a previous step we had minimized it. By clicking the restore button its previous size will be restored.
Pic. 13: Restore a minimized dialog
Within the Add-In Manager we can now convert the Workbook back to an Add-In as shown in Pic. 4 above. Afterwards we select the Add-In on the list of all Add-Ins and Save it.
Pic. 14: Save the Add-In using the Add-In Manager
In order to save the new macro under a new name we do not convert it back to an Add-In using the Add-In Manager. Instead we simply use Save As from the MS Excel menu.
Pic. 15: Save a new macro as new Add-In
Within the Save As dialog we specify Excel Add-In (*.xlam) as file type. This will immediately change the location to save the file in to your personal Add-In folder. You can save the file there and when installing it within XLplus specify this location within the XLplus Add-On Manager. Since it is more convenient having all Add-Ins stored in one location you might prefer changing the location to save the file in to the XLplus directory underneath your My Documents folder.
7. Installing your new macro in XLplus
After our macro was created and saved we can install it in XLplus using the XLplus Add-On Manager. We browse to the folder we saved the new Add-In in and from the list of Add-Ins shown we select our new tool.
Pic. 16: Install a new macro using the Add-On Manager
As you can see the macro can be identified by the Add-On Name (Finance Tools) we specified on sheet Install_Settings. Additionally information like Version Number, Description and location are shown.
Pic. 17: Installation Dialog
During the installation all details we specified will be shown, helping third party users identifying the tool to install. Once the tool was successfully installed in XLplus it is available from the XLplus Add-On menu option as shown in Picture 18.
Pic. 18: A new macro, hosted by XLplus
The example shows the new Container we have created for all Finance related tools (submenu Finance), the icon we have specified and the Label of our macro button. Additionally Tooltip and Supertip are shown.
8. Extending our macro
We now can add more financial related tools to our new macro. We just have to add new macros (objects of type button) to sheet Insert_Ribbon. This can easily be accomplished by copying row 4 to row 5 and adapt relevant information.
Once we have finished these settings we have to list the new tools in column Tools of sheet Install_Settings.
We can convert the new macro file to a workbook by using the XLplus Add-In Manager, so we can edit these sheets. Afterwards we convert it back to an Add-In and save the file.
Using the XLplus Add-On Manager we can install the new version of the macro.
In case you wish to split your tools between several departments (i.e. Sales, Logistics, etc.) it is recommended to create single tools for these functional units. The departments can solely install their tools of interest then.
If, instead, you wish to add several functional units into one tool you can divide them by adding a new menu option to sheet Install_Ribbon. The demo file shows how to do this.