Logging code

[cj-1289]

Hi, I hope someone has an answer to this:

I've been pondering for a while if it is possible to have VBA write each line of code that it executes while going through in a process to a log, in order to monitor for blocks of code which are slowing it down. So for each line of code VBA processes, I'd like it printed to a text file along with the time that it was executed.

It would be quite a practically useful thing to do now, though, so I'm asking on this forum instead of simply pondering. Essentially I would need a module that would read each line to a string variable or array, done whenever this line was executed, and written immediately to a new line of a txt file along with the time of execution.

Thanks in advance.

 

[namliam]

Usually time contraint searches are done in blocks, not line by line...
I.e. If you have a Do While ... Loop then add in front of the Do and after the Loop a line,
Debug.Print Now() & ": Starting Loop"
Debug.Print Now() & ": Loop ended"

Then in the debug window (CTRL + G, in case you dont know how to find it) will show you want loop is taking how long and you can narrow down the search.

Logging each step is a NIGHTMARE and will not contribute much to the search of time limitations. Usually a single line of code is not the cause for slowness...
I.e. A DLookup is by itself "pretty fast" but if you exucute the same DLookup 10 mio times, instead of storing same the value in a variable, your busted!

 

[DCrake]

On a variation what you can do is concentrate on the DSSP's (Do Something Significant Point) in your code. At the start of your code block

Open "C:\Log.Txt" For Append As #1

At the starting point of a DSSP

Print #1, Time()
Print #1, "What is happening now..."

Place a DoEvents immediately after the end of the code snippet to render Access to process the above code before continuing, then after that line

Print #1, Time()
Print #1, "Operation Completed"

Repeat this though your code as and when required, then at the end

Close #1

You can then edit the log file to check how long it took to process.

You could add more infomation to the file depending on what you want to know, such as the date, the duration, what machine it was ruin on, etc.

David