Class Modules

clsLog: A VBA Logging Framework

Introducing clsLog, the no-frills logging framework. Prepare to supercharge your Access logging with the closest thing to NLog or Log4j in the VBA world.

Mike Wolfe

• 10 min read
clsLog: A VBA Logging Framework

Longtime readers will know that I'm a big fan of learning from other programming languages and incorporating those concepts into VBA:

Learning From Other Languages
Even if you only write code professionally in a single language, learning the concepts and idioms of other languages will make you a better programmer.
No Longer SetMike Wolfe

In today's article, we'll be bringing in a concept I originally picked up from Python and later rediscovered when I was working in .NET: a logging framework (such as Python logging or NLog).

If you're not familiar with what a logging framework is, check out the end of my article on 3 Methods of Logging in VBA:

3 Methods of Logging in VBA
Let’s explore the various ways to log information in our Microsoft Access applications.
No Longer SetMike Wolfe

Three Parts of a Logging Framework

A logging framework has three basic parts:

  1. A logging class that raises events
  2. One or more "logger" classes with event handlers
  3. One or more instances of the logging class and associated logger(s)

Part 1: The Logging Class: clsLog

Let's jump right into the code.

Copy the VBA below into a new class module and name it clsLog:

'---------------------------------------------------------------------------------------
' Module    : clsLog
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/clslog/
' Date      : 7/21/2017 - 7/19/2024
' Purpose   : A VBA logging framework.
' Usage     : In Main application module:
'
'        Private mLog As clsLog
'        Private mTextFileLogger As loggerTextFile
'        Private mMsgBoxLogger As loggerMsgBox
'
'        Public Function Log() As clsLog
'            If mLog Is Nothing Then Set mLog = New clsLog
'            Set Log = mLog
'
'            'Initialize loggers and set respective log levels
'            If mTextFileLogger Is Nothing Then
'                Set mTextFileLogger = New loggerTextFile
'                If App.IsDev Then
'                    mTextFileLogger.Init Log, ll_Trace
'                Else
'                    mTextFileLogger.Init Log, ll_Info
'                End If
'            End If
'            If mMsgBoxLogger Is Nothing Then
'                Set mMsgBoxLogger = New loggerMsgBox
'                mMsgBoxLogger.Init Log, ll_Warn
'            End If
'
'        End Function
'---------------------------------------------------------------------------------------

Option Compare Database
Option Explicit

'https://nolongerset.com/raising-custom-events-in-vba/
Public Event LogEntry(Msg As String, Level As ll__LogLevel, Dict As Variant, LevelTxt As String)

'https://nolongerset.com/enum-type-naming-convention/
Public Enum ll__LogLevel
    ll_All
    ll_Trace
    ll_Debug
    ll_Info
    ll_Warn
    ll_Error
    ll_Fatal
    ll_None
End Enum

Public Sub Entry(LogLevel As ll__LogLevel, Msg As String, Optional Dict As Variant)
    On Error GoTo Err_Entry
    
    Dim DictToPass As Object
    If IsMissing(Dict) Then
        Set DictToPass = Nothing
    ElseIf TypeName(Dict) <> "Dictionary" Then
        Set DictToPass = Nothing
    Else
        Set DictToPass = Dict
    End If
    
    RaiseEvent LogEntry(Msg, LogLevel, DictToPass, LogLevelAsString(LogLevel))

Exit_Entry:
    Exit Sub
Err_Entry:
    Debug.Print "ERROR [" & Err.Number & "] in clsLog.Entry(): " & Err.Description
End Sub

Public Sub Trace(Msg As String, Optional Dict As Variant)
    Entry ll_Trace, Msg, Dict
End Sub
Public Sub Dbug(Msg As String, Optional Dict As Variant)
    Entry ll_Debug, Msg, Dict
End Sub
Public Sub Info(Msg As String, Optional Dict As Variant)
    Entry ll_Info, Msg, Dict
End Sub
Public Sub Warn(Msg As String, Optional Dict As Variant)
    Entry ll_Warn, Msg, Dict
End Sub
Public Sub Error(Msg As String, Optional Dict As Variant)
    Entry ll_Error, Msg, Dict
End Sub
Public Sub Fatal(Msg As String, Optional Dict As Variant)
    Entry ll_Fatal, Msg, Dict
End Sub

'https://nolongerset.com/enum-tostring/
Private Function LogLevelAsString(LogLevel) As String
    Select Case LogLevel
    Case ll_All: LogLevelAsString = "ALL"
    Case ll_Trace: LogLevelAsString = "TRACE"
    Case ll_Debug: LogLevelAsString = "DEBUG"
    Case ll_Info: LogLevelAsString = "INFO"
    Case ll_Warn: LogLevelAsString = "WARN"
    Case ll_Error: LogLevelAsString = "ERROR"
    Case ll_Fatal: LogLevelAsString = "FATAL"
    Case ll_None: LogLevelAsString = "NONE"
    End Select
End Function

The two key pieces of the above code are:

  1. Public Event LogEntry(...)
  2. Public Sub Entry(...)

LogEntry()

This is the custom event that our logger classes will be handling.

Raising Custom Events in VBA
This quick tutorial will have you writing custom events in VBA in no time.
No Longer SetMike Wolfe

It includes four parameters:

  • Msg: The message we want to log
  • Level: An enum value that represents the logging level of this particular message
  • Dict: An optional dictionary of key-value pairs to support structured logging (e.g., this could be field name-value pairs for a database logger; more on this in future articles)
  • LevelTxt: Text representation of the logging level; this is a convenience so we don't have to perform the enum-to-text conversion in every logger

Entry()

This is the procedure that raises our custom LogEntry() event.

Since this is a Public Sub, it can be called directly from an instance of our logging class.  This comes in handy if you want to be able to set the logging level of a logged message at runtime for whatever reason.

However, this routine is usually indirectly called via the Public Subs defined directly below it.  I find the six levels of logging shown below to be about right, but your needs may differ.  Here's how I think about what should be logged at each level:

  • Trace: used for only the most verbose logging needs (e.g., you might call .Trace "MyVar: " & MyVar to log the value of a variable at runtime)
  • Dbug: used to log information that would aid in troubleshooting an error (NOTE: Debug is a reserved word in VBA and cannot be used as the name of a procedure)
  • Info: used to convey information about a successful process (e.g., the full path to an output file)
  • Warn: used in situations that could be a problem (e.g., before overwriting an existing file)
  • Error: an error that halts execution of a process, but not execution of the application (e.g., failing to read an Excel file because it is open in Excel)
  • Fatal: an error that requires halting execution of the application (e.g., when Access loses its connection to the backend data)

Feel free to add or remove items from the above list.  Just be sure to update all the places affected:

  1. Public Enum ll__LogLevel
  2. Public Sub {LevelName}(...)
  3. Private Function LogLevelAsString(...)
  4. Any special handling in your various "Logger" classes

The ll__LogLevel Enum

Careful readers will notice that the LogLevel enumeration includes two additional values not represented in the Public Subs listed above:

  • ll_All
  • ll_None

Those are special values intended to always occupy positions at the top and bottom of the LogLevel enumeration.  Using those special values helps make your intentions more explicit when setting up loggers, as we will see later.

Part 2: The Logger Class: loggerMsgBox

One of the nicest things about a logging framework is that you can make sweeping changes to how you log information in your application without having to make any changes to the bulk of your code.

Want to log all your messages to a text file? Create a text file logger class.

Want to log all your errors to a database? Create a database logger class.

Want to show informational messages in message boxes? Create a message box logger class.

The best part? You don't have to choose between a text file and a database.  You can log the same message to both destinations.

I plan on dedicating future articles to individual logger classes.  But to whet your appetite and provide you a template to make your own logger classes, let's start with a logger that "logs" messages to the MsgBox function.

Copy the VBA below into a new class module named loggerMsgBox:

'class module named: loggerMsgBox

Option Compare Database
Option Explicit

Private WithEvents mLog As clsLog

Public Level As ll__LogLevel

Private Sub Class_Initialize()
    'Set defaults
    Level = ll_None
End Sub

Public Sub Init(LogToSink As clsLog, DefaultLevelToLog As ll__LogLevel)
    Set mLog = LogToSink
    Me.Level = DefaultLevelToLog
End Sub

Private Sub mLog_LogEntry(Msg As String, Level As ll__LogLevel, Dict As Variant, LevelTxt As String)
    'Don't log errors less than the level of the current logger instance
    If Level < Me.Level Then Exit Sub

    LogMessage Level, Msg, LevelTxt
End Sub

Private Sub LogMessage(Level As ll__LogLevel, Msg As String, LevelTxt As String)
    Dim Buttons As VbMsgBoxStyle
    Select Case Level
        Case ll_Fatal, ll_Error: Buttons = vbCritical
        Case ll_Warn: Buttons = vbExclamation
        Case ll_Info: Buttons = vbInformation
    End Select
    
    MsgBox Msg, Buttons, LevelTxt & " Message"
    
End Sub

Requirements

Every "logger" class that you create must include four things:

  1. This line in the class header: Private WithEvents mLog As clsLog
  2. A module-level log level variable: Public Level As ll__LogLevel
  3. This event handler: Private Sub mLog_LogEntry(...)
  4. An initialization routine: Public Sub Init(...)

The WithEvents Line

This line of code declares a variable to hold a reference to an instance of our logging class, clsLog.  More importantly, the WithEvents keyword tells VBA that this class module will provide "handlers" for all the events in the clsLog class.

After you add that line of code, the dropdown box at the top left of the VBA IDE window will be populated with the name of the class variable, mLog.

After you select mLog from the left dropdown box, the right dropdown box will populate with all the events from the clsLog class, namely LogEntry.

The Logging Level Variable

This variable sets the logging level for an instance of the logger class.

In most cases, this gets set once at application startup.  Then, every time a message gets logged, our logger checks to see if the logged entry has a higher or lower priority than the logger's class level.  The Me.Level variable and the LogEntry logging level determine whether or not a particular message gets logged.  

If we set our logger to log only info-level messages (Me.Level = ll_Info), then logging calls to lower-priority levels (such as .Trace or .Dbug) are ignored.

The mLog_LogEntry() Routine

This routine gets called every time a message is logged via the .Trace, .Dbug, .Info, etc. methods regardless of the value of Me.Level.

Therefore, it is CRITICALLY IMPORTANT that you include the following line of code as a guard clause as the first line of your mLog_LogEntry() routine:

If Level < Me.Level Then Exit Sub

Otherwise, your logger class will log every message regardless of its logging level.

If there is no mLog_LogEntry() procedure in your class module when you select LogEntry from the right dropdown, then VBA will auto-generate the procedure signature for the LogEntry event handler when you select it from the dropdown:

Now, whenever the mLog instance of our logging class (clsLog) raises its LogEntry event, the mLog_LogEntry() routine from our logger class (loggerMsgBox) will execute.

This is where things can get a bit confusing conceptually.  You might be wondering how we are supposed to call the .LogEntry method of mLog, seeing as it is a private member of class loggerMsgBox.  The short answer is...you don't.  You don't ever call mLog.LogEntry(...) (or mLog.Info(...), etc.).

Instead, remember that the mLog variable is not a direct representation in memory of an instance of the clsLog class.  Rather, it is a pointer to the location in memory where an instance of clsLog resides.

This next section illustrates the importance of understanding that concept.

The Initialization Routine

The last requirement that every logger class needs is an initialization routine.

The exact name of the routine is not important (Init() is simply my personal naming convention).

Public Sub Init(LogToSink As clsLog, DefaultLevelToLog As ll__LogLevel)
    Set mLog = LogToSink
    Me.Level = DefaultLevelToLog
End Sub

I use the initialization routine to set an initial logging level (Me.Level = DefaultLevelToLog) , but that's not strictly necessary.  What is necessary is the first line:

Set mLog = LogToSink
NOTE: LogToSink is short for "the instance of clsLog whose events we want to sink." It is unrelated to lumber cleansing

Think of LogToSink as the location in memory where an existing instance of the logging class resides.  In the above line of code, we are telling the mLog variable to point at this existing location in memory.

At this point, you're probably wondering when this "location in memory" actually gets created in the first place.  We'll get to that in our final section, when we create an instance of our logging class.

Part 3: Class Instances

Now for the final piece of the puzzle: creating class instances.

All of our class instances will be created on demand within a single public function named Log.  The instances themselves will be private, module-level, self-healing object variables.

Here's the code, which can go in any standard module:

Private mLog As clsLog
Private mMsgBoxLogger As loggerMsgBox

Public Function Log() As clsLog
    If mLog Is Nothing Then
        Set mLog = New clsLog
        
        'Reset loggers just in case they are pointed at
        '   a destroyed instance of clsLog
        Set mMsgBoxLogger = Nothing
    End If
    Set Log = mLog
    
    'Initialize each logger
    If mMsgBoxLogger Is Nothing Then
        Set mMsgBoxLogger = New loggerMsgBox
        mMsgBoxLogger.Init mLog, ll_Info
    End If    
    
End Function

Sample Usage

Once the setup is complete, you simply sprinkle calls to the various logging functions as shown below:

Log.Trace "Test trace"
Log.Info "Test info"

What do you think will happen when you execute the first line of code?

What will happen when you execute the second?

...

If you said the first line would do nothing and the next line would display a message box, then you would be correct!

Summary

We covered a lot of ground in this article.

If you don't care about the concepts and you just want the logging framework, here's a short list of what you need to do:

  1. Create a new class module named clsLog
  2. Copy and paste the big block of code from Part 1 above into clsLog
  3. Create a new class module named loggerMsgBox
  4. Copy and paste the big block of code from Part 2 above into loggerMsgBox
  5. Copy and paste the big block of code from Part 3 above into any standard code module (name does not matter)
  6. Begin logging messages with Log.Trace, Log.Dbug, Log.Info, Log.Warn, Log.Error, and Log.Fatal

Cover image generated by DALL-E-3.

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)


All original code samples by Mike Wolfe are licensed under CC BY 4.0