I would like to add an elapsed time function to some of my macros to
determine some performance benchmarks for different PCs. One of my macros is
performing a lookup of roughly 10,000 items against a separate sheet
containing 48 columns of data. (I need to do this because our inventory
control system and MRP systems are not being very friendly.) The way that I
am currently accomplishing this takes a significant amount of time, and I'd
like to be able to record the exact time the macro runs.

I've done this by declaring variables Beg and Fin (for Begin and
Finish) as dates; at the beginning of the code enter the line Beg =
Now() and at the end of the code enter Fin = Now(). Elapsed time is
the result of the formula Fin-Beg, which you can display in a message
box or write to a cell.

Thank you for the advice. At first, the time difference was calculated and
displayed in scientific notation, so I wrote the following to format it
properly for the message box display that you suggested:

Beg = Now()
'Code
Fin = Now()
Elapsed = Fin - Beg
Elapsed = Format(Elapsed, "h:mm:ss")
MsgBox ("Elapsed time of the macro:" & Chr(10) & Chr(10) & " " & Elapsed)

Probably not the simplest way to do it, but it's working for me while I
learn how to refine it.


"Dave O" wrote:

I've done this by declaring variables Beg and Fin (for Begin and
Finish) as dates; at the beginning of the code enter the line Beg =
Now() and at the end of the code enter Fin = Now(). Elapsed time is
the result of the formula Fin-Beg, which you can display in a message
box or write to a cell.