Why learn VBA when I can just use the Macro Recorder?

The VBA macro recorder in Excel is indeed a handy-dandy tool.
For a beginner, it’s a useful place to start learning about VBA.

Even for seasoned VBA developers it’s handy to quickly record an action when you’ve forgotten the syntax for it (PivotTables comes to mind).

If you want to automate a lot of the mundane, repetitive tasks you perform in Excel, it’s tempting to think you can simply hit the Macro Record button, perform the task once and it’s “job done”.

But there’s many important things you cannot achieve by recording a macro in Excel.

This means you must roll up your sleeves and write the VBA code. You can do that learn to do that yourself or hire a professional.

Here’s a few of them . . .

Slow & Inefficient VBA Code

Let's say you're recording a macro to format some cells.

You want some cells to be formatted italic, so you display the Format Cells dialog, select Italic and click OK.

The Macro Recorder doesn't pay attention to which option(s) you selected.
Instead it just notes down the value of each attribute (property) in the dialog (yeah, lazy, I know).

This is the VBA code you would have recorded:

Pretty verbose just to get italics, huh?

TintAndShade? You didn’t order that!

There’s a lot of stuff in there you didn’t do, don’t care about, and probably don’t want.

Perhaps all you really wanted was this:

Writing this (rather than recording) would produce far more succinct code.

Nota Bene: Your more succinct, hand-written VBA code will also run faster than the recorded version.

Even worse, recorded macros include poor programming practices. The resulting code is verbose, includes unnecessary actions, makes your code harder read, debug and modify.

The Macro Recorder Can't Make Decisions

Pink or Blue?

Chocolate or Vanilla?

Is 3.142 less or more than Pi?

If you need to make any decisions in your process (code), the macro recorder can't help you make them.

VBA has several ways to branch logic (make decisions).

For example, VBA let’s you make decisions with the IF statement, SELECT CASE, CHOOSE and the one-liner; IIF.

Here’s an example making a decision with an IF statement:

But none of these choices can be recorded.

If you want your automated process to have any capacity to make decisions, you'll need to write them manually yourself.

The Macro Recorder Can't Repeat Tasks (Efficiently)

Sometimes the task you want to do is pretty simple.

Maybe copy some cells from one location to another. Maybe you need to perform (repeat) this across multiple worksheets.

It would make sense to have one standard process (routine), and then repeat it for each worksheet.

VBA allows you to repeat tasks through loops such as FOR, FOR EACH, DO WHILE and DO UNTIL:

Here’s a quick example:

Once again, your macro recorder can't do this.

I hate to sound like a broken record (okay, now I’m repeating myself), but if you want any task performed multiple times (without repeating the code) you're going to have to write those loops yourself.

This allows you to perform tasks, hundreds or even thousands of times quickly and efficiently.

The Macro Recorder Can't Message You

Sometimes you'll want to prompt the person using your spreadsheet (that person might be you).

That prompt might be a confirmation that a process has finished:

Or it may be a question:

Either way, the macro recorder can’t display messages to either inform or prompt your users.

Again, you’ll need to write the code for that yourself.

Interacting with Users with Screens (a.k.a UserForms)

One way your Excel applications can add professionalism and interact with its users is via a screen or UserForm.

UserForms provide your users predesigned screens where they can enter data, select options or be guided through a complex process.

Here’s an example:

Via a userForm, your users can select options from lists, enter data in text boxes, makes decisions with option boxes (a.k.a radio buttons) and check boxes.

None of this can be recorded.

You’ll need to design the UserForm manually (or write code to automate it) and then write all the VBA to handle button clicks and all the tasks that should be performed.

Extending Excel by Controlling Other Applications

You’ve probably already guessed by now, and you’d be right - automating one application from another cannot be done using the macro recorder.

To make all the Microsoft Office application sing-and-dance together, you’ll need to write the VBA code yourself.

Tasks Outside Excel

Sometimes the tasks you need to perform extend beyond Excel (or Word, or Access or PowerPoint).

For example, you may need to copy files, get a list of files to process, check if another spreadsheet exists or confirm you have access to a network drive.

These are tasks performed with your computer (operating system) itself.
Sure, the macro recorder will allow you to select and open a file, but it won’t allow you to do the kinds of tasks you would perform with Windows Explorer, for example.

For tasks like these, you’ll need to write you own code.

The Macro Recorder “Selects” Everything

Most tasks in Windows are based on the follow two-step process:

1. Select an object
2. Perform a task

For example, in Windows Explorer this could be:

1. Select a file
2. Delete the file

Or in Excel this could be:

1. Select a cell
2. Copy cell

When you record a macro, you must “select” the object you want to work with before you can perform the “task”.

Here’s a code example:

To copy a range from one cell to another, you have to perform the two-step process twice!

Select the source cell, then copy.

Select the destination cell, then paste.

In each case you have to select the source cell and destination cell.

Not only does this make your code fragile it also slows it down.

Meanwhile, writing VBA gives you the power of telekinesis. You can move things without touching (selecting) them.

In this example, we’ve copied and pasted without selecting anything.

Heck, if you want to be even sneakier, you could achieve the same result in one line:

Recorder macros are full of “select” and “perform task” actions which slow your code down (dramatically).

The solution? Either record your macro and clean up the code or write your code yourself to have more control (and telekinetic powers!).

The Macro Recorder Hard-Codes Cell Addresses

Let’s say you had a simple spreadsheet in which you wanted to keep a running tally of values. Sales perhaps.

Manually, you might copy-and-paste these values each time you make a sale to the next blank row. Something like this:

If you started the macro recorder and performed this simple task, you’d end up with some VBA code that looks like this:

Take a look at the highlighted lines.

Not only are the affected cells “selected” (remember writing VBA gives you the power of telekinesis) but the cell addresses are “hardcoded”.

This means this process will only ever affect the cells mentioned.

If you insert a column or row; or you want to add the new sale detail after the last one, your macro will go wrong. You’ll want the task of copying the correct cells and pasting their values to the correct location to be dynamic.

To be fair, Excel’s macro recorder lets you use “relative” cell references when you record a macro.

If you selected this option, you’d end up with code like this:

It doesn’t entirely solve the issue and you’re presented with a new set of problems.

Writing this VBA manually would allow you to make the whole copy-and-pasting process dynamic giving you far more robust code.

Dialog and Input Boxes

There’s several ways you can have your Excel application interact with its users.

Screens (UserForms) and messages (MsgBox) are two I’ve mentioned so far.

You can also display Input Boxes to ask your user for information, like this:

Or you may want to ask your user to select a folder where the report is, like this:

You can't create dialog boxes and input boxes in a recorded macro but you can if you write it by hand.

Dialog boxes are great if you want to make your spreadsheet more structured and leverage off built-in functionality.

When Things Go Wrong

Let’s face it, even some of the best written (or recorded) VBA code may encounter a situation you didn’t expect or plan for.

Maybe your code tried to open a file that doesn’t exist.

Maybe your code tries to copy data to a worksheet which has been renamed.

When things go wrong, your VBA macro will encounter a runtime error. That is, an unexpected – and unhandled – error which stops the code running.

You can't handle errors in a recorded macro.

In contrast, you can write error handling code to manage how an error should be managed.

There’s two primary ways of doing this.

Firstly, you can write defensive code. That is, code which tries to anticipate where problems may occur and pre-empts the problem.

One example is checking to confirm a file exists before trying to open it. If the file does not exist, you can display a message to your user or exit gracefully without your macro crashing.

Secondly – when all else fails – you can “trap” the error. VBA provides the ability to trap unexpected errors giving you a second chance to deal with them.

This may simply mean you:

• record the details of the error to a log
• display a message to your user letting them know something went wrong
• exit gracefully

As an aside, if you want to create macros that other people will use – perhaps other people you work with – having well behaved code will make you look far more professional than code which crashes without warning.

Also, as people never (okay, rarely) read error messages, take the time to save the details of the error somewhere (like an error log) to help you figure out what went wrong and how to fix it.

Nowadays, all my VBA projects have detailed error logging which records all the details down to the offending line number.

I can’t tell you how many times this has saved my bacon.

What Have We Missed?

To be sure, Excel’s macro recorder is a great tool.

You can use it as part of your learning VBA journey or simply to check the syntax of some code.

But the macro recorder it far from perfect and by no means anyway to create professional, robust VBA macros.

While I’ve tried to make this comprehensive, it’s by no means exhaustive.

What else can’t the macro recorder do? Share it with everyone I the comments below.