How to Create a VBA Log Sheet for Errors

 

Error handling with On Error GoTo is a great start for managing runtime errors for single users in Excel. In a multi-user environment (e.g., a shared workbook on a network drive), tracking errors across users requires a centralized logging mechanism. If “On Error GoTo” is the seatbelt, an error log is the dashcam. In a shared workbook, people will run macros on different PCs, with different permissions, paths, versions of Office, and add-ins—so when something breaks, you need evidence: what macro ran, where it crashed, who ran it, and what the error was.

In this tutorial, we show how to create a VBA log sheet for errors.

Let’s create a hidden worksheet to log errors like macro crashes, including details such as timestamp, user, error description, and the macro involved. This approach helps debug issues without disrupting users.

Step 1: Create and Hide the Log Sheet

We need a special worksheet to write the errors. We’ll create it automatically with column headers and then hide it very well so normal users don’t see it or accidentally change it.

You need to hide it so users won’t get curious, delete rows, or filter the data, which could break future logging or expose sensitive info.

• Open your Excel workbook
• Go to the Developer tab >> select Visual Basic
• From the Insert menu >> select Module

• To create the log sheet programmatically (so it’s consistent), add this code to your module. This ensures the sheet exists and is set up with headers.

Sub CreateErrorLogSheet()
    Dim ws As Worksheet
    Dim sheetName As String
    sheetName = "ErrorLog"  ' Change if needed, but keep it simple
    
    ' Check if sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = sheetName
    End If
    
    ' Add headers if not present
    If ws.Range("A1").Value = "" Then
        ws.Range("A1").Value = "Timestamp"
        ws.Range("B1").Value = "User"
        ws.Range("C1").Value = "Macro Name"
        ws.Range("D1").Value = "Error Number"
        ws.Range("E1").Value = "Error Description"
        ws.Range("F1").Value = "Additional Info"
        
        ' Format headers
        ws.Range("A1:F1").Font.Bold = True
        ws.Columns("A:F").AutoFit
    End If
    
    ' Hide the sheet
    ws.Visible = xlSheetVeryHidden  ' Use xlSheetVeryHidden to prevent easy unhiding via UI
    
    ' Protect the workbook structure to prevent unhiding (optional)
    ThisWorkbook.Protect Structure:=True, Password:="yourpassword"  ' Set a password
End Sub

• Press F5 or click Run
• Run this sub once to create and hide the sheet. The xlSheetVeryHidden makes it invisible in the UI; users can only access it via VBA.
• Look in the Project Explorer—you will see a sheet called ErrorLog

• Right-click any sheet tab in Excel, and you won’t see “ErrorLog” because it’s very hidden

In a multi-user environment, run this on the master copy before sharing. If the workbook is already shared, you may need to unshare it temporarily.

• Go to the Review tab >> select Share Workbook >> uncheck “Allow changes by more than one user”

Step 2: Build a Logging Function

Create a reusable function to append error details to the log sheet. This keeps your code clean. This small routine writes one line to the hidden sheet every time an error happens. We’ll call this function from every important macro.

• Add the following code to a standard module
• Click Insert >> select Module

Sub LogError(macroName As String, errNum As Long, errDesc As String, _
             Optional addInfo As String = "")
    On Error Resume Next  ' Logging should never interrupt user workflows
    
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim userName As String
    
    ' Get Windows username (helps identify WHO had the problem)
    userName = Environ("USERNAME")
    
    ' Point to our hidden log sheet
    Set ws = ThisWorkbook.Sheets("ErrorLog")
    If ws Is Nothing Then Exit Sub  ' Safety check if sheet was removed
    
    ' Find the next empty row
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Write the error details
    With ws
        .Cells(nextRow, "A").Value = Now               ' date + time
        .Cells(nextRow, "B").Value = userName
        .Cells(nextRow, "C").Value = macroName
        .Cells(nextRow, "D").Value = errNum
        .Cells(nextRow, "E").Value = errDesc
        .Cells(nextRow, "F").Value = addInfo
        
        ' Keep columns readable
        .Columns("A:F").AutoFit
    End With
    
    ' Very important in shared files—save immediately
    ThisWorkbook.Save
End Sub

• Why Environ(“USERNAME”)? It captures the Windows username, useful for identifying who triggered the error in a multi-user setup.
• Saving the workbook: The ThisWorkbook.Save line helps make sure the log is written even if many people are using the file at the same time. In shared mode, Excel handles concurrent saves, but if conflicts arise, consider logging to a text file instead.

Step 3: Integrate Error Handling in Your Macros

For any macro where you want to log errors, modify your On Error GoTo to call the logging sub.

Suppose you have a macro called MyMacro that might crash.

• Insert a Module and copy-paste the following code

Sub MyMacro()
    On Error GoTo ErrorHandler
    
    ' Your code here, e.g.:
    Dim x As Integer
    x = 1 / 0  ' Intentional error for testing
    
    Exit Sub
    
ErrorHandler:
    ' Log the error
    LogError "MyMacro", Err.Number, Err.Description, "Line: " & Erl  ' Erl gives line number if you number lines
    
    ' Optional: Show a user-friendly message
    MsgBox "An error occurred. Details logged.", vbExclamation
    
    ' Resume or exit as needed
    Resume Next  ' Or Exit Sub
End Sub

• Repeat this pattern in every macro you want to monitor
• For line numbers (via Erl), manually number your code lines like 10: x = 1 / 0

Sub MyMacro()
    On Error GoTo ErrorHandler
    
10  Dim x As Integer
20  x = 1                  ' ← starts here
30  x = x / 0             ' ← error happens here → Erl will return 30
40  MsgBox "Result: " & x

' ...rest of the routine (ErrorHandler) as shown above...

• Click the Run icon to run MyMacro; it should log the division-by-zero error

• It will show a message box stating that an error occurred

Step 4: Check the Error Log

• Unprotect the workbook if protected:
  • Go to the Review tab >> select Protect Workbook >> enter your password >> click OK
  • If no password was set → it will just unprotect immediately

• Open the VBA Editor, press Alt + F11
• Press Ctrl+G to open the Immediate Window
• Type the following code and press Enter:

Sheets("ErrorLog").Visible = True

• Go back to Excel—you’ll now see the ErrorLog sheet
• Review the rows

• Re-protect the workbook structure
  • Go to the Review tab >> select Protect Workbook >> set a password
  • Click OK
• When you are done, hide it again

Sheets("ErrorLog").Visible = xlSheetVeryHidden

Now users can’t accidentally unhide sheets or rename them, but your VBA logging still works (because logging only writes data to cells; it doesn’t change visibility).

• Add this helper sub if you want one-click viewing:

Sub ShowErrorLog()
    ThisWorkbook.Unprotect "yourpassword"           ' remove if not protecting
    Sheets("ErrorLog").Visible = True
    Sheets("ErrorLog").Activate
End Sub

Sub HideErrorLog()
    Sheets("ErrorLog").Visible = xlSheetVeryHidden
    'ThisWorkbook.Protect Structure:=True, Password:="yourpassword"
End Sub

• Add Periodic Cleanup:

Sub ClearErrorLog()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ErrorLog")
    ws.Rows("2:" & ws.Rows.Count).ClearContents  ' Keep headers
    ThisWorkbook.Save
End Sub

• Run this when the log gets too large

Considerations for Multi-User Environments

Concurrency: If multiple users run macros simultaneously, Excel’s shared mode queues saves, but heavy usage might cause delays or conflicts.

Alternative: Log to an external text file on a shared drive.

Example logging to text file (replace LogError body):

Dim logPath As String
logPath = "\\network\share\error_log.txt"  ' Shared network path
Open logPath For Append As #1
Print #1, Now() & vbTab & userName & vbTab & macroName & vbTab & errNum & vbTab & errDesc & vbTab & addInfo
Close #1

• Security: The hidden sheet is not foolproof; power users can unhide via VBA. For sensitive logs, use the text file approach or encrypt the workbook.
• Performance: Logging adds slight overhead; only log critical errors.
• Sharing Setup: Enable sharing via Review >> Share Workbook. Test logging with multiple users.
• Debugging Tip: Add Application.EnableEvents = False before writing to prevent event triggers during logging, and restore it afterward.

Troubleshooting Common Issues

• Log sheet gets deleted accidentally: Use xlSheetVeryHidden and protect with a password.
• Log file grows too large: Implement auto-cleanup or periodic cleanup on workbook open.
• Errors occur in the logging code itself: Always use On Error Resume Next in logging functions.
• Can’t view logs: Create a dedicated admin button that calls ShowErrorLog.
• Logs don’t capture enough detail: Always pass contextual information in the addInfo parameter.

Conclusion

By following the above steps, you can create a VBA log sheet for errors. This setup provides a robust, hidden log for tracking errors without user interruption. Your error logging system will capture macro errors automatically, track user and system information, and provide detailed context for debugging—while remaining hidden from regular users. You can customize columns or add more details (e.g., workbook path) as needed. If you encounter issues, test in a single-user mode first. Remember to review your logs regularly and use the insights to improve your VBA applications over time.