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

[MrAshton]

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]

Mr Ashton,

Have you looked at the 'timer' function in Excel Help?
[vba]'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"
[/vba]
See if this works a bit better.

Cheers!

[MrAshton]

Mr Ashton,

Have you looked at the 'timer' function in Excel Help?
[vba]'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"
[/vba]
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]

Glad to be of assistance!

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

Thanks,

[MrAshton]

I just changed the [VBA]dim start,finish,totaltime[/VBA] to [VBA]dim start, finish, totaltime as integer[/VBA] and it did the trick as expected

[RonMcK]

Forcing your variables to be integers is one way to accomplish what you want to do.

A couple of random thoughts:

1. You may want to use 'long' instead of 'integer' since it will handle a larger number.
2. 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:

[vba]MsgBox ("'The macro took: " & Trim(Str(totaltime, "0.0")) & " seconds.")[/vba]
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]

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]

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!