Programming in Microsoft Excel – Macros – Part 2

Last time we introduced how to create a simple macro in Excel and use it to restructure a list of product inventory data.  During that discussion, we mentioned creating a shortcut key to execute the macro from the keyboard.  That may be all you need if you are the only one who needs to use the macro or if you only have a few macros.  However, what do you do if you plan to distribute your workbook to others to use and you want and expect them to be able to use your macros?  Do you really think they will go through the Developer ribbon and open the Macros dialog to discover what macros you created and how to access them?

Probably not.  Therefore, you want to ‘publish’ your macros in a way that other users can easily see and use them.  The easy way to do this is to add them to either one of the existing ribbons or to create your own macro ribbon.  In this blog, I’ll take you through the steps on how to add your macro to an existing ribbon and how to create a new macro ribbon to publish your macros.  I’ll also show you how to add macros to the Quick Access Toolbar at the top of the Excel screen.

To customize the current ribbon, you can right click in a blank area to the right of the ribbon to show the following menu.

This action takes you to the same Excel Options dialog you saw last time that you could also get to by opening the File menu and selecting Options near the bottom of the menu.  By right clicking on the ribbon where you want to add the macro, the dialog opens immediately to the correct ribbon tab and expands the groups.  However, Excel does not know what you want to do to the ribbon, so it opens the list of Popular Commands as your source for commands to add to the ribbon.  Switch the Choose Commands From dropdown box to: Macros to see only the macros you created.

If you want to add a macro to an existing ribbon, you will quickly find out that you cannot add commands to the existing groups.  However, you can add a custom group to the ribbon where you can place your macros by following these steps.

  • Click on the tab (ribbon) name where you want to add your macro.
  • Click the New Group button at the bottom of the column.  This creates a new group named: New Group (Custom)
  • Rename the new group by clicking the Rename button. (Excel should select the new group upon created it.  If not, select it first.)  For this example, I will rename my group: Macros.
  • In the Display Name dialog, provide a unique name for the group.  Although this dialog (shown later in these steps) displays icons that you can display with the group, I think this was a mistake as Excel only uses icons with items within a group.
  • Now with your new group renamed and selected, return to the list of macros on the left and choose the one you want to add and click the Add button.

  • With the macro added to your macro group, you can rename the macro to something more user friendly by clicking the Rename button as shown below.  Note here that either you can accept the default icon for macros or you can select a different macro to represent your command.

  • Click Ok to close the Rename dialog. Click OK on the Excel Options dialog to close it.

Now when you open the ribbon, you will see your Macros group and any macros you added to the group.  To execute the macro, you now only need to position your cursor on the cell where you want the macro to begin and then click on the desired macro in the ribbon.

To create your own Macro ribbon, the only addition to the above instructions is that before you create a new group in an existing ribbon, click the New Tab at the bottom of the Customize the Ribbon page of the Excel Options.  This creates not only a new tab (ribbon) but also adds the first group to the ribbon.  You can rename the tab by selecting it and clicking the Rename button just like you would do to rename a group or a item within a group (a really multi-functional button!).  You can even rearrange the

order of tabs, groups within tabs, and items within groups by using the up and down arrow buttons along the right side of the tab list control.  The figure to the right shows a new tab I created called Macros with a group named: Macros by MPA that only has a single command: Inventory Restructure displayed with a custom icon.

You can also add a macro to the Quick Access Toolbar at the top of the Excel window by clicking on the arrow to the right of the Quick Access Toolbar and then selecting More Commands:

You can also right click in an empty area on any ribbon and select Customize Quick Access Toolbar.  Both options take you again to the Excel Options dialog but this time to the Quick Access Toolbar page.  Again select Macros from the list of available commands rather than Popular Commands.  Choose your macro and then click the Add button as shown below:

Notice that in this toolbar, there is no Rename option.  For some reason, Microsoft decided to name this button Modify.  In any case, you can click the Modify button after adding your macro to the toolbar to rename it and to provide a different icon.  You might say, why do I need to provide a display name since the Quick Access Toolbar only shows the icon?  Well, Excel uses the Display Name in the Tooltip when you hover your mouse pointer over the icon.  Therefore, you may still want to change it.

Which method should you use to make your macros available for use?  I suppose if you have many macros, you might want to create a separate tab (ribbon) for your macros, perhaps separating them into groups based on their function.   If you only have a few macros, you could either add them to the end of one of the existing toolbars in their own group or perhaps to make it easy for end-users to find them, put them in the Quick Access Toolbar.

Next time, we will look at different places where you can store your macros and how you might choose the best location.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s