How to Spot Recorded Macros in Your Spreadsheet

Pros and Cons of Recording Macros

Most Excel users record macros to handle recurring tasks in Excel. There’s nothing wrong with recording macros. In fact, even professional programmers record macros to get the correct syntax for certain steps. But, recorded macros may be risky, because they are often inflexible and inefficient. They are inflexible because cell addresses are often hardcoded into the macro. They are inefficient because the macro recorder records everything you did, including scrolling and selecting things on the cell grid.

Checking your VBA Macros with PerfectXL

PerfectXL is capable of checking your VBA macros in order to detect potential problems in the code. When you click VBA Macros on the PerfectXL ribbon (add-in for Excel), a new pane opens up which reports the findings of the VBA code analysis.

At the top we see some general statistics about the VBA code in this file, such as the number of lines of code, the number of modules and how many recorded macros the tool has detected.

Below the general statistics a comprehensive list of all found issues is displayed.

Recorded Macros and Other Structure Issues

The third item in this example spreadsheet is the one we are talking about in this video: RecordedMacro. Note the second column, ‘Location’, it tells us where to look for the problem. In this case it says the problem is in Module 1, Line 37, Column 0.