Cat On A Spreadsheet

Advanced Error Tracing and Correction in VBA: Beyond Basic Logging

In the previous post, we built a reusable framework to capture and log VBA errors automatically. That’s a huge step forward for debugging complex projects. But there’s more we can do. Advanced VBA developers can take error handling to the next level with precise error tracing, call stack emulation, smarter logging, and even automated correction strategies. This post explores those techniques in detail.

Adding Line Numbers with Erl

The built-in Erl function returns the last executed line number if your code is numbered. By default, VBA doesn’t show line numbers — but you can add them manually (or with a utility).

Example:

  Sub CalculateValues()
 
 10     On Error GoTo ErrorHandler
 
 20     Dim x As Double, y As Double, z As Double
 
 30     x = 10
 
 40     y = 0
 
 50     z = x / y ' Division by zero
 
 ExitHere:
 
 60     Exit Sub
 
 ErrorHandler:
 
 70     Call LogErrorWithLine("CalculateValues", Erl)
 
 80     Resume ExitHere
 
 End Sub

And here’s the modified logger:

  Public Sub LogErrorWithLine(ByVal procName As String, ByVal lineNum As Long)
 
 Dim ws As Worksheet
 
 Dim nextRow As Long
 
 Set ws = ThisWorkbook.Sheets("ErrorLog")
 
 nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
 
 ws.Cells(nextRow, 1).Value = Now
 
 ws.Cells(nextRow, 2).Value = procName
 
 ws.Cells(nextRow, 3).Value = lineNum
 
 ws.Cells(nextRow, 4).Value = Err.Number
 
 ws.Cells(nextRow, 5).Value = Err.Description
 
 End Sub

Now your log will tell you exactly where in the procedure the error occurred.

Building a Manual Call Stack

VBA doesn’t provide a built-in call stack (like higher-level languages do), but we can emulate one by manually tracking entry and exit points.

Example utility module:

  Private stackTrace As Collection
 
 Public Sub EnterProc(ByVal procName As String)
 
      If stackTrace Is Nothing Then Set stackTrace = New Collection
 
      stackTrace.Add procName
 
 End Sub
 
 Public Sub ExitProc()
 
 If Not stackTrace Is Nothing Then
 
      If stackTrace.Count > 0 Then stackTrace.Remove stackTrace.Count
 
 End If
 
 End Sub
 
 Public Function GetStackTrace() As String
 
 Dim item As Variant
 
 Dim result As String
 
 For Each item In stackTrace
 
      result = result & item & " > "
 
 Next item
 
 If Len(result) > 0 Then result = Left(result, Len(result) - 3)
 
 GetStackTrace = result
 
 End Function

Usage in a procedure:

  Sub ProcessData()
 
 On Error GoTo ErrorHandler
 
 EnterProc "ProcessData"
 
 ' Code that might error
 
 Call SubRoutineA
 
 ExitHere:
 
 ExitProc
 
 Exit Sub
 
 ErrorHandler:
 
 Call LogErrorWithStack("ProcessData", Erl, GetStackTrace())
 
 Resume ExitHere
 
 End Sub

This lets you see not only where an error happened, but how the code got there.

Selective Error Trapping (Handling Expected Errors)

Not all errors are bugs. Some are expected conditions that can be handled gracefully. For example, trying to open a workbook that may not exist:

  On Error Resume Next
 
 Set wb = Workbooks.Open(path)
 
 If wb Is Nothing Then
 
      ' Expected error, handle it
 
      Call LogMessage("Workbook not found: " & path)
 
 Else
 
      On Error GoTo ErrorHandler ' Reinstate full error handling
 
 End If

Use this approach to distinguish between errors you expect and ones you don’t.

Automatic Correction Strategies

Sometimes you don’t just want to log an error — you want your code to fix itself if possible.

Examples:

If a required worksheet is missing, create it.
If a file path is invalid, prompt the user to browse.
If a reference is broken, re-link it automatically.

Example:

  On Error Resume Next
 
 Set ws = ThisWorkbook.Sheets("Data")
 
 On Error GoTo ErrorHandler
 
 If ws Is Nothing Then
 
      Set ws = ThisWorkbook.Sheets.Add
 
      ws.Name = "Data"
 
      Call LogMessage("Created missing sheet: Data")
 
 End If

Here, instead of crashing, the macro auto-corrects by creating the missing sheet.

Best Practices for Production-Grade Error Handling

Always log unexpected errors (with time, user, procedure, and line number).
Use Erl liberally in production code — it’s ugly but invaluable.
Differentiate expected vs unexpected errors (don’t over-log).
Include environmental details (Excel version, machine, user).
Fail gracefully — give users an informative but non-technical message.
Optionally notify developers (email critical failures).

Final Thoughts

VBA error handling often gets dismissed as clunky compared to modern languages — but with a little work, you can build a system that gives you:

Precise error location (Erl)
A call stack to trace execution
Smart distinction between expected/unexpected errors
Automatic correction where possible
A robust log for post-mortem debugging

This elevates VBA projects from fragile scripts to maintainable, production-quality software.