Want to measure macro elapsed time.

N

Nevyenn

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.
 
D

Dave O

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.
 
N

Nevyenn

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.
 
Top