In this piece, I want to clarify a topic that might cause some confusion: Excel macros.
You may have often heard me say that there are “macros” and then there are “macros” — meaning there are two distinct meanings to the term, and people frequently mix them up. I’ve also mentioned that the older concept of macros is not only obsolete, but it was never particularly useful in spreadsheets to begin with. This statement might raise some curiosity, or even opposition, so let me explain.
First, let’s recap what we’re talking about when we refer to the “old” macro. This type of macro was incredibly useful in the DOS era for word processing, where we could only run one application at a time and features like copy and paste or a mouse didn’t exist. These were called “keyboard macros,” and subsequently referred to as “macros” for short, and while they were helpful for automating text manipulation in word processing, they were never as practical in spreadsheets. They simply remembered the keystrokes. Yes, in those days, menu choices were made with keystrokes. No mouse.
Fast forward to today, and Excel programming has evolved into something more complex — VBA – Visual Basic for Applications. Yet, the term “macro” is still commonly used to describe both recorded macros and VBA programming. Unfortunately, most people who refer to macros are often only familiar with the “Record Macro” button on the Developer tab, which allows users to automate simple actions by recording them. This, however, is not what true Excel programming is about.
Why doesn’t recorded macros work well in real-life scenarios? The answer lies in data dependencies and variability. When you record a macro, Excel repeats the exact steps you performed. But if anything in the data changes — be it the structure, source, or shape of the data — that macro won’t work as expected. In other words, recorded macros are inflexible and often useless for anything beyond trivial, cosmetic tasks.
Despite this, 99% of Excel courses start with teaching how to record macros. Some trainers argue that this is a good way for beginners to “dip their toe into programming.” I disagree. This method misguides learners from the start and conditions them to think that Excel programming is about repeating recorded actions. This wrong start can prevent some people from ever grasping what real Excel programming is about.
So, what is Excel programming really about? First, we must recognize that everything you see on the screen in Excel is an illusion — like the visuals in a Formula One racing game. Just as a video game simulates a racing car, Excel renders grids, cells, and sheets, but these are objects created by programmers behind the scenes. Excel’s objects — cells, ranges, workbooks, and sheets — have properties – like color or value – and methods – actions you can trigger, like clearing a cell. What we’re doing when we program in Excel is interacting with these objects at a level below the visual interface.
Now, here’s where things get interesting. There are two levels of programming in Excel: low-level programming – done by the engineers who create the application – and high-level programming – which is what we do when we write VBA or even just use Excel manually. Most Excel users only interact with the highest level — they manipulate the interface using the mouse, ribbon, and keyboard. But in our training, we go one step lower, working with Excel programmatically. This allows us to automate tasks, implement conditional logic, and create loops that make Excel much more powerful.
One of the most powerful aspects of Excel programming is something many people don’t realize: not all objects in Excel are visible. For example, Excel’s data access capabilities – like using ActiveX Data Objects, or ADO – are not accessible from the ribbon but can be manipulated programmatically. This level of programming gives us access to tools far beyond what Power Query or dynamic array formulas offer, and it’s much more efficient and applicable company-wide. With just a few lines of code, we can accomplish what would take countless clicks in Power Query.
But here’s the key point: learning Excel programming properly doesn’t start with recording macros. Recording a macro gives the false impression that Excel can simply repeat recorded actions, which isn’t the case in real-world spreadsheet tasks. That said, there are two things recording a macro can be useful for.
First, it can help you learn. Many of us from older generations learned Excel programming by recording macros and studying the generated code. This helped us understand Excel’s object model — its properties and methods. So, while running recorded macros isn’t useful for automation, looking at the code behind them can provide valuable insights.
Second, recorded macros can sometimes be helpful as a quick-and-dirty solution, especially for repetitive tasks that don’t have dependencies. For example, if you have some data that needs formatting in a specific way, you can record a macro to apply column headings, totals, and color formatting. If this structure doesn’t change, the macro will work every time, saving you time.
Today, tools like ChatGPT can also help by taking the long, verbose code generated by Excel and refactoring it into something cleaner and more efficient. You can copy your recorded macro into ChatGPT, ask it to optimize the code, and voilà — you have a streamlined solution.
In summary, recording macros isn’t a good way to learn Excel programming, but it does have a place in helping us understand the objects and processes behind the scenes. True Excel programming is about working with the objects beneath the surface, manipulating their properties and methods to create powerful, automated workflows. And once you grasp that concept, the possibilities with Excel are endless.
Thank you for reading. This is a podcast by Hiran de Silva. Narrated by Bill.
Add comment