Updated: Mar 21
New to the Excel macro recorder? Not sure where to start?
The key to getting started with the Excel macro recorder is to start small. You’ve probably got a list of 10 or 15 steps you’d like to automate in Excel. That’s great, but don’t start there.
Instead, start by trying to create a macro that automates just one or two of those steps. Get that macro working correctly. Then create another one for the next step. Lather. Rinse. Repeat.
Once you’ve built a series of small macros that work correctly, create a “master” macro – one that runs all of the other macros.
I know it might seem like a waste of your time to create a tiny macro that performs just a single step or two, when it’s just as quick to manually perform that task. But they key here is to build a series of macros that, when combined, can automate a bunch of tasks for you.
If you’re already tried the macro recorder, you’ve probably already run into problems with it. First, the macro recorder is very literal, and sometimes it misinterprets what you’re trying to do. If you spend 20 minutes recording a big macro only to discover that it doesn’t always work the way you want it to, you’re quickly going to get frustrated with having to re-record.
Second, you may also have found that after perfectly executing the first few steps, you make a mistake toward the end of your recording. Once again, you’ll have to stop and re-start your recording.
Rather than using this perfect-or-bust approach, create tiny macros that focus on just one or two tasks. Each should only take you a couple of minutes to create and test. If you find that something doesn’t work the way you expected, go back and re-record. As I stated earlier, after you’ve got two or more tiny macros, you can create a macro that simply runs those macros sequentially. Yes, a macro can run a macro. Cool, right?
Hint: if the macro recording is consistently failing to do what you want, study up on Absolute vs Relative references. That’s the most common cause of problems.
Another benefit of this method is that as you create those tiny macros, you’ll be learning. As you gain experience, you’ll be able to create bigger and more complicated macros. Despite that, I think you’ll find that this piece-by-piece approach is very effective, and you may continue to use it. Personally, though I’m quite capable of recording massive macros, I don’t do it. Instead, I find it much more productive to use the approach I’ve described here.
If you run into issues with your macros, don't hesitate to reach out.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at firstname.lastname@example.org.
© North Port Solutions, LLC, 2022. All rights reserved.