How to Spot Recorded Macros in Your Spreadsheet
VBA is very powerful, but when it’s not properly utilized, it can also pose a risk. Recorded 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.
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.
Text & narration: Jan Karel Pieterse, Microsoft Excel MVP since 2002, JKP Application Development Services
More Excel Add-in Video Tutorials
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 elementsFind (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