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
- Click Record Macro from the Developer Tab.
- Press OK at the Record Macro prompt. Your macro is now recording.
Recording a Macro
After starting the recorder, follow these steps to record a sample macro.
- Select cells “D1” through “F4” (D1:F4).
- Click the Fill Color symbol.
- Click Stop Recording. You’ll find this in the same spot you clicked Record Macro in Step 1 of Starting the Macro Recorder.
Viewing your Recorded Macro
Before playing your macro, let’s see what the recorder captured.
- Launch your Visual Basic Editor
- 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.
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
- Switch to “Sheet2” in Excel.
- Play your Macro
- Click Macros in your Developer Tab
- Select Macro1
- Click Run
The cells in range “D1:F4” of Sheet2 will turn yellow.
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.