LESSON THREE: Using The Macro Recorder

Lesson 3 Banner.jpg

What Is It?

The Record Macro feature in Excel and Word is hands down the best programming tool you could ask for.  You can visualize it as a tape recorder with 2 buttons: record and stop.  When the Record Macro functionality is set to record, the program writes the code and stores every action that you do within either Excel or Word in a VBA module.  You can then go back and look at the code that was written and do various things with it. The macro recorder may seem like a VBA beginner’s tool but even Excel gurus will use it all the time because they understand the efficiency the recorder can add.

How To Turn Your Onscreen Actions Into Code

The Record Macro feature can be accessed through the Developer tab and resides in the Code section.

Macro Recorder 1.jpg

You can also access it by clicking on the Record Macro icon in the bottom left hand corner of your window (I did not know this for the longest time!)

Macro Recorder 2.jpg

 After clicking the icon you should see the Record Macro dialog box

Macro Recorder 3.png

In this dialog box you have a couple of options:

Macro Name [Required]: The recorder will always default a name in the dialog box but you are free to change it to a more meaningful name which I always recommend.  If you forget to change the name or want to change it later, don’t worry you can always change your Macro name after you’re done recording.  The naming convention allowed has to follow the below criteria:

  • Name must start with a letter
  • No spaces are allowed (use an underscore “_” if you want to put a space between words)

Shortcut Key [Optional]:  This allows you to trigger or run the code you recorded via a keyboard shortcut.  As a word of caution this does overwrite the default Excel shortcuts so as a best practice I always use the capital letter instead of the lowercase one.  For example, instead of making my macro shortcut Ctrl + c (which of course is the universal shortcut for copy), I would use Ctrl + Shft + c or Ctrl + C.

Store Macro In [Required]: This lets you determine the location of where the recorder will store the code.  All currently open workbooks will show as options in the dropdown along with your Personal Macro file (if you have one set up)

Description [Optional]:  I don’t think I have ever used this field as I always go back to my recorded code and immediately add comments to describe what the code does; but if you want you can type in a brief description for what you are about to record and the recorder with code that for you too.

Once you have filled out the dialog box to your liking and can click OK.  Your every move is now being recorded #BigBrother!  After you are done performing whatever action(s) you want to code just it the Stop Recording button (located in the Developer tab or down in the left hand corner of your window)

Not Just For Excel

The macro recorder can also be found and used in Microsoft Word.  This feature used to be available in PowerPoint a few software iterations ago however it is believed that the Microsoft Office team unfortunately axed the feature because it was too buggy.  This was a huge mistake as even a buggy macro recorder would have been useful to users trying to teach themselves how to write VBA for PowerPoint (especially since there is not a whole lot of information on PowerPoint VBA out there on the web).  Fortunately for you, I have taken it upon myself to cover VBA for PowerPoint on this site so you won’t have to pull your hair out trying to figure it out!

 Downfalls Of The Recorder

Like most things, the Recorder does have some weaknesses.  I will attempt to list some of the ones I run into on a regular basis.

  • Some Actions Aren't Recordable -  There are some instances where I have tried to record a command and it would not get coded by the recorder.  This can get frustrating sometimes!
  • Some Programs Don't Have A Recorder - Unfortunately only Excel and Word currently have the Macro Recorder capability.  For the rest of the Office programs, you will have to teach yourself how to write the VBA code (or just read my blog) as each program has it's own unique quirks and functionality.
  • Records Scrolls & Clicks - This is probably more of an annoyance  than a downfall but when I say the Macro Recorder records everything, I mean EVERYTHING!  That means every scroll your make and every cell you click on.  This usually causes the recorded macro code  to be extremely lengthy.  I always recommend going in and deleting those extraneous lines of code as it will make your code slower, harder to read, and could potentially cause errors.

Can you think of any other downfalls to the Macro Recorder?  Leave a comment below and I will continue to update this list!