Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time smaller than a second
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time smaller than a second
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. -- John Green - Excel MVP Sydney Australia "PK" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time smaller than a second
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. -- John Green - Excel MVP Sydney Australia "John Green" wrote in message ... 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. -- John Green - Excel MVP Sydney Australia "PK" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time smaller than a second
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 "PK" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Smaller than 0 to return a 0 result | Excel Worksheet Functions | |||
Smaller worksheet | New Users to Excel | |||
can this be made smaller? | Excel Discussion (Misc queries) | |||
Can this be made smaller? | Excel Discussion (Misc queries) | |||
smaller equations: a & b, or just a.. | Excel Discussion (Misc queries) |