VBA Distribution Problems?
Tens of thousands of people around the world use VBA everyday to automate tasks in their Microsoft Office Applications. While these VBA macros can be extremely powerful for the users who know how to execute them, what most VBA users don't seem to understand very well is how to distribute their macros to their co-workers, managers, or the general public.
So what goes into a well distributed add-in application? Well, there's the VBA code (which most people seem to understand) but there is also graphic design work and Ribbon user-interface (UI) coding that when thrown into the mix makes your application easy to use and understand. This is where the pain-point exists for most of us self-taught VBA coders out there.
After years of creating VBA add-ins used by analysts and companies around the world, I decided something needed to be done to make life easier for the masses. Most people who use VBA aren't computer programmers. In fact, I myself am a financial analyst by trade and didn't study a lick of computer programming in school.
So how could I make it easy for all of us users who are self-taught and don't have time searching the internet for all the pieces to the puzzle? The solution I came up with was to create a dynamic VBA add-in template that you can literally paste in your VBA macros and manage the Ribbon UI and icons directly within the comforts of VBA.
Let me introduce you to my custom package of Excel, PowerPoint, and Word VBA add-in templates simply called.....My First Add-in!
How It Works
When you first open up a My First Add-in file, you will see a new "Macro" tab in your Ribbon user interface. Within this ribbon tab will be all the available buttons you can include in your final add-in. There are four separate button groups so you can section off your macros.
Each of the four groups include:
- 10 Normal Buttons
- 3 Drop Down Menus with 12 additional buttons within the drop down
- 3 Split Menu Buttons with 12 additional buttons within the drop down
- 1 Launcher button
All-in-all you will have over 150 various types of clickable buttons to run macros within your add-in.
Using VBA To Manipulate The Ribbon UI
I've made My First Add-in EXTREMELY EASY to determine how your add-in's Ribbon UI appears and functions. Within each file there is a VBA module called RibbonSetup. This module holds 7 macros that allow you to:
- Turn a button's visibility on/off
- Determine what a button's text label says
- Determine which Microsoft Icon appears for a button (hundreds of icons to choose from)
- Determine if the size of the button is Normal or Large
- Designate which VBA macro gets called when a button is clicked
- Decide what the Screentip says when your mouse hovers over a button (think title for button description)
- Decide what the Supertip says when your mouse hovers over a button (think button description)
Here's a quick video showing you how easy it is to modify the 7 VBA macros to customize your add-ins Ribbon UI.
Access Microsoft's Entire Icon Library
Another difficulty most coders face is graphic design. With the way the My First Add-in template is set up, you will have access to all of Microsoft's icons that are used in the Office applications. This means you can simply browse the icons and pick which ones fit your needs, never worrying about colors, pixels, schematics, or anything thing else that goes into creating professionally-designed icons. You simply add the name of the icon into your VBA code and (Poof!) it appears on your Ribbon UI!!
Here are a few examples of what your Ribbon can look like:
The My First Add-in template is contained within an Excel/PowerPoint/Word file that holds a pre-coded VBA module. This VBA module allows you to completely customize the Ribbon UI for your add-in. I have gone ahead and written out all the tricky XML ribbon coding for you, so you get to stay within the comfort of working with VBA.
Pioneer Pack [$17]
You want to differentiate yourself from your peers. You have a bunch of VBA code that helps you do your work faster and more accurate. If only you knew a way to easily share all your capabilities with your co-workers. This pack of 1 add-in template per Office program will allow you to create the add-in of your dreams and impress everyone in the office. You are entering uncharted territory and you are ready to take a leap of faith and make your vision a reality.
Hero Pack [$37]
You don't just want to impress a few co-workers with your macro magic, you want to make an impact. You see needs throughout your company and you want to have the ability to create add-ins not only know but in the future when you run into situation that could really use some automation. The Hero Pack gives you the ability to run up to 5 individual add-ins (per application) at the same time. This means you can make a suite of Excel add-ins that carry out various task or that are tailored for different areas of your business.
Developer Pack [$87]
This is for the person who doesn't just want to stay within the confines of a "Package". You are a person who would rather learn how to fish than keep going back to the fish market. You're going to get 5 add-in templates to get you started, but you will also gain access to training videos that will show you how you can make an unlimited number of add-in templates. This will allow you to have no limitations on how many add-ins you can create and also will give you pointed in the right direction to add more functionalities to the templates.
The training will cover:
- What software you will need to download (don't worry, I show you all the freebies available to you)
- Basic concepts of XML coding (it's really not that complicated to pick up on!)
- How to use your own custom icons in the Ribbon
- Resources that will allow you to expand the capabilities of the My First Add-in template
Frequently Asked Questions
[Q] Can I upload my own icons into the Ribbon UI?
Using your own custom icons requires a few manual steps that could not be carried out by VBA alone. With My First Add-in you will only be allowed to use Microsoft's library of icons (in which there are hundreds to choose from). However, if you purchase the Developer Pack, I do show you how to load your own icons into your add-in's Ribbon.
[Q] Why Can't I Simply Just Buy the Pioneer Pack and make 5 copies?
Each add-in has to use a unique Ribbon ID within it's code. So simply copying an add-in will maintain that same unique Ribbon ID. The Hero Pack contains 5 My First Add-in templates that have their own individual Ribbon IDs, allowing you to run them all at the same time within your Office Application. With the Developer Pack I show you have to create new IDs so you don't risk sharing a Ribbon ID with another persons add-in.
[Q] Can I Distribute My Add-in Creations?
You most certainly can! However if you are planning to do a large distribution or even sell your creation, I would recommend you purchasing the Developer Pack so you can ensure your add-in creations have a truly unique ID and don't have trouble running alongside other developer's add-ins users might have installed. WHAT YOU MAY NOT DO is resell the template itself. That is my only restriction.
[Q] How Do I Protect My VBA Code When I Distribute It?
You can use the built-in VBA Project password protection but I do not recommend this as it can be easily bypassed. I use a software called Unviewable+ to protect all my VBA add-ins. I go into a little more detail on this at the bottom of this page.
[Q] Am I allowed to return this product?
Because there is no way for me to disable or physically take back this product, I am NOT ALLOWING any refunds on this particular product.
What Are You Waiting For?!
If you are anything like me, you've been dreaming of all the Add-ins you could make for Excel, PowerPoint, or Microsoft Word but you just didn't know how to get it built. Well now there is a very simple solution available to you with My First Add-in and there are no excuses. So pick up one of the three packs available and start building your very first add-in.
And trust me...you'll never forget your first!
Protect Your Add-ins Like A Pro
After you've nailed down creating your very own add-ins with the help of My First Add-in, you may want to start distributing your creation either for free or for monetary value. In order to ensure your VBA code is not stolen or tampered with, it is a great idea to protect it with an amazing application called Unviewable+. I have used Unviewable+ to protect all the Microsoft Add-in products I sell here on TheSpreadsheetGuru website. It is quick and easy to use and I don't know what I would do without it's capabilities. You can learn more about Unvieawable+ through my complete review of the product.