Code Example: Creating Menu Buttons for VBA Add-Ins

Sure, you can write VBA code. Sure, you can add a button to spreadsheet to trigger that code. Sure, you can set that code to run automatically in the background after a certain action is performed. But, how do you set up an add-in so that nobody has to open and see a blank spreadsheet every time they want to choose to just run your code (perhaps on a different workbook than where your code resides)? Well, here's a good way to get started...

*For the purposes of this post, I'll be referring to Excel (usually 2010, sometimes 2003), but this is mostly transferrable to Word as well.

---

First thing's first: Write your code!
Get your function up and running and doing whatever it is you want it to do, and make sure it's doing it properly. There's no point in having an add-in with menu buttons without an operation to perform or if the code is working incorrectly! Get all these details ironed out first, then move on to the next step.

---

Set up your code for the long haul.
To work with the menus, you'll need to keep a consistent naming convention, otherwise, menus may get lost or broken. There is some error catching that can help with this, but it is not always enough. To be more proactive against these errors, you should keep some variables saved as Constant values. These values are set once in your code and they cannot be changed by the code, that way your menus should always remain functional. For example, these are the values you’ll want for your add-in:

Const conXLMenuBar As String = "Worksheet Menu Bar"

The String literal in this first line is a standard name in Excel and should always be this. The conXLMenuBar variable name can be changed if you like, but it should remain descriptive to you know what it means later.

Const conYourAddInButton As String = "Click this button to run your code"

Both the variable name (“conYourAddInButton”) and the String literal in this example are customizable to your needs. “Click this button to run your code” will ultimately be the text that shows up on the button, so you'll want to keep it short.

---

Clean up after yourself!
To actually get some buttons and make sure your menu bar doesn't explode as a result, there are two main functions you'll need to add to your code modules: Auto_Open() and Auto_Close(). (The names should make it obvious about when they AUTOmatically run.) I typically like to add these to their own module named AddinSetup or something like that (to make this code easier to find the in the future) and separate from the other code you have or will add in the future. You need both an auto-open and auto-close set of Subs, otherwise, you run the risk of creating multiple/duplicate items in your menu when you open and/or close Excel. For that reason, I'll start this section off by demonstrating the Auto_Close() functionality.

Sub Auto_Close()
On Error Resume Next
Application.CommandBars(conXLMenuBar).Controls(conYourAddInButton).Delete
Err.Clear
On Error GoTo 0
End Sub

The program or code may fail from time to time, so we use the On Error Resume Next to continue silently if there is a problem. Admittedly, this is not the best coding practice, but it will suffice for what we have here. On Error GoTo 0 resets this once we're done removing the old menu.

When it doesn't fail, this will look in the standard Excel menu (remember conXLMenuBar?) for your custom button (conYourAddInButton) and remove it. If that item does not exist, there will be an error, but we've already got that situation covered, and the code will silently continue.

---

Time to make the doughnuts buttons!
That’s all there is to the clean up, so let’s move to the open functionality, which actually creates the buttons we want to use.

Sub Auto_Open()
On Error Resume Next
Application.CommandBars(conXLMenuBar).Controls(conYourAddInButton).Delete
On Error GoTo 0
If Application.Version < 14 Then
MsgBox "Microsoft Office 2010 (or higher) required to use these tools.", _
vbCritical, "Incompatible Verison"
ThisWorkbook.Close False
End If
With Application.CommandBars(conXLMenuBar).Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = conYourAddInButton
.FaceId = 524
.Style = msoButtonIconAndCaption
.OnAction = "YourFunctionNameGoesHere"
End With
End Sub

At the beginning, you'll see we've actually duplicated the code from the Auto_Close() block. This again helps prevent duplication by pre-emptively removing anything that looks like what we are about to add. If everything is working as normal, this should not be necessary, but it's a simple step to add that just adds that extra, nice redundancy.

The next section does a version check of the Excel program. This is not explicitly required, but if you are working with pivots in 2010, or other objects types/functionality that is not backwards compatible, I highly recommend it. Trying to run these kinds of functions in previous versions requires additional and more in-depth programming to prevent errors that will occur. While taking care of that cleanly is probably better, this catch does help prevent some possible, unexpected errors. If Excel fails the version check, there is a quick warning MsgBox() to the user, then the add-in will automatically close. This closure also triggers the deletion of the buttons we had planned to use, so there's just one more opportunity for the code to clean up the workspace.

The last step is the meat of this whole process: actually creating the button. There are couple options you can use to customize this, but I'll just start with this one. First, we use a With block to help make the processing of options smoother. The more options we want to set, especially if those options dig more than one level down into the Object, the simpler your life will become by using the With. With uses the Object (a newly added msoControlButton in this case) to shortcut to the options available to the Object.

Application.CommandBars(conXLMenuBar).Controls.Add(Type:=msoControlButton) is the line that actually creates the button. Alone, it is not much use, since the button will be blank (no image or text) and will not do anything when clicked. The options within the With block are what make the button useful. Below is a description of these options...

• .BeginGroup
  • Helps organize your buttons if you have more than one
• .Caption
  • Text that will display on the button
    • see the conYourAddInButton reference above
  • Name by which you reference the button later
    • e.g. when deleting it from the menu
• .FaceId
  • Tells the application which built-in icon to use
  • There are a few thousand of these, so don't bother trying to memorize them. You can find multiple guides/references online to help you pick a good one.
• .Style
  • Tells Excel how you want to display the button.
    • In this case, we are using msoButtonIconAndCaption, which means we want to show both the icon (FaceId) and text on the button.
• .OnAction
  • The most critical piece, as it actually tells Excel what to do when the button is pressed.
  • The value you set is a String within double quotes (“”) that is the name of the function you made in your other module.
  Excel will look through your code for a matching function name and run that, so make sure your function is specified as Public. (i.e. instead of just Sub FunctionName(), use Public Sub FunctionName()).

---

Let's wrap it up.
Once that's done, save your Excel file as an add-in. In 2003, this is simply the .xla extension, but in 2007+, .xlam is also available. Just choose which is most applicable for your use. You should now have a function Excel add-in with built in menu buttons!