Very Flexible, Very Problematic
VBA allows you to control every aspect of a spreadsheet. That means whenever code runs, anything could happen. Without extensive knowledge of VBA, it’s a challenge to understand what’s happening. In fact, most VBA is written by people with little or no programming experience, which can lead to very unpredictable behaviour. But even seasoned programmers will quickly discover that VBA has its downsides; before you know it, a spreadsheet becomes slow or unresponsive, and support is inconsistent between different versions of Excel.
Finding the Issues
To warn you about potential dangers in spreadsheets that are related to VBA, PerfectXL offers an extensive overview of risk detections specifically for VBA code.
A list of VBA issues can be found in PerfectXL’s web-based analysis tool as well as in the PerfectXL plugin for Excel. A description and explanation tell you what’s going on, and the location tells you which module and line of code contain the issue.
One way to introduce VBA code in Excel is to record a macro. Unfortunately, macros tend to contain notoriously bad code. They are often slow, incomprehensible and overly specific to the situation. PerfectXL Risk Eliminator finds them for you.
Did you know that Excel can run VBA code without you knowing? Simply opening a macro-enabled workbook can be a trigger to run a piece of VBA code. At best this is undesirable, at worst it is outright dangerous if the code in question is malicious.
In-depth module inspection
To aid in inspecting a specific module, you can make use of the ‘Complete modules with issues’ section. This allows you to select a module to inspect, such as a recorded macro, and find all related VBA issues right at the relevant line of code.