Overview
In last week’s post, I discussed how valuable macros can be, and how you can get started with them. One of the best ways to leverage macros is with add-ins. Having macros live within an add-in is a great way to share them, provide a user interface for them, and ensure they are always available, no matter which Excel file you are working in.
Add-ins come in a variety of flavors – some are extremely basic, some are advanced, and some are built to support specific applications and processes. In this article, we’ll discuss what add-ins are, why they are useful, how you can build your own, and some examples of what they can do.
Why use add-ins
An add-in is a special Excel file type (usually .xlam) that enhances Excel with additional functionality. Generally once it is installed, it launches automatically every time you open Excel and works with any Excel files you open. It often will provide a user interface through the Excel ribbon, pop-up forms, and/or keyboard shortcuts. Users can often customize add-ins for their specific preferences, such as with formats. An add-in can contain both macros – where all kinds of steps can be automated – and user-defined functions, which can be used within macros and just like other standard functions within cell formulas.
There are several benefits of using add-ins:
Saving time
Macros within an add-in can save you time in a variety of ways from small, frequent bits of time to large, repetitive-task bursts of time. Having many related macros together in an add-in can compound the time savings. That saved time often leads to reducing errors and freeing up capacity for value-added activities.
User interface
Making macros as clear and easy to run as possible via a user interface (UI) further saves time and and reduces frustration. The three main sources of UIs in add-ins are the Excel ribbon, pop-up forms, and keyboard shortcuts. For the most common actions, using keyboard shortcuts is usually the best option. Auto-complete powered search boxes can also be extremely helpful in avoiding a lengthy search for the action you want to run. Often the Excel ribbon, while it may look pretty, is the slowest option because the mouse is usually required and actions can be buried all over the menu.
Distribution
Add-ins are an easy way to share your macros and user-defined functions with other users inside and outside of your organization. No need to copy and paste the VBA code or worry about version control with macros being spread across dozens of different Excel templates.
Customization
Being able to make customizations based on preferences such as formatting, default actions, favorites, etc. can further save time, reduce errors, and speed up user adoption. Some add-ins even allow you to take your existing macros and include them within the add-in so they are available in the add-in user interface.
Application integration
Many applications leverage add-ins to import and export data. Excel is extremely useful for this because of the spreadsheet UI, the ability to reference cell values, and analytical tools like filtering, sorting, and charting. I’ve built some great, flexible reports using add-ins that connect to key applications.
Building an Excel add-in
If you’ve never even built a macro, this section might be more than you need! If you have built macros or if you’re curious, it’s easier than you might think to build add-ins. My colleague Chris Newman (aka “The Spreadsheet Guru”) has a fabulous article and several tools to build your own add-in. In fact, I used his article as a very helpful guide to build the XLEV8 add-in!
Here’s what you’ll need to make an add-in:
Existing or new macros / user-defined functions
You’ll want to either already have macros/UDFs or know what you want to build. There’s really no point to having an add-in without them! When building your add-in, consider building it in a scalable way as you’ll likely want to add more macros to it over time. Think about combining any macros you use in several different templates that would be easier to use and maintain by having them all in one place. Keep in mind that anyone who would need to use the macros/UDFs would also need to have the add-in installed to leverage them.
User interface
While it’s not technically required to have a ribbon UI for your add-in, it sure makes it look slick. It’s also the main way mouse users would run macros. The ribbon UI is based on XML and includes elements like sections, menus, buttons, icons, and labels. There are other UI options, such as prompts and pop-up boxes that could enhance the user experience. Keyboard shortcuts can also automatically be assigned (or let users assign them!).
Examples of add-in macros and user-defined functions
While any type of macro or UDF can be housed within an add-in, certain types make even more sense. In general, the more frequently you use a macro or UDF across different Excel files, the more it makes sense to include it in an add-in.
Formatting
Formatting is something we do in Excel about as frequently as anything else. The ability to save and easily apply your favorite/default formats is really helpful. Two examples in the XLEV8 add-in are toggling favorite highlight colors and toggling favorite number formats. It’s amazing how much time you can save when you make it as easy as possible and lay off the mouse!
Navigating
Navigating within workbooks, worksheets, cells, and even folders is a common task that can be time-consuming. Macros can expedite that by helping you maneuver with ease. Two examples in the XLEV8 add-in are using the Sheet Action Picker (jump to first sheet, last sheet, search sheets by name, create table of contents, etc.) and the Search Recent Files box (search saved files with auto-complete).
Integrations
Many systems integrate with Excel directly, taking the file import/export friction out of the process. Excel plays nicely with other MS Office apps like Word (i.e. automate templated messages), PowerPoint (i.e. automate refreshing slide content), and Outlook (i.e. automate filing your email messages), but via the XLEV8 API Configuration macro, you can connect to virtually any application that offers APIs! If that’s not enough, you can also automate repetitive web-based processes by controlling Google Chrome from Excel!
Summary
Add-ins can significantly enhance the usefulness and power of Excel, for general usage, for specific use cases, and to aid in managing certain applications. They house macros and user-defined functions you can leverage from any workbook, and they make it easy to share useful tools with colleagues. They validate Excel as a flexible platform, well beyond the accounting and finance image that people often associate with Excel. A small army of developers (including me!) have built out-of-the-box add-ins that save tremendous time (often 10+ hours a week!), reduce risk, and help you produce valuable work. If you haven’t yet tried an Excel add-in, you’ll be amazed at what they can do. And you’ll wonder how you ever lived without them!
Have you leveraged an Excel add-in before? What kinds of functionality did it have? Did it make Excel much more useful for you? Please share your thoughts in the comments below!
Recent Comments