Logging (VBA Class) ·

“Testing can show the presence of bugs, but not their absence.” [Edsger W. Dijkstra]

Abstract

This Logger class enables message logging with severities INFO, WARN, FATAL, and EVER to a log file as well as to an Excel sheet.

Using this Logger class is not complicated: Copy general module Logger_Factory and class module Logger from sample file at the end of this page into your own application, then define Public Const AppVersion with value “My Application Version 1.0” for example in your main module, and now you can with

GLogger.info "Info message ..."
GLogger.warn "Warn message ..."
GLogger.fatal "Error message ..."
GLogger.ever "Not suppressable standard message ..."

create your own log messages and store them automatically in sheet Workflow and in log file “My Application Version 1.0_Logfile_yyyymmdd.log” in subfolder Logs.

Please note that I got the initial code for this class from Cliff G. in 2009 which I enhanced later on. Cliff used his class mainly for testing. I found it very helpful to create an audit trail. Recently I added version information and system / Excel settings to quickly find out differences between user environments. With this logger I also use to measure simple runtimes of each SQL database query which I invoke from Excel, for example with a code such as:

'Glogger is declared in module LoggerFactory and set in Sub auto_open()
Dim dtStamp As Date
'...
dtStamp = Now
'Retrieve data from database here
Glogger.info "SQL xxx ran " & Format(Now - dtStamp, "n:ss") & " [m:ss]"

Pro and Con

In my opinion this Logger program offers the most reasonable secondary functionality of any VBA application. You can:

test in a traceable manner
easily see whether more than one user are running a program simultaneously
easily detect whether an issue is caused by a different user environment
systematically isolate even sporadic application errors
convincingly show auditors the correct bugfree application over a longer time period (single log files could be manipulated but a larger set of log files does convince)
roughly measure the runtime of VBA (sub-)routines
measure the throughput time of whole processes

The last point above might worry workers' council:

if you measure throughput times of whole processes, you can detect the performance of single employees, compare them and potentially use this information against them.

This would be a clear legal breach of the GDPR (General Data Protection Regulation), see (external link) https://gdpr-info.eu/

I never used this logging for performance measurement of my staff or of any user, but I sometimes detected user errors and then performed a re-training. But of course this cannot serve as an argument for its uncritical usage.

I think you can always reach agreement with workers' council by pointing out that this is a voluntary protocol:

each user can switch this logging on or off before starting an application
each user can delete the log files at any point in time after they have been created

I used and still use this logging in several European countries (UK, Germany) in different companies (banks, insurance companies, IT providers) without any complaint so far.

Parameters

Compiler constants

SEPARATE_LOGFILES_FOR_DIFFERENT_USERS - True = Separate Log files for different users, False = one daily Log file for all users

USE_LOGGER_AUTO_OPEN_CLOSE - True will use subs auto_open and auto_close of LoggerFactory, False will not

Logging_on_Screen - Set to True in both LoggerFactory and Logger if you want to log messages also to sheet Workflow (i. e. on screen).

Logging_cashed - Set to True in both LoggerFactory and Logger if you want to speed up the application by writing log messages in one go to the log file at program end. This requires Logging_on_Screen set to True.

Logging variables

LogFilePath - Full pathname of log file

SubName - Set at the beginning of each subroutine to enable the logger to report on the right subroutine name

LogLevel - The level for which logging should be performed:

	  1 - Report all log messages: INFO, WARN, FATAL, and EVER
	  2 - Report all log messages but not INFOs
	  3 - Report from FATAL level onwards, i. e. just FATAL and EVER messages
	  4 - Report only EVER messages
	  5 - Switch off logging

LogScreenRow - Row from where to start logging in sheet Workflow (usually 3)

Public constant

AppVersion - Define something like

Public Const AppVersion As String = "... Version x"

Then “… Version x” will be logged as version information for this application.

Modules

Please read my Disclaimer.

Normal

LoggerFactory contains constants, public variables, default logger settings, and optional autoopen and autoclose subs.

Option Explicit

'This general module is named LoggerFactory. Together with class module Logger it offers logging functionality.
'Version When         Who             What
'      1 Once upon .. Cliff G.        Initial version
'      2 25-Nov-2020  Bernd Plumhoff  Log level EVER
'      3 01-Dec-2020  Bernd Plumhoff  Make auto_open and auto_close optional and list used references
'      4 07-Dec-2020  Bernd Plumhoff  Changes to version and build info
'      5 11-Dec-2020  Bernd Plumhoff  Domain name added in class Logger, VDI type and Oracle client and Const SEPARATE_LOGFILES_FOR_DIFFERENT_USERS added
'      6 13-Jan-2021  Bernd Plumhoff  Oracle home x64 added
'      7 29-Jan-2021  Bernd Plumhoff  32 or 64 bit Office added
'      8 05-Sep-2021  Bernd Plumhoff  AppVersion instead of CAppVersion. Requires: Public Const AppVersion As String = "... Version x"
'                                     Declare strings and integers properly, not with $ or %
'      9 12-Sep-2021  Bernd Plumhoff  Move Private sThisLogFilePathfrom from class module Logger here as Public GsThisLogFilePath
'     10 17-Jan-2022  Bernd Plumhoff  Using function ApplicationVersion().
#Const SEPARATE_LOGFILES_FOR_DIFFERENT_USERS = False
#Const USE_LOGGER_AUTO_OPEN_CLOSE = True 'Enable auto_open and auto_close subs in here
#Const Logging_on_Screen = True          'IMPORTANT: Also change this constant in class module Logger! We like to see recent run's loggging messages on screen in tab Workflow
#Const Logging_cashed = False            'IMPORTANT: Also change this constant in class module Logger! Write logging messages into file at program end to speed this up
Public GLogger As Logger                 'Global logfile object - variable scope is across all modules
Public GsThisLogFilePath As String
' Constant log levels
Public Const INFO_LEVEL As Integer = 1
Public Const WARN_LEVEL As Integer = 2
Public Const FATAL_LEVEL As Integer = 3
Public Const EVER_LEVEL As Integer = 4 'For logging messages which cannot be switched off
Public Const DISABLE_LOGGING As Integer = 5
'The application-specific defaults
Const DEFAULT_LOG_FILE_PATH As String = "" 'Force error if not set [Bernd 12-Aug-2009]
Const DEFAULT_LOG_LEVEL As Integer = INFO_LEVEL
Public Function getLogger(sSubName As String) As Logger
    Dim oLogger As New Logger
    oLogger.SubName = sSubName
    'Defaults to the specified values - but may be overridden before used
    oLogger.LogLevel = DEFAULT_LOG_LEVEL
    oLogger.LogFilePath = DEFAULT_LOG_FILE_PATH
    Set getLogger = oLogger
End Function
#If USE_LOGGER_AUTO_OPEN_CLOSE Then
Sub auto_open()
'Change History:
'Version Date        Programmer Change
'1       25-Nov-2020 Bernd      Enable logging
'2       01-Dec-2020 Bernd      List used references
'3       02-Dec-2020 Bernd      Some changes to .info and comment on Trust Center Settings
'4       07-Dec-2020 Bernd      Changes to version and build info
'5       11-Dec-2020 Bernd      VDI Type and Oracle client added
'6       13-Jan-2021 Bernd      Oracle home x64 added
'7       29-Jan-2021 Bernd      32 or 64 bit Office added
'8       05-Sep-2021 Bernd      AppVersion instead of CAppVersion. Requires: Public Const AppVersion As String = "... Version x"
'9       12-Sep-2021 Bernd      Code outsorced to Start_Log so that user does not need to use auto_open.
Start_Log
End Sub
Sub auto_close()
'Change History:
'Version Date        Programmer Change
'1       25-Nov-2020 Bernd      Close logging
'2       05-Sep-2021 Bernd      AppVersion instead of CAppVersion
'3       12-Sep-2021 Bernd      Code outsorced to End_Log so that user does not need to use auto_close.
End_Log
End Sub
#End If '#If USE_LOGGER_AUTO_OPEN_CLOSE
Sub Start_Log()
'Change History:
'Version Date        Programmer Change
'1       12-Sep-2021 Bernd      Initial version so that user does not need to use auto_open. He can manually call this sub.
'2       17-Jan-2022 Bernd      Using function ApplicationVersion().
Dim wb As Workbook
Dim i As Long
Dim s As String, sDel As String
 
If Dir(ThisWorkbook.Path & "\Logs\", vbDirectory) = vbNullString Then
    MkDir ThisWorkbook.Path & "\Logs"
End If
If GLogger Is Nothing Then Set GLogger = New Logger
#If SEPARATE_LOGFILES_FOR_DIFFERENT_USERS Then
    'If AppVersion is not defined please define it in your main module like: Public Const AppVersion As String = "Application Version ..."
    GLogger.LogFilePath = ThisWorkbook.Path & "\Logs\" & Environ("Userdomain") & "_" & Environ("Username") & _
                          "_" & AppVersion & "_" & "Logfile_" & Format(Now, "YYYYMMDD") & ".txt"
#Else
    GLogger.LogFilePath = ThisWorkbook.Path & "\Logs\" & AppVersion & "_" & _
                          "Logfile_" & Format(Now, "YYYYMMDD") & ".txt"
#End If
GLogger.LogLevel = 1
#If Logging_on_Screen Then
    GLogger.LogScreenRow = 3
    wsW.Range("E2:E4").ClearContents
    wsW.Range("5:65535").Delete
#End If
'Initialize logger for this subroutine
With Application
GLogger.SubName = "Start_Log"
#If Win64 Then
s = "(64-bit)"
#Else
s = "(32-bit)"
#End If
GLogger.ever "Logging started with " & AppVersion & ", " & .OperatingSystem & " / " & getOperatingSystem() & _
             " and " & .Application & " [" & ApplicationVersion() & "] " & s & " " & .Version & .Build & " (" & .CalculationVersion & ")"
GLogger.info "Application ThousandsSeparator '" & .ThousandsSeparator & "', DecimalSeparator '" & .DecimalSeparator & _
             "', " & IIf(Not (Application.UseSystemSeparators), "do not ", "") & "use system separators"
GLogger.info "App.Internl ThousandsSeparator '" & .International(xlThousandsSeparator) & _
             "', DecimalSeparator '" & .International(xlDecimalSeparator) & "', ListSeparator '" & _
             .International(xlListSeparator) & "'"
GLogger.info "App.Internl xlCountryCode '" & .International(xlCountryCode) & "', xlCountrySetting '" & _
             .International(xlCountrySetting) & "'"
End With
Set wb = ThisWorkbook
With wb.VBProject.References 'In case of error tick box Trust access to the VBA project object model under File / Options /
                             'Trust Center / Trust Center Settings / Macro Settings
s = "VBAProject References: "
On Error Resume Next
For i = 1 To .Count
    s = s & sDel & .Item(i).Description
    sDel = ", "
Next i
GLogger.info s
'Now two examples of environment variables which might not exist for all Windows / Excel installations.
'Use Sub List_Environ_Variables below to see which variables exist on your system.
s = ""
s = Environ("CRC_VDI-TYPE") 'If this does not exist we will not log anything
If s <> "" Then GLogger.info "CRC_VDI-TYPE: '" & s & "'"
s = ""
s = Environ("ORACLE_HOME_X64") 'If this does not exist we will not log anything
If s <> "" Then GLogger.info "Oracle Client: '" & s & "'"
On Error GoTo 0
End With
End Sub
Sub End_Log()
'Change History:
'Version Date        Programmer Change
'1       12-Sep-2021 Bernd      Initial version so that user does not need to use auto_close. He can manually call this sub.
If GLogger Is Nothing Then Call auto_open
GLogger.SubName = "End_Log"
'If AppVersion is not defined please define it in your main module like: Public Const AppVersion As String = "Application Version ..."
GLogger.ever "Logging finished with " & AppVersion
#If Logging_cashed Then
    Set GLogger = Nothing 'Necessary, or Class_Terminate() won't be called for GLogger because it's Public
#End If
End Sub
'---------------------------------------------------------------------------------------
' Procedure : getOperatingSystem
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the active OS details
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Sep-27                 Initial Release
'---------------------------------------------------------------------------------------
Public Function getOperatingSystem()
    Dim localHost As String
    Dim objWMIService As Variant
    Dim colOperatingSystems As Variant
    Dim objOperatingSystem As Variant

   On Error GoTo Error_Handler

   localHost = "." 'Technically could be run against remote computers, if allowed
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & localHost & "\root\cimv2")
    Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")

   For Each objOperatingSystem In colOperatingSystems
        getOperatingSystem = objOperatingSystem.Caption & " " & objOperatingSystem.Version
        Exit Function
    Next

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: getOperatingSystem" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function
Function ApplicationVersion() As String
'Returns MS Excel's version - with a little kludge
'Source (EN): http://www.sulprobil.com/applicationversion_en/
'Source (DE): http://www.bplumhoff.de/applicationversion_de/
'(C) (P) by Bernd Plumhoff 19-Jan-2022 PB V0.3
Dim n As Integer
n = Val(Application.Version)
Select Case n
Case 16
    ApplicationVersion = "Excel 2016"
    'Excel 365 introduced Lambda - but this test does not seem to be
    'sufficient so far.
'    On Error Resume Next
'    ThisWorkbook.Names.Add Name:="HasLambda", RefersTo:="=LAMBDA(x,x)"
'    n = Evaluate("HasLambda(19)")
'    ThisWorkbook.Names("HasLambda").Delete
'    On Error GoTo 0
'    If n = 19 Then
'        ApplicationVersion = "Excel 365"
'    Else
        'Excel 2021 introduced RandArray
        On Error Resume Next
        n = Application.WorksheetFunction.RandArray(1, 1, 18, 18, True)(1)
        On Error GoTo 0
        If n = 18 Then
            ApplicationVersion = "Excel 2021/365" '"Excel 2021"
        Else
            'Excel 2019 introduced TextJoin
            On Error Resume Next
            n = Val(Application.WorksheetFunction.TextJoin(" ", True, "17"))
            On Error GoTo 0
            If n = 17 Then ApplicationVersion = "Excel 2019"
        End If
'    End If
Case 15
    ApplicationVersion = "Excel 2013"
Case 14
    ApplicationVersion = "Excel 2010"
Case 12
    ApplicationVersion = "Excel 2007"
Case 11
    ApplicationVersion = "Excel 2003"
Case 10
    ApplicationVersion = "Excel 2002"
Case 9
    ApplicationVersion = "Excel 2000"
Case 8
    ApplicationVersion = "Excel 97"
Case 7
    ApplicationVersion = "Excel 7/95"
Case 5
    ApplicationVersion = "Excel 5"
Case Else
    ApplicationVersion = "[Error]"
End Select
End Function
Sub List_Environ_Variables()
'Original code found here: http://www.office-loesung.de/ftopic46029_0_0_asc.php
'Note that you can call Environ with Environ(9) or Environ("USERNAME"), for example.
'Change History:
'Version Date        Programmer Change
'1       12-Dec-2020 Bernd      Initial version
Dim i As Long
Dim iPos As Long
Dim sKey As String
Dim sResult As String
Dim sValue As String
i = 1
Debug.Print "#", "Key", "Value"
Do
    sResult = Environ(i)
    If sResult <> "" Then
        iPos = InStr(sResult, "=")
        sKey = Left(sResult, iPos - 1)
        sValue = Mid(sResult, iPos + 1)
        Debug.Print iPos, sKey, sValue
    End If
    i = i + 1
Loop Until sResult = ""
End Sub

Logging_Example

A sample module General which just shows how you could use the logger:

Option Explicit

Public Const AppVersion As String = "Logging Version 9"
 
Sub Logging_Sample()
'Change History:
'Version Date       Programmer Change
'1       11/03/2020 Bernd      Logging example
'2       01/12/2020 Bernd      Logger factory now contains auto_open and auto_close
Dim i As Long

If GLogger Is Nothing Then auto_open
'Initialize logger for this subroutine
GLogger.SubName = "Logging_Sample"
 
'Just do something to give log message examples
i = 2
Do While Not IsEmpty(wsData.Cells(i, 1))
    Select Case i
    Case Is < 6
        GLogger.info i & " is a number less than 6"
    Case Is < 9
        Call Logging_Warn(i)
    Case Else
        Call Logging_Fatal(i)
    End Select
    i = i + 1
Loop
 
#If Logging_cashed Then
Set GLogger = Nothing 'Necessary, or Class_Terminate() won't be called for GLogger since it's Public
#End If
 
End Sub
 
'You do not need extra subroutines to log warn messages or fatal messages.
'They are just examples of additional subroutines which do some logging.
Sub Logging_Warn(i As Long)
    'Initialize logger for this subroutine
    GLogger.SubName = "Logging_Warn"
    GLogger.warn i & " is 6, 7, or 8"
End Sub
 
Sub Logging_Fatal(i As Long)
    'Initialize logger for this subroutine
    GLogger.SubName = "Logging_Fatal"
    GLogger.fatal i & " is greater 8"
End Sub

Class Modules

Logger contains the logging functionality.

Option Explicit
'This class module is named Logger. Together with class module LoggerFactory it offers logging functionality.
'Version When         Who             What
'      1 Once upon .. Cliff G.        Initial version
'      2 25-Nov-2020  Bernd Plumhoff  Log level EVER
'      3 09-Dec-2020  Bernd Plumhoff  Userdomain added
'      4 05-Sep-2021  Bernd Plumhoff  Declare strings and integers properly, not with $ or %
'      5 12-Sep-2021  Bernd Plumhoff  Move Private sThisLogFilePathfrom here to module LoggerFactorey as Public GsThisLogFilePath
#Const Logging_on_Screen = True 'IMPORTANT: Also change this constant in module LoggerFactory! We like to see recent run's loggging messages on screen in tab Workflow
#Const Logging_cashed = False   'IMPORTANT: Also change this constant in module LoggerFactory! Write logging messages into file at program end to speed this up
Const INFO_LEVEL_TEXT As String = "INFO:"
Const WARN_LEVEL_TEXT As String = "#WARN:"
Const FATAL_LEVEL_TEXT As String = "##FATAL:"
Const EVER_LEVEL_TEXT As String = "EVER:"
Private sThisSubName As String
Private iThisLogLevel As Integer
#If Logging_on_Screen Then
Private iThisLogRow As Integer
Public Property Let LogScreenRow(iLogRow As Integer)
    iThisLogRow = iLogRow
End Property
Public Property Get LogScreenRow() As Integer
    LogScreenRow = iThisLogRow
End Property
#End If
Public Property Let LogFilePath(sLogFilePath As String)
    GsThisLogFilePath = sLogFilePath
End Property
Public Property Get LogFilePath() As String
    LogFilePath = GsThisLogFilePath
End Property
Public Property Let SubName(sSubName As String)
    sThisSubName = sSubName
End Property
Public Property Get SubName() As String
    SubName = sThisSubName
End Property
Public Property Let LogLevel(iLogLevel As Integer)
    iThisLogLevel = iLogLevel
End Property
Public Property Get LogLevel() As Integer
    LogLevel = iThisLogLevel
End Property
Public Sub info(sLogText As String)
    If Me.LogLevel = LoggerFactory.INFO_LEVEL Then
        Call WriteLog(LoggerFactory.INFO_LEVEL, sLogText)
    End If
End Sub
Public Sub warn(sLogText As String)
    If Me.LogLevel < LoggerFactory.FATAL_LEVEL Then
        Call WriteLog(LoggerFactory.WARN_LEVEL, sLogText)
    End If
End Sub
Public Sub fatal(sLogText As String)
    If Me.LogLevel <= LoggerFactory.FATAL_LEVEL Then
        Call WriteLog(LoggerFactory.FATAL_LEVEL, sLogText)
    End If
End Sub
Public Sub ever(sLogText As String)
    If Me.LogLevel <= LoggerFactory.EVER_LEVEL Then
        Call WriteLog(LoggerFactory.EVER_LEVEL, sLogText)
    End If
End Sub
Private Sub WriteLog(iLogLevel As Integer, sLogText As String)
    Dim FileNum As Integer, LogMessage As String, sDateTime As String, sLogLevel As String
      
    Select Case iLogLevel
    Case LoggerFactory.INFO_LEVEL
        sLogLevel = INFO_LEVEL_TEXT
    Case LoggerFactory.WARN_LEVEL
        sLogLevel = WARN_LEVEL_TEXT
    Case LoggerFactory.FATAL_LEVEL
        sLogLevel = FATAL_LEVEL_TEXT
    Case LoggerFactory.EVER_LEVEL
        sLogLevel = EVER_LEVEL_TEXT
    Case Else
        sLogLevel = "!INVALID LOG LEVEL!"
    End Select
    sDateTime = CStr(Now())
    LogMessage = sLogLevel & " " & Environ("Userdomain") & "\" & Environ("Username") & " " & sDateTime & " [" & Me.SubName & "] - " & sLogText
    #If Not Logging_cashed Then
    FileNum = FreeFile
    Open Me.LogFilePath For Append As #FileNum
    Print #FileNum, LogMessage
    Close #FileNum
    #End If
    #If Logging_on_Screen Then
    wsW.Cells(iThisLogRow, 5) = LogMessage
    iThisLogRow = iThisLogRow + 1
    #End If
End Sub
Private Sub Class_Initialize()
    #If Logging_cashed And Not Logging_on_Screen Then
    Err.Raise Number:=vbObjectError + 513, Description:="Logging_cashed requires Logging_on_Screen"
    #End If
End Sub
Private Sub Class_Terminate()
    #If Logging_cashed Then
    Dim i As Long, FileNum As Integer, LogMessage As String
    FileNum = FreeFile
    Open Me.LogFilePath For Append As #FileNum
    For i = 3 To iThisLogRow - 1
        LogMessage = wsW.Cells(i, 5).Text
        Print #FileNum, LogMessage
    Next i
    Close #FileNum
    #End If
End Sub

Please read my Disclaimer.

logging.xlsm [53 KB Excel file, open and use at your own risk]

Sulprobil

Logging (VBA Class)

Abstract

Pro and Con

Parameters

See Also

Modules