From time to time, I need to work on more advanced Excel projects. Often, I am tasked with evaluating existing Excel files, many of which utilize extensive formulas. In many cases errors appear on the spreadsheet and are difficult to detect.
Excel VBA – Error Scan
I use the following macro to quickly find the actual cell that is causing the error. This is a very simple setup, but has worked very well when I need to quickly find formulas or cells that have any types of error messages returned.
The actual VBA code is quite simple:
For Each c In ActiveCell.CurrentRegion
If IsError(c) = True Then
MsgBox "Error Detected"
For simplicity, say you have the following table in Excel:
Simply highlight the range and run the macro “ErrorScan”
The macro will detect the error (in cell D5), then stop the macro and select the cell with the first detected error:
This comes in handy on large spreadsheets, where the actual error message does not jump out. I like to build this into many of my projects as I am writing and developing the VBA code. I call this macro as I build others, to check for errors that I do not consider looking for. You can modify this macro to meet your needs.