Simple timing measurements in VBA

[cappy2112]

I had used VB6 up until ~2 years ago, so I've forgotten some things.

I'm now maintaining some Excel VBA macros which is reprocessing a text file, then displays stuff about the file in a spreadhseet

I'd like to measure the time it takes to process the file, but can't seem to find the right functions to do it. I only need 1-second resolution.

I've looked through VBA in a nutshell, but the time-related calls dont seem to do what I need (or I'm not looking in the right place.

StartTime = SomeTimeFunction()

' code to process the file

StopTime = SomeTimeFunction()

Elapsed = (StopTime-StartTime)


I've though about adding a timer using 1-second callbacks, to increment a count, but I can't seem to add any VB controls to the Control Pallete in The VBA Editor in Excel 2003.

(VB6 let me add tons of controls.)

Does anyone have any code I can look at to get the elapsed time in seconds?

thanks

[kender_a]

...I only need 1-second resolution.

Lookup the GetTickCount function. This Windows API function returns a number of millisecond that had elapsed since since windows started.

[dglienna]

Excel had Time/Date fields, so you can use DateDiff() and DateAdd() to get time in hours, minutes, seconds, days, weeks, etc.

[Shaikh.Riyaz.a]

Use this:

StartTime = Timer

'Code to process the file

Elapsed = (Timer - StartTime)

[Shuja Ali]

GetTickCount is actually what you need. There are numerous examples here on CG and even on the other sites.

[Shaikh.Riyaz.a]

and what about 'Timer' function?

[aio]

Normally I am using a named range plus this code

Public Sub perform_task()
    Range("runtime_start").Value = Now()
    task1
    task2
    task3
    Range("runtime_end").Value = Now()
End Sub

Then a third cell returns Range("runtime_end").Value minus Range("runtime_start").Value

[cappy2112]

Normally I am using a named range plus this code

Public Sub perform_task()
    Range("runtime_start").Value = Now()
    task1
    task2
    task3
    Range("runtime_end").Value = Now()
End Sub

Then a third cell returns Range("runtime_end").Value minus Range("runtime_start").Value

Thanks to everyone who replied.
It's starting to come back to me now