Essential CODE Snippets for Every PROJECT in 2023
to SAVE Debugging Time

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. Call Stack View 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.

Excel VBA Programming – The Complete Guide

Excel

Excel VBA & Scripts

Formulas

Power Query

Python

Outlook + Excel VBA

Others

Free Guide

The ONE Critical Excel Skill You Need

Home / Visual Basic

Visual Basic

Excel, VBA & Scripts, Visual Basic

Essential CODE Snippets for Every PROJECT in 2023
to SAVE Debugging Time

Excel, VBA & Scripts, Visual Basic

Round Time to Nearest N Minutes

Excel, VBA & Scripts, Visual Basic

SAVE Debugging

Excel, Power Query, Visual Basic

List.Generate in Power Query – A Complete Guide With Examples

Excel, Power Query, VBA & Scripts, Visual Basic

Advanced uses of the Web.Contents for data retrieving from API (Power Query)

Excel VBA Programming – The Complete Guide

Free Guide

The ONE Critical Excel Skill You Need

Home / Visual Basic

Visual Basic

Excel, VBA & Scripts, Visual Basic

Excel, VBA & Scripts, Visual Basic

Excel, VBA & Scripts, Visual Basic

Excel, Power Query, Visual Basic

Excel, Power Query, VBA & Scripts, Visual Basic

Stay Ahead with Weekly Insights!

Dive into Excel, Al and other essential tech news:

carefully crafted for the modern professional.