VBA Stack Trace Logger Add-in: Practical Examples and Precautions

Introduction

When debugging in programming, the stack trace (function call history) is extremely important information.

If you can see which function led to another to arrive at the current point when an error occurs, it becomes much easier to identify the root cause.

However, VBA does not have a standard feature to retrieve a stack trace during "code execution."

Therefore, I created a logger add-in that makes it easy to output logs including stack traces in VBA.

Please check the following article for instructions on how to install and use the add-in basic features.

https://zenn.dev/trifolium/articles/479478033f16da

In this article, I will introduce practical use cases and precautions based on actual scenes where this logger is used.

Due to the nature of my own use cases, the content will center on error handling and debugging, but I hope you find it useful as an introduction from a practical perspective.

Logger Demo

[2025-06-22 19:30:24.076][Trace][MyModule.MainProc] >> Enter MyModule.MainProc
[2025-06-22 19:30:24.077][DEBUG][MyModule.MainProc] This is my message
[2025-06-22 19:30:24.079][Trace][MyModule.SubProc < MyModule.MainProc] >> Enter MyModule.SubProc

Use Cases

Example 1: Supporting root cause identification when error handling is not implemented

In VBA development, there is a major challenge where it becomes difficult to understand the situation when an error occurs if error handling is insufficient.

Typically, you would take countermeasures such as:

• Writing error handlers (On Error GoTo ErrorHandler)
  • Recording error details like Debug.Print Err.Number
• Writing error propagation to the caller (Err.Raise)
• Leaving logs with Debug.Print at the start and completion of procedures

However, these require significant effort, and sometimes they are postponed or only partially implemented to prioritize development speed.

Without countermeasures, you fall into a situation where there are zero clues as to when or where an error occurred.

By using the stack trace feature of this logger, you can easily record procedure call history.

Example of information obtained during an error with standard features when no error handling is present

Suppose you performed a division by zero in the procedure ProcessStepC().

When the code is executed, a dialog informing you of the error appears as follows.

If you press the Debug button, the line where the error occurred is highlighted in yellow.

---That is all the information you get.---

The critical issue is that all the above information is only displayed via the editor (VBE) GUI, and there is no text data (such as logs).

Furthermore, the amount of information is insufficient.

• At what timing did the error occur?
• In what order were the procedures called?

You cannot know this information.

---Compared to other languages, it can be said that the cost of an error occurring in VBA without countermeasures is very high.---

In many other languages, stack trace information is output as text to the console when an error occurs, ensuring a minimum level of information is available.

Usage Scenario

• Scenario

Inside a loop, multiple different processes are called depending on the item number.
Intentionally cause an error in a specific process (ProcessStepC) without any error handling.

Option Explicit
Private Const MODULE_NAME As String = "MyModule"

Sub CheckLogger()
    ' === Initialize Logger ===
    Mylogger.StartConfiguration _
        .EnableStackTrace _
        .EnableWriteToExcelSheet _
        .SetOutputExcelSheet(ActiveSheet) _
        .Build
    ' === Output Logs ===
    ProcessLargeData
    ' === Logger Termination Process ===
    Mylogger.Terminate
End Sub

Sub ProcessLargeData()
    Const PROC_NAME As String = "ProcessLargeData": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    
    Mylogger.Log "Processing started"
    Dim i As Long
    For i = 1 To 10
        Mylogger.Log "[Process Start] Item " & i

        ProcessStepA ' Process to always execute
        If i Mod 2 = 0 Then ProcessStepB ' Process to execute only for multiples of 2
        If i Mod 3 = 0 Then ProcessStepC ' Process to execute only for multiples of 3

        Mylogger.Log "[Process End] Item " & i
    Next i
    Mylogger.Log "Processing completed"
End Sub

Private Sub ProcessStepA()
    Const PROC_NAME As String = "ProcessStepA": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    ' Process
End Sub

Private Sub ProcessStepB()
    Const PROC_NAME As String = "ProcessStepB": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    ' Process
End Sub

Private Sub ProcessStepC()
    Const PROC_NAME As String = "ProcessStepC": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    ' Intentionally cause an error
    Err.Raise 513
End Sub

Obtained Logs

Log Content

[2025-06-27 23:45:41.067][Trace][MyModule.ProcessLargeData] >> Enter MyModule.ProcessLargeData
[2025-06-27 23:45:41.068][INFO][MyModule.ProcessLargeData] Processing started
[2025-06-27 23:45:41.071][INFO][MyModule.ProcessLargeData] [Process Start] Item 1
[2025-06-27 23:45:41.072][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] >> Enter MyModule.ProcessStepA
[2025-06-27 23:45:41.073][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] << Exit MyModule.ProcessStepA
[2025-06-27 23:45:41.073][INFO][MyModule.ProcessLargeData] [Process End] Item 1
[2025-06-27 23:45:41.074][INFO][MyModule.ProcessLargeData] [Process Start] Item 2
[2025-06-27 23:45:41.074][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] >> Enter MyModule.ProcessStepA
[2025-06-27 23:45:41.075][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] << Exit MyModule.ProcessStepA
[2025-06-27 23:45:41.075][Trace][MyModule.ProcessStepB < MyModule.ProcessLargeData] >> Enter MyModule.ProcessStepB
[2025-06-27 23:45:41.076][Trace][MyModule.ProcessStepB < MyModule.ProcessLargeData] << Exit MyModule.ProcessStepB
[2025-06-27 23:45:41.076][INFO][MyModule.ProcessLargeData] [Process End] Item 2
[2025-06-27 23:45:41.077][INFO][MyModule.ProcessLargeData] [Process Start] Item 3
[2025-06-27 23:45:41.077][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] >> Enter MyModule.ProcessStepA
[2025-06-27 23:45:41.078][Trace][MyModule.ProcessStepA < MyModule.ProcessLargeData] << Exit MyModule.ProcessStepA
[2025-06-27 23:45:41.079][Trace][MyModule.ProcessStepC < MyModule.ProcessLargeData] >> Enter MyModule.ProcessStepC

From these logs output as text, you can see various pieces of information that cannot be obtained through standard VBA/VBE features.

• At what timing did the error occur?

  • Since there is no [Process End] log for the 3rd item, it can be inferred that the error occurred during the 3rd process.
  • While there is a start log for ProcessStepC (>> Enter ProcessStepC), there is no exit log (<< Exit ProcessStepC), allowing us to identify that the error occurred during the execution of ProcessStepC.

• In what order were the procedures called? (Stack Trace)

  • Looking at the final line of the log where the process stopped due to an error, the call hierarchy [MyModule.ProcessStepC < MyModule.ProcessLargeData] is recorded.
  • You can trace that the error was reached as a result of the procedures being called in the sequence of ProcessLargeData -> ProcessStepC.

In this example, the benefit may seem small because the nesting is shallow. However, in complex programs with deep call nesting, this stack trace information becomes vital for identifying the cause of an error.

• Processing status up to just before the error

  • You can confirm from the logs that the processing for the 1st and 2nd items (from [Process Start] to [Process End]) was completed successfully.

  • For the 3rd item, the logs also show that ProcessStepA executed normally (>> Enter ProcessStepA, << Exit ProcessStepA).

Additionally, you can gain other useful information, such as whether the ProcessStepC where the error occurred had also been called in previous processes (items 1 and 2).

For example, if ProcessStepC was called for the first time with the 3rd item, it is highly likely that there is an issue with the logic of ProcessStepC itself.

Conversely, if it had been called before without problems and only failed with the 3rd item, you might suspect that the cause lies in the conditions at the time of calling (changes in public variables, arguments, etc.).

In this example, we can determine from the logs that it follows the former pattern.

Example 2: Enhancing Error Handling

By using this logger, you can supplement the stack trace information that is lacking in standard error handling.

Usage Scenario

Option Explicit
Private Const MODULE_NAME As String = "MyModule"

Sub CheckLogger()
    ' === Initialize Logger ===
    Mylogger.StartConfiguration _
        .EnableStackTrace _
        .EnableWriteToExcelSheet _
        .SetOutputExcelSheet(ActiveSheet) _
        .Build
    ' === Output Logs ===
    MainProc
    ' === Logger Termination Process ===
    Mylogger.Terminate
End Sub

Sub MainProc()
    Const PROC_NAME As String = "MainProc": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    On Error GoTo ErrorHandler
    
    Mylogger.Log "Processing started"
    SubProc
    Mylogger.Log "Processing completed"
    Exit Sub
    
ErrorHandler:
    Mylogger.Log "Error occurred. Number: " & Err.Number & ". Desc: " & Err.Description, LogTag_Error
End Sub

Private Sub SubProc()
    Const PROC_NAME As String = "SubProc": Dim scopeGuard As Variant: Set scopeGuard = Mylogger.UsingTracer(MODULE_NAME, PROC_NAME)
    On Error GoTo ErrorHandler
    
    ' Process where an error occurs
    Dim hoge: hoge = 1 / 0
    
ErrorHandler:
    Mylogger.Log "Error occurred. Number: " & Err.Number & ". Desc: " & Err.Description, LogTag_Error
    Err.Raise Err.Number, Err.Source, Err.Description
End Sub

Obtained Logs

In the error handler (ErrorHandler), logs are output by specifying the Error tag, such as myLogger.Log "Error description", LogTag_Error.

You can see that the error occurring in SubProc() is recorded in the log, propagated to MainProc() via Err.Raise, and recorded as an error again there.

Outputting logs to Excel makes checking errors easier.

• Colors are applied based on the tag type (e.g., Red for Error)
• You can filter the Tag column for "Error"

Stack trace information is based on the information at the point the log is output.
Please output the log before propagating the error to the caller (Err.Raise).

Full log (Collapsible)

[2025-06-28 00:44:52.011][Trace][MyModule.MainProc] >> Enter MyModule.MainProc
[2025-06-28 00:44:52.013][INFO][MyModule.MainProc] Processing started
[2025-06-28 00:44:52.014][Trace][MyModule.SubProc < MyModule.MainProc] >> Enter MyModule.SubProc
[2025-06-28 00:44:52.016][ERROR][MyModule.SubProc < MyModule.MainProc] Error occurred. Number: 11. Desc: Division by zero.
[2025-06-28 00:44:52.019][Trace][MyModule.SubProc < MyModule.MainProc] << Exit MyModule.SubProc
[2025-06-28 00:44:52.020][ERROR][MyModule.MainProc] Error occurred. Number: 11. Desc: Division by zero.
[2025-06-28 00:44:52.023][Trace][MyModule.MainProc] << Exit MyModule.MainProc

Example 3: Switching Log Output for Development/Release

While you want to check logs in detail during development, there are situations where you want to suppress or completely disable log output when handing over the macro to others (releasing it).

This can be easily achieved by combining the customization features of this logger with VBA's conditional compilation.

https://learn.microsoft.com/ja-jp/dotnet/visual-basic/programming-guide/program-structure/conditional-compilation

Configuration Example

Declare a conditional compiler constant in the module, such as #Const DEBUG_MODE = False.
Set it to True during development and False for release.

In the example below, the logger customization (initialization process) is changed using #If DEBUG_MODE ~ #End If.

Standard Module

Option Explicit
#Const DEBUG_MODE = False
Private Const MODULE_NAME As String = "MyModule"

Sub MainProc()
    ' === Initialize Logger ===
    #If DEBUG_MODE Then
        ' Enable log output when DEBUG_MODE is active (e.g., during development)
        MyLogger.StartConfiguration.Build
    #Else
        ' Example 1: Disable all log output
        MyLogger.StartConfiguration.DisableLogging.Build
        ' Example 2: Disable specific tags
        ' (Info and Trace tags are disabled = Error tags, etc., will still be output)
        MyLogger.StartConfiguration _
            .EnableTagFiltering _
            .Add(LogTag_Info) _
            .Add(LogTag_Trace) _
            .Apply _
            .Build
    #End If
End Sub

(Reference) Comparison with standard configuration methods

Normally, I think individual log outputs are often controlled as follows.
This results in many points to edit, making it redundant and poor in readability.

Standard Module

Option Explicit
#Const DEBUG_MODE = False

Sub MainProc()
    #If DEBUG_MODE Then
        Debug.Print "Some log message"
    #End If
End Sub

In this logger, by adopting a specification that customizes the logger's behavior at initialization, you only need to branch the initialization part.

There is no need to comment out log instructions like MyLogger.Log "Some log message" in the code; the logger will ignore log output commands based on the settings.

However, processing interactions with the logger originating from the log output command will still occur. Therefore, if you strictly need to reduce processing speed or memory consumption, all logger-related code should be commented out. (That said, the impact is likely minimal, so you usually shouldn't need to worry about it.)

(Misc) Some other languages have this as a standard feature

For example, in C# (assuming the editor is Visual Studio), you select "Debug" / "Release" mode during build (when converting code to an exe file).

In Debug mode, Debug.WriteLine() in the code is enabled and logs are output, but in Release mode, it is automatically disabled and no logs are output.

Also, a log method that is always output, such as Trace.WriteLine(), is provided.

I wish the officials would add such features in an update...

https://qiita.com/takutoy/items/817a9f82a430c1096ff4

https://learn.microsoft.com/ja-jp/dotnet/api/system.diagnostics.debug?view=net-9.0

C#

Debug.WriteLine("This message is only output during development");
Trace.WriteLine("This message is always output");

Example 4: Combining with other tools - Efficiency through automatic code insertion

Manually writing boilerplate code like Const PROC_NAME ... for every procedure to use the stack trace feature is time-consuming.

Work efficiency can be improved by using VBA development support tools like MZ-Tools or DIY macros to perform automatic code insertion.

MZ-Tools

This is a tool that adds various features to the VBE. You can insert boilerplate text using shortcuts.

As of June 2025, it is a paid tool ($79.95).

I have not installed this tool myself, so please refer to the official website for its actual behavior.

https://www.wordvbalab.com/code/3032/

https://www.mztools.com/index.aspx

It also seems to have a feature to automatically assign values when inserting boilerplate text.

Predefined variables are variables used in code templates, exception / error handlers, headers, etc. whose values are assigned automatically by MZ-Tools when the code template is inserted into the code file (on the contrary to user-defined variables, whose values are asked to the user through a dialog window).

Code Element Full Name ($P[CODE_ELEMENT_FULL_NAME]): the full name (with namespace or library name) of the code element where the template is being inserted. For example, "Vehicles.Car".

Code Element Name ($P[CODE_ELEMENT_NAME]): the name (without namespace) of the code element where the template is being inserted. For example, "Car".

https://www.mztools.com/v8/onlinehelp/MZTools8Help.html?predefined_variables.htm

DIY Macros (Not Recommended)

This is a method of directly editing code via macros to insert boilerplate text.

For actual code, please refer to projects like StackTraceLib_VBA.

This method involves high security risks, so I generally do not recommend it.

http://ahox.jp/prog/2025-05_excel-2016-vba-qr-code-generate-and-logging-stack-trace.html

https://github.com/ahox/StackTraceLib_VBA

For those who want more details

In StackTraceLib_VBA, VBA is used to access the VBE itself to reference and edit modules.

Example of accessing VBE

' vbaModule is assigned a CodeModule
' VBComponents is a Collection
' When executed, it prints the name of one of the modules in the project
Dim vbaModule As Variant
Set vbaModule = Application.VBE.ActiveVBProject.VBComponents(1).CodeModule
Debug.Print vbaModule

To execute Application.VBE.ActiveVBProject, you need to enable:

• Trust access to the VBA project object model

https://qiita.com/moitaro/items/03cf067afd5da02b876c

Enabling this raises the following security risks:

• The VBA project becomes accessible via VBA macros
  • This creates an environment where tampering with VBA code or information leakage is easy
  • If a macro file obtained from an external source contains malicious code, the damage will be significant

Unfortunately, this setting is applied to the entire Excel application, so permissions cannot be controlled on a per-VBA-project basis.

While it might be acceptable for personal development, it is likely to conflict with security policies in corporate use, requiring careful judgment.

Precautions

Operating Environment

The author's development environment is as follows:

• Windows 11 64 bit
• Excel (Microsoft Office Home and Business 2019, Ver. 2503, Build 18623.20178)
• VBA (Ver. 7.1, Build 7.1.1146, Forms3 16.0.18623.20076)

Operational testing has not been performed in other environments.
While it should work if the VBA version is the same (Ver 7), I cannot guarantee its operation.

Additionally, use in environments other than Excel macros is not intended.

Intended Use Cases and Unsuitable Use Cases

This logger is intended for large-scale development where you want to obtain detailed information based on logs.

In other words, it is meant for programs where standard and class modules are intricately combined, where you integrate this logger's stack trace feature into each function to visualize complex processes through logs.

In the following cases, choosing other tools may be "easier to write" or have a "lower processing load":

• Small-scale development
  • Manually embedding Debug.Print is sufficient.
• Wanting to see stack trace information for only a specific point
  • Debug.Print, pseudo-stack trace through error propagation *1
  • CallByName wrapper method *2
• Execution speed is the top priority
  • (Prerequisite) Keep in mind that log output itself becomes a processing load.
  • Use minimal Debug.Print.
  • This logger + ON/OFF control using conditional compilation *3
    • Retrieve detailed logs during debugging and disable or reduce log output for release.
• New introduction to legacy code (existing code)
  • Automatic bulk code insertion using StackTraceLib_VBA, etc., is easier *4
    • Considering security risks, it should only be used for the initial introduction, and subsequently replaced with other methods through refactoring.
• Asynchronous processing *5
  • Debug.Print, pseudo-stack trace through error propagation.
  • There is a risk of unintended behavior with this logger or StackTraceLib_VBA.

*1 Regarding pseudo-stack trace through error propagation
https://stackoverflow.com/questions/127645/is-it-possible-to-retrieve-the-call-stack-programmatically-in-vb6

*2 Regarding the CallByName wrapper method
https://zenn.dev/sakanapan/articles/fc8406da72675c

*3 Regarding conditional compilation
Please refer to the methods introduced in "Processing Load and Usage Methods to Avoid" and "Example 3: Switching Log Output for Development/Release."

https://learn.microsoft.com/ja-jp/office/vba/language/concepts/getting-started/understanding-conditional-compilation

*4 Regarding StackTraceLib_VBA
http://ahox.jp/prog/2025-05_excel-2016-vba-qr-code-generate-and-logging-stack-trace.html

https://github.com/ahox/StackTraceLib_VBA

*5 Regarding asynchronous processing in Excel VBA
VBA does not support asynchronous processing.
While workarounds exist, they are specialized methods, so support for them in this logger is not planned.

https://learn.microsoft.com/ja-jp/previous-versions/office/troubleshoot/office-developer/asynchronous-programming-to-object-model

https://stackoverflow.com/questions/24574884/excel-vba-make-a-script-asynchronous

References

Please refer to another article where I have summarized various methods.

https://zenn.dev/trifolium/articles/5f91a2fd269ffd

Processing Load and Usage Methods to Avoid

The log writing process is executed every time.

If logs are output at a high frequency, the writing process will consume more time accordingly.
The same applies to the stack trace feature, as it also involves log output.

Countermeasure 1

In the case of For loops, adjust the output frequency to once every 10 loops, for example, instead of outputting logs every iteration.

Countermeasure 2

Use conditional compilation to disable log output during production operation.

https://learn.microsoft.com/ja-jp/office/vba/language/concepts/getting-started/understanding-conditional-compilation

Processing time estimates

I measured the elapsed time when calling myLogger.Log "hi" 100 times in a For loop.
The configuration includes outputting to both an Excel sheet and the Immediate Window.

• Executed with an empty Immediate Window:

n	Time (ms)
1	105
2	105
3	104

• Executed with 200 lines already in the Immediate Window:

n	Time (ms)
1	109
2	113
3	119

In conclusion, the log output process takes approximately 1 ms.

I also checked the processing time for the stack trace feature.
Calling a procedure 50 times in a For loop took 107 ms.

*Note: Since "Enter" and "Exit" logs are automatically output for each procedure call, it results in 100 log outputs.

When using the stack trace feature, the majority of the processing time was the time spent on log output.

Log Output Volume Limits

If you want to record more than 200 lines of logs, outputting to an Excel sheet is recommended.

• Excel sheet: 1,048,576 rows
• Immediate Window: 200 lines

The Immediate Window can only display up to 200 lines.
When the 201st line is written, the 1st line will be deleted.

https://support.microsoft.com/ja-jp/office/excel-の仕様と制限-1672b34d-7043-467e-8e27-269d656771c3

https://oirnao.blogspot.com/2011/07/blog-post_12.html

Maintenance

I am creating this for my own work, so I plan to maintain it for a while.

However, continuous maintenance cannot be guaranteed.

Since it is published under the MIT license, if updates stop, please feel free to fork it on GitHub and use it.

Bugs, Requests, and PRs

For bugs and requests, please post a comment on this article or create a GitHub Issue.

From a record management perspective, creating a GitHub Issue is preferred.

Pull Requests (PRs) on GitHub are very welcome.
Please check the following before submitting a PR:

• Link to a corresponding Issue if one exists.
  • Create an Issue if one does not exist.
• Ensure that code style and naming conventions align with the project.
  • Specify types explicitly, and use Private instead of Public where not necessary (variables, procedures, modules).
• Ensure appropriate granularity for the PR.
  • If changes address multiple Issues, split them into individual PRs per Issue.
• Perform operational tests (testing changed features is a must).
  • Currently, unit tests and CI pipelines (automated testing) have not been implemented.

Work in Progress (WIP)

At the time of publishing this article, there are many TODO items such as debugging, error handling, and document preparation.

For now, I am releasing it as a working version is complete, but there is a high possibility that bugs still remain.

Please be aware of this.