Simple timing measurements in VBA

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

Quote:

---

Originally Posted by cappy2112

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

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

Use this:

Code:

---

StartTime = Timer

'Code to process the file

Elapsed = (Timer - StartTime)

---

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

and what about 'Timer' function?

Normally I am using a named range plus this code

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

Quote:

---

Originally Posted by aio

Normally I am using a named range plus this code

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