Excel time in milliseconds 2

[stduc]

Clutching at straws here but I would like see how long bits of a macro are taking and the now() function seems only to get the time to the nearest second even though I am using the following format mm:ss.000 I have also tried mm:ss.ms but I don't get the correct results displayed or the time captured to the nearest millisecond, only second.

Is it possible to get the time captured and displayed to the nearest millisecond in excel?

 

[xlbo]

very close:
MsgBox Format(Now(), "dd/mm/yyyy hh:mm:ss:ms")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Days: (Now-Date)
Hours: (Now-Date)*24
Minutes: (Now-Date)*24*60
Seconds: (Now-Date)*24*60*60
Miliseconds: (Now-Date)*24*60*60*1000

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

mm is ambiguous, so I'd use this:
Format(Now(), "dd/mm/yyyy hh:[!]nn[/!]:ss:ms")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zelgar]

Try using the Timer function. At the beginning of your macro put a line in of
Start=Timer
and at the end of your macro
Cells(?,?) = Timer - Start

This will give you how many seconds have past while running your macro. For the level of precision, a macro I just ran gave the results as 1.171875 (seconds).

 

[strongm]

ms" does not display milliseconds. I leave it as an exercise for the reader to figure out what is does display ...

And even if it could it wouldn't be at all accurate; the system clock used by Now() does not have millisecond accuracy.On most modern PCs the Windows system clocks are only accurate to about 15ms

The following should tell you the actual accuracy of your PC

[blue]Private Declare Function GetSystemTimeAdjustment Lib "kernel32" (lpTimeAdjustment As Long, lpTimeIncrement As Long, lpTimeAdjustmentDisabled As Long) As Long

Private Sub Command1_Click()
    MsgBox "Accuracy: " & GetTick & "ms"
End Sub

Public Function GetTick() As Double
    Dim a, b, c
    GetSystemTimeAdjustment a, b, c
    GetTick = b / 10000 ' for milliseconds
End Function[/blue]

there are higher resolution timers available in Windows, but do you really need ms accuracy? If so, then:

[blue]Option Explicit

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Public Sub Example()
     Dim StartTime As Currency
     Dim EndTime As Currency
     Dim TickFrequency As Currency
     
     QueryPerformanceFrequency TickFrequency
     
     QueryPerformanceCounter StartTime
     Sleep 100
     QueryPerformanceCounter EndTime
     
     MsgBox "Elapsed time: " & 1000 * (EndTime - StartTime) / TickFrequency & "ms" 'scaled to milliseconds
End Sub[/blue]

 

[PHV]

OOps, you're right strongm.
:ms stands for :MonthSeconds ...

 

[strongm]

PHV wins the prize

 

[stduc]

WOW - thanks guys - much for me to learn I see. I'll report back ASAP. Meanwhile - I need to get my ISP to stop blocking the notification emails from here as spam - grrrr.

 

[stduc]

Absolutely brill strongm - many many thanks - I had no trouble adapting your very clear code.