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

i need log actions of vba macro running in excel (100 subs , functions, 10 main sub). i'm using "workflow" sheet light log (mains sub) readable user.

but i'd log printed in immediate window debug.print, can generate lot since it's more comprehensive log. i'm considering :

open "c:\temp\log.txt" append #1 print #1, "this need logged" close #1 

how should use open , close commands ?

open / append / close each line, necessary

something :

public sub log_to_file(byval message string)     log_file_nb = freefile      open "c:\temp\log.txt" append #log_file_nb     print #log_file_nb, message     close #log_file_nb end sub  log_to_file ("this need logged") 

much simpler implement, i'm concerned performance of opening/closing file 1000's times.

open @ beginning of main sub / append necessary / close @ end of main sub

something :

public const log_file_nb = 1  public sub open_log_file(byval log_file_nb)     open "c:\temp\log mymacro.txt" append #log_file_nb end sub  public sub log_to_file(byval message string)     print #log_file_nb, message end sub  public sub close_log_file()     close #log_file_nb end sub  log_to_file ("this need logged") 

• how check if file open before trying log ? (some subs can launch directly button, or recursively main sub)

• can open output second file (my macro need output data csv files) while file n°1 still open ?

• will keeping file open have big impact on memory (assuming 100 000 lines in files) ?

there no problem opening more 1 file @ time. make sure file numbers different , don't confuse them.

your first method, opening file, appending it, , closing each call log_to_file can useful way it, if you're concerned losing logging data if program crashes or externally terminated. if buffer output, tim williams suggested, lose what's in buffer @ moment of crash/termination.

there performance cost, how depends on number of factors, configuration of computer system, type of drive log file located on, etc. may worth trying see sort of performance hit take. (one test worth thousand expert opinions...)

the other approach, open log file @ start , close @ end approach. tim suggested, create global boolean variable, logfileopenedand add check log_to_file sub.

public const log_file_nb = 1 private log_file_open boolean  public sub open_log_file(byval log_file_nb)     open "c:\temp\log mymacro.txt" append #log_file_nb     log_file_open = true end sub  public sub log_to_file(byval message string)     if not log_file_open 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 leaves hanging need close log file @ point. setting log_file_open false part of close_log_file sub, more once through course of running program. need @ least once, after of log_to_file calls.

one final note: opening log file in append mode, file grow each time program run. depending on situation, may acceptable, or may have include code keep log file becoming huge. possible solutions:

1. give log file name based on date. results in many smaller log files. delete log files dates old, keeping total number in check.
2. write code delete oldest (topmost) lines in file once file gets large. possible, not trivial.
3. the first time open log file during particular run of program, delete existing log file first. end log file includes logging of last program run.