Cat On A Spreadsheet

Automating Error Logging in VBA: Building a Robust Debugging Framework

When you’re working on small macros, a simple MsgBox error handler might be enough. But as your projects grow in size and complexity, error handling needs to evolve too. For large VBA projects, it’s not just about stopping errors gracefully — it’s about capturing useful debugging information automatically, so you can fix issues quickly without relying on users to describe what went wrong.

In this post, we’ll walk through building an error logging framework that writes useful details to a central location, such as a dedicated worksheet or a text file.

Why Error Logging Matters

Imagine you’ve distributed your VBA project to 50 users. One day, a user runs into an issue. Instead of getting a vague “Runtime Error 91,” your error logger could provide:

The exact procedure where the error occurred
The error number and description
The time and date
Optionally, the username and machine name

This transforms debugging from guesswork into a straightforward process.

Step 1: A Basic Error Handler

At its simplest, you can wrap procedures with an error handler:

  Sub ExampleMacro()
 
 On Error GoTo ErrorHandler
 
 ' Example code that may cause an error
 
 Dim x As Integer
 
 x = 10 / 0 ' Division by zero
 
 ExitHere:
 
 Exit Sub
 
 ErrorHandler:
 
 MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
 
 Resume ExitHere
 
 End Sub

This works, but it only shows an error message to the user. What we really want is a reusable system that logs these details automatically.

Step 2: Centralized Error Logging Procedure

Instead of writing error-handling code in every macro, create a centralized subroutine that records error details:

  Public Sub LogError(ByVal procName As String)
 
      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 = Err.Number
 
      ws.Cells(nextRow, 4).Value = Err.Description
 
      ws.Cells(nextRow, 5).Value = Environ("Username")
 
      ws.Cells(nextRow, 6).Value = Environ("ComputerName")
 
 End Sub

This routine assumes you have a worksheet named ErrorLog with columns for:

Timestamp
Procedure Name
Error Number
Error Description
User Name
Machine Name

Step 3: Using the Logger in Your Procedures

Now, instead of writing custom error handling in every macro, you can reuse the logger:

  Sub ImportData()
 
      On Error GoTo ErrorHandler
 
      ' Simulated code
 
      Dim wb As Workbook
 
      Set wb = Workbooks.Open("C:\nonexistent.xlsx")
 
 ExitHere:
 
      Exit Sub
 
 ErrorHandler:
 
      Call LogError("ImportData")
 
      Resume ExitHere
 
 End Sub

If the file doesn’t exist, the error gets recorded in your ErrorLog sheet automatically, complete with time, user, and details.

Step 4: Logging to a Text File

For larger teams, it can make sense to log to a text file instead of cluttering the workbook. Here’s a version that appends error logs to a .txt file:

  Public Sub LogErrorToFile(ByVal procName As String)
 
      Dim fNum As Integer
 
      Dim logLine As String
 
      Dim logPath As String
 
      logPath = ThisWorkbook.Path & "\ErrorLog.txt"
 
      fNum = FreeFile()
 
      logLine = Now & " | " & procName & " | " & Err.Number & " | " & Err.Description
 
      Open logPath For Append As #fNum
 
          Print #fNum, logLine
 
      Close #fNum
 
 End Sub

This version creates (or appends to) a file called ErrorLog.txt in the workbook’s folder.

Step 5: Enhancements for a Production-Ready Framework

You can extend this error-logging approach by adding:

Line numbers with the Erl function to pinpoint where the error occurred
Stack tracing (manually passing procedure names down the call chain)
Email notifications when critical errors occur
Configurable logging levels (e.g., only log critical vs log everything)

Final Thoughts

By automating error logging, you shift from reactive debugging to proactive error management. Instead of frustrated users reporting vague issues, you’ll have a clear, timestamped history of what went wrong, where, and for whom. This framework also helps you spot patterns — if many users encounter the same issue, you’ll know exactly where to focus your fixes.