How the Excel Macro Recorder Works

The Excel macro recorder is a built-in engine that tracks and stores your actions in Excel; its capabilities and features must be understood so you record efficient macros.

Additionally, you will need the Macro recorder even after you learn how to write macros from scratch; I still use it to record simple macros and as an Excel VBA content provider.

So let's start …

1) The Excel recorder registers every action you perform on Excel

On your audio recording device; while the Record button is pressed, everything is recorded: your voice and the background noise. On Excel; the same thing happens; while it is in recording mode every action is recorded.

These actions may be: selecting a cell, writing a formula, scrolling, formatting a cell, zooming, etc.

And every action is written on a line per line basis.

Take into account that the same way a voice recording captures your predictions and background noise, the macro recorder captures everything you do, including your mistakes and incidental actions.

2) The Excel recorder generates a VBA instruction after the given action has been performed on Excel

When you record your voice, you need to talk first so the recording device captures your speech; the same thing happens with Excel Macros.

You need to perform an action on Excel (see left window below) so the Excel recorder registers it.

This is one of the main shortcomings to develop smart applications. A Macro does not have flow control, it starts and ends. It has no anticipation, no decision.

The unique way to build smart applications is by improving a macro or by writing it from scratch.

3) The Excel recorder changes the "properties" of Excel objects programmatically

At first sight the produced VBA language is Greek; nothing more far from truth.

Decode the VBA language grammar quickly by reading a short introduction to objects …

On the real world, your hair has properties: length, color, type; you can cut it, change its shape, etc. Your hair behaves in specific ways: it grows, it falls, etc.

Some properties can be changed, like the color. Yes you can apply a cosmetic treatment and turn your black hair into red, blond, etc.

Some properties are read-only, you can know them but you can not change them. For example: your hair type: curly, straight.

Let's summarize the features of your hair …

  • A hair Property is what you hair is: length, color, etc.
  • A hair Method is what you can do with your hair: cut it, change its shape
  • A hair Event is how it behaves: it grows, it falls, etc.

Now back to Excel …

Excel has properties and methods too, and you can manipulate them by using VBA language.

You are fully aware of the way you change properties of Excel objects, you know how to change the color of a cell, the font, the name of a sheet, etc. But you are not quite aware of how to change them programmatically; a Macro preforms that very easily.

And finally …

4) The Excel recorder uses the "methods" of Excel Objects programmatically

The same way you change the properties, you use the methods of Excel objects when you cut a cell, delete a sheet, open a workbook, etc.

Source