If you want to reduce debugging time, this post is for you. It would help if you had these code snippets in every project in 2023. Debugging in coding is a time-consuming task especially tracing method call stack. We need to know how different methods get called.
In VBA, if you press the Ctrl+ L shortcut while the code is running, you will get its call stack like the image below. We can’t get it using VBA itself while the code is running. For that reason, I have created a Logger class that will log different method calls, and then you can have call logs.
Here is the code for the Logger class and Mapper class (Helper for Logger)
Logger.cls
Mapper.cls
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Mapper"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'@IgnoreModule SuperfluousAnnotationArgument, ImplicitlyTypedConst, UnrecognizedAnnotation
'@Folder "Lambda.Editor.Logger"
'@PredeclaredId
Option Explicit
Const QUOTATION_MARK As String = """"
Const HASH_SIGN As String = "#"
Const JSON_PATTERN As String = QUOTATION_MARK & "{0}" & QUOTATION_MARK & " : " & _
QUOTATION_MARK & "{1}" & QUOTATION_MARK
Const JAGGED_MAPPER_JSON_PATTERN As String = QUOTATION_MARK & "{0}" & QUOTATION_MARK & " : " & "[" & "{1}" & "]"
Const COMMA As String = ","
Private Type TMapper
Key As String
Value As Variant
ToJSON As String
End Type
Private this As TMapper
Public Property Get ToJSON() As String
Dim JSONText As String
Dim JaggedMapperCollection As Collection
Dim CurrentMapper As Mapper
If TypeName(this.Value) = "Collection" Then
Set JaggedMapperCollection = this.Value
If TypeName(JaggedMapperCollection.Item(1)) = "Mapper" Then
For Each CurrentMapper In JaggedMapperCollection
JSONText = JSONText & COMMA & CurrentMapper.ToJSON
Next CurrentMapper
End If
JSONText = BeautifyString(JAGGED_MAPPER_JSON_PATTERN, Array(this.Key, JSONText))
Else
JSONText = BeautifyString(JSON_PATTERN, Array(this.Key, this.Value))
End If
this.ToJSON = JSONText
ToJSON = this.ToJSON
End Property
Public Property Get Key() As String
Key = this.Key
End Property
Public Property Let Key(ByVal RHS As String)
this.Key = RHS
End Property
Public Property Get Value() As Variant
If IsObject(this.Value) Then
Set Value = this.Value
Else
Value = this.Value
End If
End Property
Public Property Let Value(ByVal RHS As Variant)
this.Value = RHS
End Property
Public Property Set Value(ByVal RHS As Variant)
Set this.Value = RHS
End Property
Public Function Create(GivenKey As String, GivenValue As Variant) As Mapper
Dim CurrentMapper As Mapper
Set CurrentMapper = New Mapper
With CurrentMapper
.Key = GivenKey
If IsObject(GivenValue) Then
Set .Value = GivenValue
Else
.Value = GivenValue
End If
End With
Set Create = CurrentMapper
End Function
'@Author : Md.Ismail Hosen
'@Description("This function will replace placeholder text with appropriate values")
'@Dependency("No Dependency")
'@ExampleCall : BeautifyString("Your Name : {1} Your Age: {2}",Array("Md.Ismail Hosen", 24),1) >> Your Name : Md.Ismail Hosen Your Age: 24
'@ExampleCall : BeautifyString("Your Name : {0}","Md.Ismail Hosen") >> Your Name : Md.Ismail Hosen
'@ExampleCall : BeautifyString("Your Name : {}","Md.Ismail Hosen") >> Your Name : {} >> Because no place holder value..So if your text has {} this will help
Private Function BeautifyString(Pattern As String, PlaceHolderValues As Variant _
, Optional StartNumber As Long = 0, Optional PlaceHolder As String = "{#}") As String
Dim PlaceHolderValue As Variant
Dim CurrentPlaceHolder As String
If IsArray(PlaceHolderValues) Then
For Each PlaceHolderValue In PlaceHolderValues
CurrentPlaceHolder = Replace(PlaceHolder, HASH_SIGN, StartNumber)
Pattern = Replace(Pattern, CurrentPlaceHolder, PlaceHolderValue)
StartNumber = StartNumber + 1
Next PlaceHolderValue
Else
CurrentPlaceHolder = Replace(PlaceHolder, HASH_SIGN, StartNumber)
Pattern = Replace(Pattern, CurrentPlaceHolder, PlaceHolderValues)
End If
BeautifyString = Pattern
End Function
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Logger"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'@Folder("LambdaEditor.Logger")
'@PredeclaredId
Option Explicit
Const LEVEL_JSON_KEY As String = "Level"
Const TIME_JSON_KEY As String = "Log Time"
Const MESSAGE_JSON_KEY As String = "Message"
Const CALL_FROM_JSON_KEY As String = "Call From"
Const PARAMETERS_JSON_KEY As String = "Parameters"
Const LOG_FILE_OPEN_NUMBER As Long = 1
Const ALREADY_OPENED_FILE_ERROR_NUMBER As Long = 55
Private Const IS_PRODUCTION_MODE_ON As Boolean = False
Public IsLogFileOpen As Boolean
Public Enum Level
INFO_LOG = 0
TRACE_LOG = 1
DEBUG_LOG = 2
WARNING_LOG = 3
ERROR_LOG = 4
End Enum
'Attention: This level description is linked with the Level Enum
Private Function GetLogLevelDescription() As Variant
GetLogLevelDescription = Array("INFO", "TRACE", "DEBUG", "DEBUG", "WARNING", "ERROR")
End Function
Public Sub Log(LogLevel As Level, Message As String, Optional CallFrom As String = vbNullString, Optional Parameters As Variant)
If IS_PRODUCTION_MODE_ON Then Exit Sub
Dim MapperCollection As Collection
Set MapperCollection = New Collection
Dim LogLevelDescription As String
LogLevelDescription = GetLogLevelDescription(LogLevel)
With MapperCollection
.Add Mapper.Create(LEVEL_JSON_KEY, LogLevelDescription), LEVEL_JSON_KEY
Dim CurrentTimeText As String
CurrentTimeText = VBA.Format$(Now(), "DD MMM YYYY HH:MM:SS AM/PM")
.Add Mapper.Create(TIME_JSON_KEY, CurrentTimeText), TIME_JSON_KEY
.Add Mapper.Create(MESSAGE_JSON_KEY, Message), MESSAGE_JSON_KEY
If CallFrom <> vbNullString Then
.Add Mapper.Create(CALL_FROM_JSON_KEY, CallFrom), CALL_FROM_JSON_KEY
End If
If Not IsMissing(Parameters) Then
.Add Mapper.Create(PARAMETERS_JSON_KEY, Parameters), PARAMETERS_JSON_KEY
End If
End With
LogToDifferentLogContainer MapperCollection
End Sub
Private Sub LogToDifferentLogContainer(MapperCollection As Collection)
Dim FileLogMessage As String
FileLogMessage = PrepareJSON(MapperCollection)
If Not IsLogFileOpen Then OpenLogFile
On Error GoTo ExitLog
Print #LOG_FILE_OPEN_NUMBER, FileLogMessage
Dim ImmediateWindowLogMessage As String
MapperCollection.Remove LEVEL_JSON_KEY
MapperCollection.Remove TIME_JSON_KEY
ImmediateWindowLogMessage = PrepareJSON(MapperCollection)
ImmediateWindowLogMessage = VBA.Replace(ImmediateWindowLogMessage, "{", vbNullString, 1, 1)
ImmediateWindowLogMessage = VBA.Left$(ImmediateWindowLogMessage, Len(ImmediateWindowLogMessage) - 1)
Debug.Print ImmediateWindowLogMessage
'Application.StatusBar = ImmediateWindowLogMessage
Exit Sub
ExitLog:
Debug.Print Err.Description
Debug.Print ImmediateWindowLogMessage
End Sub
Private Function PrepareJSON(KeyValuePair As Collection) As String
Dim CurrentMapper As Mapper
Dim JSONText As String
For Each CurrentMapper In KeyValuePair
JSONText = JSONText & "," & CurrentMapper.ToJSON
Next CurrentMapper
JSONText = VBA.Right$(JSONText, Len(JSONText) - 1)
JSONText = "{" & JSONText & "}"
PrepareJSON = JSONText
End Function
Public Sub OpenLogFile()
Dim LogFilePath As String
LogFilePath = GetLogFilePath
On Error GoTo ErrorToOpenFile
If Not IsLogFileOpen Then
Open LogFilePath For Append As #LOG_FILE_OPEN_NUMBER
End If
ErrorToOpenFile:
If Err.Number = ALREADY_OPENED_FILE_ERROR_NUMBER Then
IsLogFileOpen = True
Log TRACE_LOG, "Log File is Opened for logging", "Logger.OpenLogFile"
End If
End Sub
Private Function GetLogFilePath() As String
Dim LogFolder As String
LogFolder = ThisWorkbook.Path & Application.PathSeparator _
& ThisWorkbook.Name & Space(1) & "Logs" & Application.PathSeparator
CreateFolderIfNotExist LogFolder
Dim LogFileName As String
LogFileName = VBA.Format$(Date, "DD MMMM YYYY") & ".log"
GetLogFilePath = LogFolder & LogFileName
End Function
Public Sub CloseLogFile()
Log TRACE_LOG, "Log File is closing", "Logger.CloseLogFile"
Close #LOG_FILE_OPEN_NUMBER
IsLogFileOpen = False
End Sub
Private Sub CreateFolderIfNotExist(FolderPath As String)
Dim FolderManager As Object
Set FolderManager = CreateObject("Scripting.FileSystemObject")
If Not FolderManager.FolderExists(FolderPath) Then
FolderManager.CreateFolder FolderPath
End If
Set FolderManager = Nothing
End Sub
The main important method of the Logger class is Log. We have set VB_PredeclaredId to True. So it will have a static method. We can call Logger.Log without having any instance of the Logger class. And we need to keep two logs for each method. One is for entry and the other is for exit log. Here is a sample function with it.
Public Sub UpdateAllPowerQueryCode(Optional OfBook As String = vbNullString)
Logger.Log TraceLog, "Enter modFormatPowerQueryCode.UpdateAllPowerQueryCode"
Dim CurrentBook As Workbook
If OfBook = vbNullString Then
Set CurrentBook = ActiveWorkbook
Else
On Error Resume Next
Set CurrentBook = Application.Workbooks(OfBook)
On Error GoTo 0
End If
If CurrentBook Is Nothing Then Exit Sub
Dim CurrentQuery As WorkbookQuery
For Each CurrentQuery In CurrentBook.Queries
Debug.Print " >> Formatting : " & CurrentQuery.Name
CurrentQuery.Formula = GetFormattedMCode(CurrentQuery.Formula)
Debug.Print
Next CurrentQuery
Logger.Log TraceLog, "Exit modFormatPowerQueryCode.UpdateAllPowerQueryCode"
End Sub
How Does it help to DEBUG?
So whenever we run code it will create a log file by
- It will create a subfolder having Logs appended with workbook name. If already exist then skip.
- Create a log file with Date Stamp(DD MMMM YYYY.log)
- Then it will put all the trace log into that.
And here is the sample log for it
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Log File is Opened for logging","Call From" : "Logger.OpenLogFile"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Enter modFormatPowerQueryCode.UpdateAllPowerQueryCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Enter modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Exit modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Enter modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Exit modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:45 PM","Message" : "Enter modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:46 PM","Message" : "Exit modFormatPowerQueryCode.GetFormattedMCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:46 PM","Message" : "Exit modFormatPowerQueryCode.UpdateAllPowerQueryCode"}
{"Level" : "TRACE","Log Time" : "04 Jan 2023 11:46:46 PM","Message" : "Log File is closing","Call From" : "Logger.CloseLogFile"}
So if you look at the log file then you will understand order of method call and time.
