Always add
Option Explicit
Make sure to use the May 8 version of SubZero with a GetCentralLogFilePath function that trims the extra quotes from the variant string value when fetching Central Log Path
gstrLogPath
Make sure the local log line writing index variable is globally declared at the top
Public iLogIndex As Integer
Always make sure that the column-to-textfile log writer runs before the macro quits. This will be at the end of the mainline logic, but also at all "bail out" exit points. Call WriteTextLog as the last thing before closing Excel on any "Bail to The Exit" sketchy go-to code - before Exit Sub or other types of Exit actions. It's important that WriteTextLog is called at least once, or the text log file never gets created.
WriteTextLog
For the Jason style workbooks with local Log in G, update SubZero prologue constant to use G rather than D or I or wuddevah
Public Const LocalLogSheetColumnID As String = "G"
Use standard WriteLog - but NOT with Private scope- we need UserForms to be able to call it
Sub WriteLog(sMessage As String)
ThisWorkbook.Worksheets("Macro").Range(LocalLogSheetColumnID & iLogIndex).Value = Now & ": " & sMessage
iLogIndex = iLogIndex + 1
End Sub
Drop this block at the top of the main line, typically in place of the "clear_log" line.
' ####### LOG UP #########
ThisWorkbook.Worksheets("Macro").Range(LocalLogSheetColumnID & ":" & LocalLogSheetColumnID).Delete
iLogIndex = 1
WriteLog "We are at the start, local sheetlog scrubbed, iLogIndex was just set to 1 "
WriteLog "Invoking SubZero function WriteLogFile..."
WriteLogFile
WriteLog "SubZero GetTextfileData: gblnActivate returns: " & GetTextfileData("gblnActivate")
WriteLog "SubZero GetTextfileData: gstrDelimiter returns: " & GetTextfileData("gstrDelimiter")
' ####### LOG #########
Remove these subs and wherever they are invoked, replace with subzero WriteLog calls.
Module1: clear_log
Module1: log_message
Module1: log_run_completed
Module1: log_run_started
In the refactored log writing calls, we need to change syntax to be simple append strings
From:
log_message "ERROR - Could not delete file: " & filePath, MessageError
To:
WriteLog "ERROR - Could not delete file: " & filePath & ", " & MessageError
One error seen on closing a macro - Object/With block variable is occurring because of an exit point (bail out) with post-close code that's trying to close excel items that have already been closed, as in the progressForm bail-out code.
I added the write msg and WriteTextLog and that helped
'Check if user cancelled the process
If intReturnCode > 0 Then
WriteLog "User Cancelled"
WriteTextLog
EndProcess objHOST, oExcel
Exit Sub
End If
If rc > 0 Then
WriteTextLog
Exit Sub
End If
CAREFUL - some folks use a sub like end_process
to catch the bail-out processing so in those cases the WriteTextLog coverage logic may need a different structure.
There are also some "Cancel" buttons in UserForms - those must be intercepted with a log call.
Private Sub cancelButton_Click()
Unload dateForm
End
End Sub
This needs to change to:
Private Sub cancelButton_Click()
Module1.WriteLog "Report upload Cancelled."
SubZero.WriteTextLog
Unload dateForm
End
End Sub
Also we need to remove short-circuit bail-outs, as when there are If statements with no End If like so:
If Not get_reports Then Exit Function
If Not do_monarch Then Exit Function
If Not build_workbook Then Exit Function
We need these to leave a breadcrumb before bailing, cmon people!
If Not get_reports Then
WriteLog "get_reports Failure"
WriteTextLog
Exit Function
End If
Another example of rewrite:
Before:
If processRequest Then
'log_run_completed
do_status "Idle."
MsgBox "Operation complete."
Else
MsgBox "Program execution halted."
WriteLog "Program execution halted."
do_status "Program execution halted."
End If
After:
If processRequest Then
WriteLog "run_completed"
do_status "Idle."
MsgBox "Operation complete."
Else
MsgBox "Program execution halted.", vbExclamation
WriteLog "Program execution halted. " & MessageError
do_status "Program execution halted."
WriteTextLog
End If
look for MsgBox "Macro completed!" will often need to move the cleanup actions all to bottom
WriteLog "Macro Complete!"
WriteTextLog
MsgBox "Macro Complete!"
'Clean Up
Set objHOST = Nothing
oExcel.Visible = True
Set oExcel = Nothing
Look for random runs of hard-coded log-writing instructions that write to an absolute location on the worksheet, especially those using the dreaded “ActiveSheet”, which, if you have multiple workbooks open, will vandalize other spreadsheets. These should be replaced with well behaved logging code. For example, this:
ActiveSheet.Range("D:D").Value = "" 'clear column D
ActiveSheet.Range("D:D").Font.Size = 12 'set font size for column D
ActiveSheet.Range("D1").Value = "Change Legal Status Process started at " & _
Now 'print the start time to the Excel sheet
Should be replaced with this
WriteLog "Change Legal Status Process started at " & Now
And this
ActiveSheet.Range("D2").Value = "Change Legal Status Process " & _
"completed at " & Now
Should instead be this
WriteLog "Change Legal Status Process completed at " & Now
One more tidy-up - replace "hail mary" ActiveSheet declarations that can replaced with a qualified declaration to the sheet named "macro".
Set gMacroSheet = ActiveSheet
Should be
Set gMacroSheet = ThisWorkbook.Worksheets("Macro")
When the macro has a UserForm with cancel buttons, an unlogged bailout can slip by. Make sure to add log writing to the Cancel button.
Change from:
Private Sub cancelButton_Click()
Unload dateForm
End
End Sub
To:
Private Sub cancelButton_Click()
Module1.WriteLog "Report upload Cancelled."
SubZero.WriteTextLog
Unload dateForm
End
End Sub