Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel time in milliseconds 2

Status
Not open for further replies.
stduc

stduc

Programmer
Nov 26, 2002
1,903
GB
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?
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote

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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
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

Code: 
[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:

Code: 
[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]

 
Upvote 0 Downvote
OOps, you're right strongm.
:ms stands for :MonthSeconds ...
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Absolutely brill strongm - many many thanks - I had no trouble adapting your very clear code.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
544
remeng
remeng
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
252
Andrzejek
Andrzejek
sal21
  • Question
scrping in listbox in a web page
Replies
0
Views
166
sal21
sal21
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
322
huiettcm
huiettcm
f0rg3tfu1
  • Locked
  • Question
VBA Error in Excel
Replies
11
Views
323
combo
combo

Part and Inventory Search

Sponsor

Back
Top