Time smaller than a second

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

Hello.

I frequently use formatting such as the following:

xTime = Format(Time, "HH:MM:SS")

How would I format/capture time increments smaller than
the "SS" in the previous line? Or do I need to change my
approach?

What are the limits of the measurable time in VBA?

Thanks for your assistance and example code if possible.
 
It is possible to format a time with decimal fractions of a second:

Format(Time, "HH:MM:SS.00")

However, Time and other VBA functions will only return a result to the nearest second. It is possible to get finer measurements
using API calls.
 
Here's an example that returns time in milliseconds. It comes from an old post by Myrna Larson:

If you want millisecond resolution, you can call a Windows API routine,
timeGetTime:

Here's some code adapted from a routine published by Ken Getz in VBA
Developer's Handbook.

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private StartTime As Long

Sub StartTimer()
StartTime = timeGetTime()
End Sub

Function EndTimer() As Long
EndTimer = timeGetTime() - StartTime
End Function

To use this:

Dim X As Double

StartTimer
Call RoutineToBeTimed
X = EndTimer() / 1000 'for elapsed time in seconds
Worksheets("Sheet1").Range("A1").Value = X

Note that if you want accurate timings, you SHOULD NOT write the start time to
the worksheet before calling your routine. Moving data between VBA and the
worksheet is very slow, particularly writing to the worksheet, and including
the time to do that could distort your figures significantly. So you keep the
values in VBA variables and write the results to the worksheet once, at the
end.
 
Use the following API:

Public Declare Function GetTickCount Lib "kernel32" () As Long
The GetTickCount function retrieves the number of milliseconds that have
elapsed since the system was started. It is limited to the resolution of the
system timer.

Bill Barclift
 
Back
Top