How to Spot Recorded Macros in Your Spreadsheet

VBA is very powerful, but when not properly programmed they can also pose a risk. Recorder macros in particular are unreliable and highly context-specific. In this video we’ll explain to you how to find them with the PerfectXL Add-in for Excel.

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.

Excel Add-in :: Spotting Recorded Macros :: PerfectXL Spreadsheet Validation Tool

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.

General Statistics :: Spotting Recorded Macros :: PerfectXL Spreadsheet Validation Tool

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

All Structure Issues :: Spotting Recorded Macros :: PerfectXL Spreadsheet Validation Tool

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.

Text & narration: Jan Karel Pieterse, Microsoft Excel MVP since 2002, JKP Application Development Services

More Excel Add-in Video Tutorials

How to Unhide/Rehide Cells, Columns & Worksheets with the PerfectXL Add-in

Unhide and Rehide Hidden Worksheets, Rows & Columns

Hiding sheets, rows and columns can be a neat solution to present the spreadsheet to an end-user, but very annoying to the spreadsheet developer. In this video we’ll show you how you can easily unhide and rehide hidden worksheets in Excel with the PerfectXL add-in.

Learn how to unhide and rehide hidden elements

How to find circular references in your spreadsheet :: Tutorial :: PerfectXL Add-in

Find (Hidden) Circular References in your Spreadsheet

(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references. In this video we’ll show you how PerfectXL detects any circular reference.

Learn how to find circular references in your spreadsheet