“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.
See Also
Write-Log, a similar logging function for MS PowerShell.
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
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]