PDA

View Full Version : Solved: I want to show the time it took for macro to run



MrAshton
03-07-2008, 11:03 AM
Hey. I'd like to show the time it took for my macro to run.
I tried to enter a stime as "now" at the beginning and at the end added an etime as "now" which stored the start and end times but when I tried to subtract stime from etime and store it as ttime. the value is some sort of large exponential value.

Is there a good way to display a message box showing how long the macro took when the macro is done?

RonMcK
03-07-2008, 11:24 AM
Mr Ashton,

Have you looked at the 'timer' function in Excel Help?
'This is a quick tweak of the Timer example in Help
Dim Start, Finish, TotalTime
Start = Timer ' Set start time.
' <invoke your macro here>
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Executing macro took: " & TotalTime & " seconds"

See if this works a bit better.

Cheers!

MrAshton
03-07-2008, 12:54 PM
Mr Ashton,

Have you looked at the 'timer' function in Excel Help?
'This is a quick tweak of the Timer example in Help
Dim Start, Finish, TotalTime
Start = Timer ' Set start time.
' <invoke your macro here>
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Executing macro took: " & TotalTime & " seconds"

See if this works a bit better.

Cheers!

Works perfectly. Im just going to remove some decimal places because right now it tells me it takes 35.42915 seconds lol

RonMcK
03-07-2008, 01:00 PM
Glad to be of assistance!

Be sure to mark you thread 'solved' before you move on to your next challenge.

Thanks,

MrAshton
03-07-2008, 01:02 PM
I just changed the dim start,finish,totaltime to dim start, finish, totaltime as integer and it did the trick as expected

RonMcK
03-07-2008, 02:13 PM
Forcing your variables to be integers is one way to accomplish what you want to do.

A couple of random thoughts:
You may want to use 'long' instead of 'integer' since it will handle a larger number.
I'd rather preserve and use detail as long as I can, and do my rounding when I display the final result; thus, I'd leave the Dim statement(s) alone and use the following:MsgBox ("'The macro took: " & Trim(Str(totaltime, "0.0")) & " seconds.")
I can use the code wherever I need it and all I need do is adjust the mask to get my desired precision.

Thanks,

MrAshton
03-07-2008, 03:08 PM
Hey that's cool. I'm pretty entry level with VB but i've been able to figure out how to do 99% of things I want to do, working with strings is not one of them. I have found just about every way around them that I can. That trim thing is awesome. Thanks

RonMcK
03-07-2008, 03:25 PM
I can appreciate keeping things simple. With numbers, unless an intermediate result has to be limited to a particular precision (number of decimals), I prefer to stay with max precision until the bitter end. :)

Keep asking questions and posing problems, you'll keep learning!

Cheers!