My First Add-in Help

NOTE: The following instructions will be from the perspective of using the Excel version of the My First Add-in template. Although there are few, it will be noted where there are any differences in the process for using the PowerPoint and Word versions of the template.

Modifying Your Add-in’s Ribbon Layout

All the code you will be editing to adjust the layout of your Ribbon buttons will be located in the RibbonSetup module of your Template. You can use the keyboard shortcut Alt+F11 to open up the Visual Basic Editor to view the VBA code.

Types of Buttons

• Button Groups (6 groups) – There are up to six groupings that you can use to organize your buttons. Groups are separated by Separators (grey vertical lines) in the Ribbon
• Regular Buttons (10 per group) – Normal buttons, you click them and they execute VBA code
• Drop-Down Buttons (3 per group) – Click these and up to 10 buttons will show up within the drop-down menu
• Split Buttons (3 per group) – Same as a drop-down button with a clickable button to the left of the drop-down arrow symbol
• Launcher Buttons (1 per group) – Acts just as a normal button. These are typically used to display userforms with settings in them

Using The Table Of Contents

There are a ton of lines of code due to the number of buttons available within this template. I have created a table of contents macro so you can easily navigate through the various macros. To navigate, simply right-click on the name of the macro you wish to be taken to and hit the “d” key on your keyboard (or you can manually select “Definition” from the menu.

If you want to return back to the Table of Contents, you can use the keyboard shortcut ctrl+Home to scroll to the top of the Module.

Edit Button Visibility

The GetVisible subroutine will control whether your buttons or groups of buttons are displayed on the Ribbon UI. Simply navigate to the GetVisible macro within the RibbonSetup module and use “=Show” to make a button visible or use “=Hide” to not display a particular button.

Edit Button Labels

If you would like to include a textual label along with your buttons, you can insert your label text within the GetLabel macro.

The GetLabel macro also provides a way for you to name your Ribbon tab and also your button groups.

Choose Button Icons

To call one of the Microsoft Office icons available to you, you will need to add the name of the icon (Case-Sensitive!) into the GetImage macro. To learn how to view all the icon images available to you, visit the Choosing Icons For Your Add-ins section of this help page.

Determine Button Icon Size

You only have two options for your button’s size in the Ribbon. 

• Small – 16×16 pixel icon that can be stacked up to 3 icons in a column
• Large – 32×32 pixel icon and cannot be stacked

To modify the size, simply type the word “Small” or “Large” within the Case line corresponding with your button’s ID within the GetSize macro.

Run Macros When Buttons Are Clicked

To determine what the button will do once clicked, you will need to provide an action within the RunMacro macro. You can run a macro by including “Call YourMacroNameHere” or you can initialize a userform by including “YourUserform.Show”.

Provide Button Descriptions on Hover

To provide your users with button-specific instructions or descriptions, you can include ScreenTips and SuperTips. The text you add will display to your users when they hover over a button with their mouse cursor. All text MUST be surrounded with double quotation marks.

Adding Your VBA Macro Code To Your Add-in

The My First Add-in template has a placeholder module called “Macros” where you can paste in the macros that your Ribbon buttons will execute.

You can add all your macros into this single module or you can create multiple modules (Right-Click >> Insert >> Module) to store your VBA code in.

Once you have a macro written you can link it to a specific Ribbon button by navigating to the RibbonSetup module. Once viewing the RibbonSetup module’s code, use the Table of Contents or scroll down to the subroutine called RunMacro.

Within the RunMacro subroutine, you can modify your desired button to call your macro code instead of the DummyMacro by overriding the name with your macros name.

Optimizing Your VBA Code

Due to the fact that this template is set up to operate over 150 buttons, chances are there are going to be lines of code that you don’t end up using. I recommend that you comment any unused lines of code out so that your add-in does not cycle through code lines that are not needed.

You can comment code out by either manually placing an apostrophe in front of a specific line of code or you can highlight of area of code and use the Comment Block button within your Edit toolbar.

Choosing Icons For Your Add-in

In order to view the hundreds of icons that Microsoft has made available to us to use in our add-ins you will first need to download an Excel file called Office2007IconsGallery.xlsm.

Download: Office2007IconsGallery.xlsm (from Microsoft’s website originally, but the page was taken down – now hosted on this website)

After you have downloaded the file from Microsoft’s website you will want to open it and navigate to the Developer tab of your Ribbon. If your Developer tab is not appearing you can learn how to make it visible HERE.

Within the Developer tab, you should see a new group of buttons in the Office Icons group.

By viewing the galleries within each of these drop-downs, you can browse all the available icons that are at your disposal. Once you have found one you want to use, hover over the icon and write down its name. Note that the name is Case Sensitive!

Once you have the name of the icon you want to use, it’s time to add it to your VBA code. Navigate to the Visual Basic Editor and double-click on the RibbonSetup Module within the Project pane.

Use the Table of Contents or scroll down to the GetImage macro code. Locate the button you would like to change and replace the placeholder icon name “ObjectPictureFill” with your new name (in this example “StarRatedFull“).

To see your new icon appear in your Ribbon interface you will want to Save your file and close out of it. When you re-open up your My First Add-in file, you will see your new icon get loaded into your Ribbon tab!

Naming Your Add-in Project

To get your Add-in’s Name and Description showing in the Add-ins dialog box, follow these steps before saving as an add-in file type.

1. Go to File >> Info
2. In the Properties section, click Show All Properties (link at very bottom)
3. Fill in the Title with your add-in’s official name
4. Fill in the Comments with your desired description
5. Save your file

Converting The Template Into An Add-in File

Now it’s time to ice the cake! You’ve worked hard to put together your add-in and you’re at a place where you want to convert the My First Add-in template into a unique add-in file that you can use and distribute.

So here’s what you need to do:

1. Open up the Save As dialog (File >> Save As)
2. Give your Add-in a name
3. Make the Save As Type equal .xlam (PowerPoint = .ppam | Word = .dotm)
4. Choose a folder to save your add-in file in
5. Click Save

That’s it! You should now have an add-in file saved to your computer and you can move on to installing your add-in into Excel.

PowerPoint Note: For PowerPoint add-ins, Microsoft does not allow you to edit the VBA within .ppam file types. So if you ever need to make edits to your VBA code you will need to go back and tweak your template file and then resave it as a .ppam file. Super annoying, but make sure you know not to delete your template after you have created your add-in version, you will definitely need it!

Password Protection & Encryption For Your Add-in

Many add-in developers like to protect their code so users can’t get in and mess around with the code, inevitably breaking something. Another reason for preventing users from modifying your code could be to protect someone else from taking credit for all your hard work. Either way, if you are interested in protecting your code, here are a couple of options you can use.

Using Password Protection (Weaker Option)

To add a password to protect unauthorized users from your VBA code, you must do the following:

1. Right-Click on your VBA Project in the Visual Basic Editor’s Project Editor Pane
2. Select the Properties option
3. Navigate to the Protection tab
4. Check the Lock Project For Viewing checkbox
5. Input a password to protect your VBA code
6. Click OK

NOTE: Password protection will protect your VBA code from most Excel users but beware there are a lot of programs available on the internet that can easily bypass the password protection provided by Microsoft. You may want to use the program described in the next section if you are worried about folks determined to get into your add-in’s code.

Using Encryption (Stronger Option)

If you are worried about protecting your code from getting stolen or pirated, I highly recommend using a product called Unviewable+. I use this product to protect all the add-ins I sell here on TheSpreadsheetGuru.com website and it is the best VBA protection software currently available.

The way this software works is it locks down your VBA project so no one (not even you) can gain access to it. So essentially you will want to make a copy of your unprotected add-in and lock it up with Unviewable+ to distribute. 

If you want to learn more about this product and its features, you can read my review here.

That’s All, Any Questions?

I’ve tried to cover everything I could think of to get your add-in up and running. If I happened to miss something or if you need clarification on something please don’t hesitate to email me or leave a comment below.

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.