VBA to test Macro Speed

[Muhamed Faizal]

Using following code, I could make speed of my Macro in Seconds.

Sub Speed()
StartTime = Timer
'
'other code goes here
'
MsgBox "This report prepared in " & Timer - StartTime & "Seconds"
End Sub

How do I convert the time in hh:mm:ss format?

Thanks in advance

 

[wigi]

Use instead:

Format((Timer - StartTime) / 86400, "hh:mm:ss")

There are 86400 seconds in a day, which is the unit interval for Timer.

 

[Muhamed Faizal]

Thanks for the reply. It gives incorrect value

Eg: 01:52:30 for lessthan 5 Sec

 

[wigi]

I edited my message - did you test the last version?

 

[dave3009]

Yip I'd go with wigi

Format((endt - Start) / 86400, "hh:mm:ss")

returns 00:00:04 for a 4 second runtime

 

[Muhamed Faizal]

I edited my message - did you test the last version?

Wonderful, It works perfectly<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank You Wigi<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:\DOCUME~1\mfaizal\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>

 

[jean.lopes]

You could also use the Now() function

Sub test()
    Dim Start, Finish, Time As Date
    Dim i As Long

    Start = Now

    'adding some code just to delay a bit for an example
    For i = 1 To 1000000000
    Next i

    Finish = Now

    Time = Finish - Start
    MsgBox Time
End Sub

 

[wigi]

Hello Jean

Thank you for contributing.

I would not use Time, since that is a reserved word. But you could use it if you wanted.

Also, note that your variables Start and Finish are declared as Variants, not as Date.
You need to repeat the "As Date" twice.

 

[jean.lopes]

Hi! thx for the heads up,
you mean like:
Dim Start as Date, Finish as Date, Time as Date ?

 

[wigi]

Yes