Overview
Macros are a polarizing topic in Excel – some people love them, some people hate them, and some people have no clue what they are. Personally, I can’t live without them. They can automate so many tasks, well beyond the financial schedules that people typically associate with Excel. With more and more being demanded of us in our jobs, it’s crucial to automate and optimize whatever repetitive work you can to focus on the valuable stuff – managing exceptions, analyzing data, and building relationships with colleagues and customers.
Macros let you own much of that automation, whether you build something yourself, have someone else do it, or leverage a tool where the macros already exist. In this article, we’ll discuss what macros are, why they are useful, the building blocks of a macro, and how to get started. I’ll also open with my personal story that’s driven a ton of my success.
My history with macros
Here’s a secret that those who meet me find surprising: I used to HATE programming. When I was looking for my first job at 16 years old, my dad gave me a huge VBA (Visual Basic for Applications) reference guide and told me it would be really useful and make me a lot more than the usual teenage jobs. I tried and after a couple hours, I was too bored. I chose the job at the amusement park where the girls were. I took a few required programming classes in high school and college, and found them painful. So what changed?
I took an Excel class in college at Texas A&M University (whoop!) that showed us how we would use it in public and corporate accounting. I was intrigued. It was led by my favorite professor, who had us do some extremely boring, repetitive tasks. Then she showed us how to use macros (and some creative formulas) to automate them. I was hooked! All of a sudden, programming was really interesting – like a game or a puzzle. I just needed my WHY (to do things a LOT quicker!).
A few months into my first post-college job, I worked on a unique project with a LOT of colleagues, without much space. It was like the middle seat on an airplane. Because I could literally only move my mouse about an inch at a time, I got really good at keyboard shortcuts – as many as I could learn. Then I learned I could set macros to keyboard shortcuts, so I recorded and built a bunch of them, mostly simple stuff like Ctrl+y = yellow highlight. That led to automating more and more of the repetitive work. Then I focused on stuff that could benefit the larger group. I built a reputation for being extremely useful very quickly!
Over the years, I’ve built thousands of macros – some for specific scenarios and others that were highly reusable. Many of them literally changed lives (less overtime!). A few years ago, I learned from Chris Newman (aka The Spreadsheet Guru) how to build an add-in. I had accumulated hundreds of useful macros, so I thought it would be the best way to share them with others! I cleaned them up, added tons of instructions and validations, and I launched the XLEV8 add-in. It includes a tracker where you can see how much time you’re saving (10+ hours a week for me!).
I often want to go back to that 16-year-old and teach him the same way my college professor did. Who knows what would have happened? But I believe things happen for a reason, and we have to leverage our experiences to find our purpose. Hopefully you can learn from my experience!
Why use macros
First let’s define what macros are, at least in Excel. A macro is a series of steps that Excel can execute for you, such as creating a sheet, filling in values, opening a file, copy/paste, etc. Virtually every command you can do manually in Excel (thousands of them!) can be automated with a macro. Some are simple – like applying three types of formatting in one step, and some are more advanced, like looping through hundreds of files and copying specific items into one central list. They can even integrate with lots of other applications to facilitate data being imported and exported. Many other applications have concepts similar to macros where logic, steps, and processes can be automated and even scheduled. In Excel (and other MS Office apps), macros are built with the VBA (Visual Basic for Applications) programming language.
Saving time
The most obvious benefit of using macros is saving time. I like to look at this in four buckets:
- Nano – save 1-5 seconds each time for the commands you perform hundreds of times a day (example: highlighting cells with your favorite colors). Assign the most frequent commands to keyboard shortcuts, because it’s much quicker than using the mouse!
- Micro – save 5-30 seconds each time for commands that you may run less frequently, take longer to find in the Excel menus, or combine a few steps together (example: apply AutoFilter, freeze panes for the top row, and auto-fit the column sizes). Some of these warrant keyboard shortcuts too!
- Macro – save a few minutes and sometimes a few hours by running commands in bulk for the super-repetitive tasks (example: renaming or inserting several sheets, copying/pasting several files, etc.).
- Specific – save a wide variety of time by automating all the steps in a common process (example: copy data from a separate file, sort and filter it, save it in a new location, and email it to colleagues). These are built for a specific use case, so the time saved is extremely varied.
Reducing errors
Manual, repetitive steps are often ripe for errors, because there are so many chances to make them, and repetitive work tends to boring and fatiguing. The more steps you can automate (assuming with sufficient validation checks and testing), the lower the chances that errors are made, leading to a lot less rework!
Enhancing quality
By automating processes, you can often enhance the quality of the process itself and the output, for two key reasons. First, you’re freeing up time that can be used for more review and analysis (and often those components can also be built into the automation!). Second, you’re able to do things we just wouldn’t even consider doing manually. As an example, in a prior role of mine, we built a 120-slide PowerPoint file because I built a macro that could automatically refresh it in a few minutes (manually, it would have taken two full days of work!). We wouldn’t even consider building and refreshing that many slides with the need to spend so much time updating them.
Macro building blocks
The VBA programming language that macros are built with shares many similar concepts with other modern programming languages. It’s extremely well-documented throughout the internet, including on Microsoft’s website. It is object oriented, meaning objects (such as workbooks, worksheets, cells, etc.) have properties, methods, and events.
Variables
Variables are an important concept in any programming language. They allow you to set a value (i.e. current_row = 1) that can be referenced over and over again. Sometimes they are required, and sometimes they are just easier to work with. It’s a lot easier to type and read “current_row” than ActiveWorkbook.ActiveSheet.ActiveCell.Row, isn’t it?
Conditionals
Conditional statements contain the fundamental logic that most macros require to complete their tasks. If-then-else is the most common type. When the condition is met, one or more lines of code will execute, and when it is not met, they are skipped. Multiple conditions can be evaluated within a nested approach or with And or Or operators. The Select/Case statement allows you to specify several separate conditions with their separate code to execute when the condition is met (kind of like the options on a multiple-choice test). Think of it in a logical sense: if the milk is gone, add milk to the grocery list.
Loops
A huge benefit of macros is the ability to do high volume, bulk tasks extremely quickly, such as formatting several files, sheets, or ranges in one step. This is often accomplished with loops. There are different kinds of loops (for, for each, while, do while) that increment one counter at a time, loop through all the components of an array, or continue while a certain condition is met. Be careful with loops – it’s easy to accidentally get caught in an infinite loop that can never finish, causing Excel to freeze.
How to get started with macros
There are several ways to leverage macros, and one or more of them might be right for you. Macros can be stored in an individual file (only available if that file is open), your personal macro workbook, or within an add-in. In your personal macro workbook and add-ins, macros are generally always available to run, no matter which file(s) you have open.
Build your own
It’s extremely easy to build your own macro, thanks to the built-in macro recorder. Access it from the Developer tab on the ribbon menu (it may be hidden). Simply turn on the recorder, perform the actions you want to automate, then stop the recorder. It helps to review the code (use the Visual Basic Editor within the Developer ribbon tab) to make sure there isn’t a lot of extra junk included. Seeing what code is built by the recorder is a great way to learn how VBA works. If you can precisely describe what you’re trying to do, a Google search or AI prompt can help you build it from scratch pretty quickly.
Have someone else build it
While they aren’t on every corner, there are many people who are really good at developing macros (including me!). The more you make, the better you get at quickly realizing what to do for a project, and the bigger your library of VBA code you can reuse to quickly complete a project. If going this route, make sure to define the outcome, the steps to automate, and ensure the developer will build in sufficient validation checks and documentation that can help troubleshoot later.
Out of the box
There are countless commercial products you can buy and install with macros already built, ready to use, such as the XLEV8 add-in. An add-in is a common way for a few or a lot of features to be packaged together in a product that can be installed and always be available, no matter which files you’re working in. Some are industry or use-case specific, and some have general functionality for virtually any type of Excel user. This is often the most economical way to get a ton of functionality and value at a low price. Make sure to understand the key features and how you might benefit from them.
Summary
You might be like I was at 16 years old – no interest in programming whatsoever. But technology has evolved to the point that we’re all doing programming to some extent (even if it’s just setting up some DVR recordings!). Building formulas in Excel is actually a lot like programming, and building/using macros is just a small step further. Macros can save a tremendous amount of time, reduce a lot of error risk, and enhance the quality of your files. Those make you an incredibly valuable resource!
What kind of macros have you used in your experience? What are some manual, repetitive tasks you perform that could probably be automated? Keeping your eyes open is the first step! Please share your thoughts in the comments below!
Recent Comments