blocks|key|1326630|text|您可以使用here描述的方法，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1326631|创建一个名为StopWatch的新类模块，将以下代码放入StopWatch类模块：|style|CODE|1326632|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|code-block|syntax|javascript|1326633|您可以按如下方式使用代码：|1326634|Dim+sw+as+StopWatch
Set+sw+=+New+StopWatch
sw.StartTimer

'+Do+whatever+you+want+to+time+here

Debug.Print+"That+took:+"+&+sw.EndTimer+&+"milliseconds"|1326635|其他方法描述了VBA计时器功能的使用，但这只能精确到百分之一秒(厘米秒)。|1326636|entityMap|0|LINK|mutability|MUTABLE|url|http://www.tek-tips.com/faqs.cfm?fid=2292^0|5|4|0|0|6|9|S|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@$A|15|B|16|F|G]|$A|17|B|18|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|19|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1A|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1B|8|@]|9|@]|C|$K|L]]|$1|Q|3|R|5|6|7|1C|8|@]|9|@]|C|$]]|$1|S|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

You could use the method described <a href="http://www.tek-tips.com/faqs.cfm?fid=2292" rel="noreferrer" title="here">here</a> as follows:-

Create a new class module called <code>StopWatch</code>
Put the following code in the <code>StopWatch</code> class module:

<pre><code>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
</code></pre>

You use the code as follows:

<pre><code>Dim sw as StopWatch
Set sw = New StopWatch
sw.StartTimer

' Do whatever you want to time here

Debug.Print "That took: " &amp; sw.EndTimer &amp; "milliseconds"
</code></pre>

Other methods describe use of the VBA Timer function but this is only accurate to one hundredth of a second (centisecond).

blocks|key|1326703|text|如果您只需要以厘米为单位的时间，那么您不需要TickCount应用编程接口。您可以只使用所有Office产品中都存在的VBA.Timer方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1326704|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|code-block|syntax|javascript|1326705|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you just need time elapsed in Centiseconds then you don't need the TickCount API. You can just use the VBA.Timer Method which is present in all Office products.

<pre><code>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&amp; To 10000000
 r = Rnd
 Next
End Sub
</code></pre>

blocks|key|1541874|text|如果要进行微秒级操作，则需要GetTickCount和性能计数器。对于毫秒，你可以使用像这样的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541875|'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+%2B+sSysTime.wMilliseconds
'do+your+stuff+spending+few+milliseconds
GetLocalTime+sSysTime+'+get+the+new+time
iCurrentSec=CLng(sSysTime.wSecond)+*+1000+%2B+sSysTime.wMilliseconds
'Different+between+iStartSec+and+iCurrentSec+will+give+you+diff+in+MilliSecs|code-block|syntax|javascript|1541876|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

GetTickCount and Performance Counter are required if you want to go for micro seconds..
For millisenconds you can just use some thing like this.. 

<pre><code>'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
</code></pre>

blocks|key|1541954|text|您也可以使用在单元格中计算的=NOW()公式：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1541955|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()+%2B+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")|code-block|syntax|javascript|1541956|entityMap^0|E|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can also use <code>=NOW()</code> formula calcilated in cell:

<pre><code>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")
</code></pre>

blocks|key|1542003|text|很抱歉唤醒这篇老帖子，但我得到了一个答案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1542004|像这样写一个毫秒的函数：|1542005|Public+Function+TimeInMS()+As+String
TimeInMS+=+Strings.Format(Now,+"HH:nn:ss")+&+"."+&+Strings.Right(Strings.Format(Timer,+"#0.00"),+2)+
End+Function++++|code-block|syntax|javascript|1542006|在您的sub中使用此函数：|1542007|Sub+DisplayMS()
On+Error+Resume+Next
Cancel+=+True
Cells(Rows.Count,+2).End(xlUp).Offset(1)+=+TimeInMS()
End+Sub|1542008|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Apologies to wake up this old post, but I got an answer: 
Write a function for Millisecond like this: 

<pre><code>Public Function TimeInMS() As String
TimeInMS = Strings.Format(Now, "HH:nn:ss") &amp; "." &amp; Strings.Right(Strings.Format(Timer, "#0.00"), 2) 
End Function 
</code></pre>

Use this function in your sub: 

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

blocks|key|1326884|text|如果Timer()精度足够高，那么您可以通过将日期和时间与毫秒相结合来创建时间戳：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1326885|Function+Now2()+As+Date

++++Now2+=+Date+%2B+CDate(Timer+/+86400)

End+Function|code-block|syntax|javascript|1326886|要计算两个时间戳之间的差值(以毫秒为单位)，可以减去它们：|1326887|Sub+test()

++++Dim+start+As+Date
++++Dim+finish+As+Date
++++Dim+i+As+Long

++++start+=+Now2
++++For+i+=+0+To+100000000
++++Next
++++finish+=+Now2
++++Debug.Print+(finish+-+start)+&+"+days"
++++Debug.Print+(finish+-+start)+*+86400+&+"+sec"
++++Debug.Print+(finish+-+start)+*+86400+*+1000+&+"+msec"

End+Sub|1326888|对我来说，这种方法的实际精度约为8毫秒(BTW+GetTickCount更差-+16毫秒)。|1326889|entityMap^0|2|7|0|0|0|0|O|C|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

If <code>Timer()</code> precision is enough then you can just create timestamp by combining date and time with milliseconds:

<pre class="lang-vb prettyprint-override"><code>Function Now2() As Date

 Now2 = Date + CDate(Timer / 86400)

End Function
</code></pre>

To calculate the difference between two timestamps in milliseconds you may subtract them:

<pre class="lang-vb prettyprint-override"><code>Sub test()

 Dim start As Date
 Dim finish As Date
 Dim i As Long

 start = Now2
 For i = 0 To 100000000
 Next
 finish = Now2
 Debug.Print (finish - start) &amp; " days"
 Debug.Print (finish - start) * 86400 &amp; " sec"
 Debug.Print (finish - start) * 86400 * 1000 &amp; " msec"

End Sub
</code></pre>

Actual precision of that method is about 8 msec (BTW <code>GetTickCount</code> is even worse - 16 msec) for me.

I need to calculate the difference between two timestamps in milliseconds.
Unfortunately, the DateDiff-function of VBA does not offer this precision.
Are there any workarounds?

How to get a DateDiff-Value in milliseconds in VBA (Excel)?

我需要以毫秒为单位计算两个时间戳之间的差值。不幸的是，VBA的DateDiff函数没有提供这种精度。有什么变通方法吗？

问如何在VBA (Excel)中获取以毫秒为单位的DateDiff值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA (Excel)中获取以毫秒为单位的DateDiff值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA (Excel)中获取以毫秒为单位的DateDiff值？
EN