Overview
A few years ago, I started taking an approach that I’ve gone a little nuts with, and I wish I had done it sooner. It’s leveraging Excel itself to power macros using one or more “settings sheets.” A settings sheet is an Excel sheet that contains settings or values used by a macro to automate a task – usually something very repetitive, such as renaming sheets. Read on to understand how this works, why it’s helpful, and a common example.
How you do it
Building the settings sheet
Start by defining what should be on the settings sheet (or sheets, there could be multiple sheet working together). The sheet should contain the fields that will power the macro. If the settings sheet is not found in the active workbook, add it programmatically and alert the user. It can be built dynamically or copied from a template. I tend to build them dynamically unless they are complex. Include plenty of notes, instructions, and examples to guide users. Data validation is a plus here if relevant!
Filling in the settings sheet
Encourage users to leverage native Excel functions they are comfortable with – copy/paste, find/replace, filter/sort, etc. to fill in the settings efficiently. For example, if you’re copying a bunch of folders and files for different locations, copy and paste the list from some other file where it already exists!
Use formulas to quickly build a dynamic list. This is especially helpful for making a reusable template. I’ve built several templates I use every year in this way and they save a ton of time. My favorite is using a template to build 700+ appointments for our kids’ activities that I import to Outlook. It takes about 15 minutes to update from the prior year and a few seconds for them all to import. Doing that manually in Outlook would take days!
Processing the settings sheet
Once the sheet is populated, when the macro is run again, it should process the contents. Build in plenty of validation checks to account for all the different scenarios where users might mess something up. Below are some common examples. Once everything is validated, automate away!
Validation is especially important. If you can, provide row-level validation so users don’t replicate their mistakes for every row. I’ve recently been doing this and it’s such a nice user interface feature. I like to highlight errors and add a cell comment on the cell containing the error (or all the errors in one cell off to the side). Here are some common validation types:
- Valid file path, file name, sheet name, or range name
- Valid column letter, row number, or cell address
- Valid item from a pre-defined list
- Valid email address, website, phone number, zip code, or other common format
Why it’s helpful
Automate repetitive tasks
So much of what we do is repetitive, especially among accountants, which comprises most of my career. Often there’s a high volume of micro steps that we could automate, like copying and pasting, creating folders, files, or sheets, or refreshing some type of content. A settings sheet approach is great for highly repetitive tasks like this.
No-code option for users
Plenty of people would love to be more efficient, but they are scared to learn coding. With this approach, all the logic is done such that they just have to fill in the settings sheet. It’s just like a search form or contact form, but uses the familiar Excel interface.
Easy to reuse and share steps
This approach is easy to reuse over and over when you build the list using formulas and link them to inputs like dates (the example below does this). It’s also easy to share the settings with others assuming they have access to the same macros (this is what add-ins are great for!).
Example
The example below contains screenshots of the Create Subfolders macro. It allows us to create multiple folders in one step – 5, 10, 50, or even hundreds of folders can be created in seconds! These are the steps to run a macro like this one:
- Run the macro once, and it creates the sheet in the top-left part of the screenshot below.
- Fill the sheet quickly using copy/paste or other Excel features such as formulas, as were used in the top-right part of the screenshot below.
- Run the macro again to process the settings sheet contents.
- The folder goes from the empty state on the bottom-left to the filled state on the bottom-right!
There are dozens of macros in the XLEV8 Excel Add-in that work this way. Here are a few of my other favorites:
Summary
This might be a different way of approaching macros for you, but as you can see, there are so many useful benefits. It’s always good to reuse your effort, especially with tasks that are time-consuming. Best of all, we’ve build dozens of them already for you in this way with the XLEV8 Excel Add-in, and it’s constantly being enhanced. Give it a try today if you haven’t already!
Do you build macros like this – leveraging the table-like structure of Excel? Do you have other tips and tricks for building efficient, reusable macros for all to use? Let us know in the comments below!
Recent Comments