CODEWITHSUNDEEP
vbaexcelloggingappend
Tibo
Tibo

Reputation: 393

Logging strategies with 'Open myFile For Append' (VBA Excel)

I need to log the actions of a VBA macro running in Excel (100 subs and functions, 10 main sub). I'm already using a "Workflow" sheet as a very light log (mains sub) readable by the user.

But I'd like to log what is currently printed in the Immediate Window by Debug.Print, which can generate a lot since it's much more comprehensive log. I'm thus considering something like :

Open "C:\Temp\Log.txt" For Append As #1
Print #1, "This need to be logged"
Close #1

How should I use the Open and Close commands ?

Open / Append / Close for each line, as necessary

Something like : 

Public Sub Log_to_file(byval message as string)
    log_file_nb = FreeFile 
    Open "C:\Temp\Log.txt" For Append As #log_file_nb
    Print #log_file_nb, message
    Close #log_file_nb
End Sub

Log_to_file ("This need to be logged")

Much simpler to implement, but I'm concerned about the performance of opening/closing the file 1000's times.

Open at the beginning of the Main sub / Append as necessary / Close at the end of the Main sub

Something like :

Public Const log_file_nb = 1

Public Sub Open_log_file(byval log_file_nb)
    Open "C:\Temp\Log myMacro.txt" For Append As #log_file_nb
End Sub

Public Sub Log_to_file(byval message as string)
    Print #log_file_nb, message
End Sub

Public Sub Close_log_file()
    Close #log_file_nb
End Sub

Log_to_file ("This need to be logged")

Upvotes: 1

Views: 3061

Answers (1)

Rich Holton
Rich Holton

Reputation: 682

There is no problem opening more than one file at a time. Just make sure your file numbers are different and you don't confuse them.

Your first method, opening the file, appending to it, and closing with each call to log_to_file can be a useful way to do it, especially if you're concerned about losing logging data if the program crashes or is externally terminated. If you do buffer the output, as Tim Williams suggested, you would lose what's in the buffer at the moment of the crash/termination.

There will be some performance cost, but how much depends on a number of factors, like the configuration of the computer system, what type of drive the log file is located on, etc. It may be worth trying to see what sort of performance hit you take. (One test is worth a thousand expert opinions...)

The other approach, where you open the log file at the start and close it at the end is also a good approach. As Tim suggested, just create a global boolean variable, something like logfileOpenedand add a check to the log_to_file sub.

Public Const log_file_nb = 1
private log_file_open as boolean

Public Sub Open_log_file(byval log_file_nb)
    Open "C:\Temp\Log myMacro.txt" For Append As #log_file_nb
    log_file_open = True
End Sub

Public Sub Log_to_file(byval message as string)
    if not log_file_open then Open_log_file log_file_nb
    Print #log_file_nb, message
End Sub

Public Sub Close_log_file()
    Close #log_file_nb
    log_file_open = False
End Sub

A detail this leaves hanging is the need to close the log file at some point. By setting log_file_open to false as part of the Close_log_file sub, you could do this more than once through the course of running the program. But you would need to do it at least once, after all of the Log_to_file calls.

One final note: By always opening the log file in append mode, the file will grow each time the program is run. Depending on the situation, this may be acceptable, or you may have to include some code to keep the log file from becoming huge. Some possible solutions:

  1. Give the log file a name based on the date. This results in many smaller log files. You could also delete log files with dates that are too old, keeping the total number in check.
  2. Write code to delete the oldest (topmost) lines in the file once the file gets too large. This is very possible, but not trivial.
  3. The first time you open the log file during a particular run of the program, delete the existing log file first. You end up with a log file that only includes the logging of the last program run.

Upvotes: 2

Related Questions