如何在VBA（Excel）中以毫秒为单位获取DateDiff值？

Question

我需要计算两个时间戳之差（以毫秒为单位）。 不幸的是，VBA的DateDiff函数不提供这种精度。 是否有任何解决方法？

Answer 1

你可以使用方法描述here如下： -

创建一个名为StopWatch 将下面的代码StopWatch类模块中一个新的类模块：

Private mlngStart As Long 
Private Declare Function GetTickCount Lib "kernel32"() As Long 

Public Sub StartTimer() 
    mlngStart = GetTickCount 
End Sub 

Public Function EndTimer() As Long 
    EndTimer = (GetTickCount - mlngStart) 
End Function 

您使用的代码如下：

Dim sw as StopWatch 
Set sw = New StopWatch 
sw.StartTimer 

' Do whatever you want to time here 

Debug.Print "That took: " & sw.EndTimer & "milliseconds" 

其他方法描述使用VBA定时器功能，但这只能精确到百分之一秒（厘秒）。

Answer 2

除了通过AdamRalph（GetTickCount()）中描述的方法，你可以这样做：

• 使用QueryPerformanceCounter()和QueryPerformanceFrequency() API函数
  How do you test running time of VBA code?
• 或者，对于环境，而无需访问的Win32 API（如VBScript）：
  http://ccrp.mvps.org/（查看“高性能定时器”的可安装COM对象的下载部分，它们是免费的。）

Answer 3

如果你只需要在Centiseconds中花费的时间，那么你就不需要TickCount API。您可以使用所有Office产品中存在的VBA.Timer方法。如果你想要去的微秒

Public Sub TestHarness() 
    Dim fTimeStart As Single 
    Dim fTimeEnd As Single 
    fTimeStart = Timer 
    SomeProcedure 
    fTimeEnd = Timer 
    Debug.Print Format$((fTimeEnd - fTimeStart) * 100!, "0.00 "" Centiseconds Elapsed""") 
End Sub 

Public Sub SomeProcedure() 
    Dim i As Long, r As Double 
    For i = 0& To 10000000 
     r = Rnd 
    Next 
End Sub 

Answer 4

的GetTickCount和性能计数器需要.. 对于millisenconds你可以用一些这样的事..

'at the bigining of the module 
Private Type SYSTEMTIME 
     wYear As Integer 
     wMonth As Integer 
     wDayOfWeek As Integer 
     wDay As Integer 
     wHour As Integer 
     wMinute As Integer 
     wSecond As Integer 
     wMilliseconds As Integer 
End Type 

Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME) 


'In the Function where you need find diff 
Dim sSysTime As SYSTEMTIME 
Dim iStartSec As Long, iCurrentSec As Long  

GetLocalTime sSysTime 
iStartSec = CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds 
'do your stuff spending few milliseconds 
GetLocalTime sSysTime ' get the new time 
iCurrentSec=CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds 
'Different between iStartSec and iCurrentSec will give you diff in MilliSecs 

Answer 5

您还可以使用=NOW()公式calcilated在细胞：

Dim ws As Worksheet 
Set ws = Sheet1 

ws.Range("a1").formula = "=now()" 
ws.Range("a1").numberFormat = "dd/mm/yyyy h:mm:ss.000" 
Application.Wait Now() + TimeSerial(0, 0, 1) 
ws.Range("a2").formula = "=now()" 
ws.Range("a2").numberFormat = "dd/mm/yyyy h:mm:ss.000" 
ws.Range("a3").formula = "=a2-a1" 
ws.Range("a3").numberFormat = "h:mm:ss.000" 
var diff as double 
diff = ws.Range("a3") 

Answer 6

道歉唤醒这个老帖子，但我得到了一个答案：
写这样的毫秒功能：

Public Function TimeInMS() As String 
TimeInMS = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2) 
End Function  

使用此功能在您的子：

Sub DisplayMS() 
On Error Resume Next 
Cancel = True 
Cells(Rows.Count, 2).End(xlUp).Offset(1) = TimeInMS() 
End Sub