Starting the Macro Recorder | Recording | Viewing | Testing

The macro recorder is a handy tool that records what you do in Excel. This tutorial will guide you through the process of recording a macro.

Before we begin, it’s important to point out the macro recorder isn’t perfect. It can’t record everything you do in Excel. For instance, if you use the “Text Import Wizard” to paste a block of text in fixed width format, the macro recorder fails. It will capture the paste, but it won’t record the desired format. There are ways around this, but that’s for a future lesson.

Now that you understand the limitations, let’s begin.

Starting the Macro Recorder

  1. Click Record Macro from the Developer Tab.
    Record macro

  2. Press OK at the Record Macro prompt. Your macro is now recording.
    Record macro launch screen

Recording a Macro

After starting the recorder, follow these steps to record a sample macro.

  1. Select cells “D1” through “F4” (D1:F4).
  2. Click the Fill Color symbol. Excel Fill Color

  3. Click Stop Recording. You’ll find this in the same spot you clicked Record Macro in Step 1 of Starting the Macro Recorder. Stop Recording Macro

Viewing your Recorded Macro

Before playing your macro, let’s see what the recorder captured.

  1. Launch your Visual Basic Editor
  2. If you’ve been with me since the start of the Chapter, click Module2 in your Project Explorer. If you just joined me, you’ll likely need to click Module1.
    View recorded macro

You can see where the macro recorded you selecting a range of cells and then captured the steps required to change the background color of the range.

Testing your Recorded Macro

  1. Switch to “Sheet2” in Excel.
    Select Sheet2
  2. Play your Macro
    1. Click Macros in your Developer Tab
    2. Select Macro1
    3. Click Run
      Run recorded macro

The cells in range “D1:F4” of Sheet2 will turn yellow.
Play your recorded macro

You now know how to start recording a macro, stop recording a macro and play your recorded macros. In addition, you learned the Excel Macro Recorder is not perfect and I introduced you to one such limitation. You are well on your way to becoming a VBA expert.