Creating a logfile while a macro is running - worthwhile?

[chuckles1066]

I'm putting together a macro and have been asked if it's possible to have a complete audit of events recorded.

Easy enough, the first stage of my macro will ask them if they wish to create a log file, if they click Yes then a Boolean flag (logfileyorn) is set to true.

But for everything that then happens, I guess I will have to code something like

 If logfileyorn Then........

or even

 Select case (logfileyorn).........

and go on to use the Print # command to write to a text file

Has anyone ever done anything similar?

Does it slow the macro down? What's the ultimate solution?

TIA.

 

[John_w]

I wrote this self-contained debugging/logging function recently:

Private Function DBG(txt As Variant)

    Static fileNum As Integer
    Static recs As Long
    Static debugFile As String
    
    If gDEBUGTOFILE Then
        If debugFile = "" Then debugFile = "debug_" & Format(Now, "yymmdd_HhNnSs") & ".txt"
        If fileNum = 0 Then
            fileNum = FreeFile
            Open gDEBUGFOLDER & debugFile For Append As fileNum
        End If
        Print #fileNum, Format(Now, "HH:MM:SS ") & txt
        
        recs = recs + 1
        If recs Mod 5 = 0 Then
            'Close the file every 5 records to ensure buffer is flushed
            Close #fileNum
            fileNum = 0
        End If
    End If
    
    Debug.Print Format(Now, "HH:MM:SS ") & txt
    
End Function

It logs to the Immediate window, with the option of also logging to a file and is controlled by these global constants:

Const gDEBUG As Boolean = True
Const gDEBUGTOFILE As Boolean = True
Const gDEBUGFOLDER As String = "C:\temp\Excel\"

and invoked by statements such as:

    If gDEBUG Then DBG "IE1 Busy " & .busy & " ReadyState " & .readystate

        If gDEBUG Then
            DBG arrDropDownOptions(i + 1)(j)        'Option value
            DBG arrDropDownOptions(i + 1)(j + 1)    'Option visible text
        End If

There will be a small performance penalty whichever method you use. Logging to the current workbook, maybe in a hidden sheet, should be faster than the file Print # method, but then you have to ensure that the workbook is saved to save the logged events.

 

[chuckles1066]

Thanks very much for that, I'll have a play