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.
- Avoid >70 types of risks
- Improvement suggestions
- Quality reports
Read more about risks in spreadsheets
On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.
Here you will find information like system requirements, release notes, and answers to most questions about our software.
VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.
Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.
Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.
Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.
(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.
Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.
The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.
Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.
Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Calculate your formulas only once!
It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!
It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.
Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.
To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.
Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.
Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.