Cat On A Spreadsheet

Cat On A Spreadsheet

Advanced Error Handling in VBA: Centralized Logging and Email Alerts

When building large Excel VBA projects, the way you handle errors can make or break your solution. If errors go unnoticed or leave no trace, debugging becomes guesswork. Advanced projects need more than a quick On Error Resume Next — they need error tracing, logging, and reporting. In this post, we’ll build a reusable ErrorHandler module that:

  • Traces procedures with a stack.

  • Collects all errors in memory during a run.

  • At the end of execution, creates a single timestamped log file.

  • Emails the log file automatically to the developer for review.

  • Does nothing if no errors occur.

 

This makes error handling predictable, auditable, and automated.

 

The ErrorHandler Module

 

Here’s a ready-to-use module. It has three main parts:

  • Procedure tracing (EnterProc and ExitProc)

  • Error capturing (LogError)

  • Log flushing + email (FlushErrors)

'============================
' Module: ErrorHandler
'============================
Option Explicit

Private stackTrace As Collection
Private errorBuffer As Collection
Private Const DEV_EMAIL As String = "developer@company.com"

'--- Initialize stack trace ---
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

Private 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

'--- Capture error details into buffer ---
Public Sub LogError(ByVal procName As String, ByVal lineNum As Long)
     If errorBuffer Is Nothing Then Set errorBuffer = New Collection

     Dim logLine As String
     logLine = Now & " | " & procName & " | Line " & lineNum & " | Err " & Err.Number & ": " & Err.Description
     logLine = logLine & " | User=" & Environ("Username") & " | PC=" & Environ("ComputerName")
     logLine = logLine & " | Stack=" & GetStackTrace()

     errorBuffer.Add logLine
End Sub

'--- At the end of run, flush errors into a file and send email ---
Public Sub FlushErrors()
     If errorBuffer Is Nothing Then Exit Sub
     If errorBuffer.Count = 0 Then Exit Sub

     Dim logPath As String
     Dim fNum As Integer
     Dim entry As Variant

     logPath = ThisWorkbook.Path & "\ErrorLogFile " & Format(Now, "yyyy-mm-dd hh-nn-ss") & ".txt"

     ' Create and write log
     fNum = FreeFile()
     Open logPath For Output As #fNum
         Print #fNum, "VBA Error Log - Generated " & Now
         For Each entry In errorBuffer
             Print #fNum, entry
         Next entry
     Close #fNum

     ' Send email with log attached
     Call SendErrorEmail(logPath)

     ' Clear buffer for next run
     Set errorBuffer = Nothing
End Sub

'--- Email notification with attachment ---
Private Sub SendErrorEmail(ByVal logPath As String)
     On Error Resume Next
     Dim olApp As Object
     Dim olMail As Object

     Set olApp = CreateObject("Outlook.Application")
     Set olMail = olApp.CreateItem(0)

     With olMail
         .To = DEV_EMAIL
         .Subject = "VBA Error Report"
         .Body = "See attached error log for details."
         .Attachments.Add logPath
         .Send
     End With
End Sub

 

Usage Example

 

Wrap your procedures with structured error handling:

Sub ExampleProc()
     On Error GoTo ErrHandler
     EnterProc "ExampleProc"

     ' Your code goes here

CleanExit:
     ExitProc
     Exit Sub

ErrHandler:
     LogError "ExampleProc", Erl
     Resume CleanExit
End Sub

 

At the end of your main controlling routine, flush all collected errors:

FlushErrors

 

Why This Approach Works

  • One file per run: Keeps logs tidy and easy to match with sessions.

  • Complete context: Each error has a timestamp, user, machine, and procedure stack.

  • Automatic reporting: Developers get notified immediately with the full log.

  • Zero noise if no errors: No empty files or pointless emails.

 

Final Thoughts

 

Error-proofing large VBA projects is about more than avoiding runtime crashes. With centralized logging, stack tracing, and automated developer alerts, you’ll always know what went wrong, where, and why — without relying on users to describe the problem. This module is a strong foundation, and you can extend it further with features like severity levels, unique error IDs, or integration with bug trackers.

29 September 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet