Creating a logfile while a macro is running - worthwhile?

chuckles1066

Banned
Joined
Dec 20, 2004
Messages
372
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

Code:
 If logfileyorn Then........
or even

Code:
 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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,723
I wrote this self-contained debugging/logging function recently:
Code:
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:
Code:
Const gDEBUG As Boolean = True
Const gDEBUGTOFILE As Boolean = True
Const gDEBUGFOLDER As String = "C:\temp\Excel\"
and invoked by statements such as:
Code:
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,142
Members
416,366
Latest member
ChrisDXB1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top