vba VB Excel 宏毫秒计时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10438338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB Excel Macro Millisecond Timing
提问by Luke
I am trying to write a macro in VB for an Excel spreadsheet which is executed in specific intervals which are defined by a value (in Hz) contained in the spreadsheet itself. My problem is that the code I found for accomplishing an automatic macro in this way appears to only allow for second accuracy, so any frequencies above 1Hz are not possible. I would like to be able to get up to around 10Hz if possible, which would require millisecond accuracy.
我正在尝试在 VB 中为 Excel 电子表格编写一个宏，该宏以特定间隔执行，这些间隔由电子表格本身中包含的值（以 Hz 为单位）定义。我的问题是我找到的以这种方式完成自动宏的代码似乎只允许第二精度，因此任何高于 1Hz 的频率都是不可能的。如果可能的话，我希望能够达到 10Hz 左右，这需要毫秒精度。
I am new to VB, so I do not know much about it. Here is the code:
我是VB的新手，所以我对它了解不多。这是代码：
Sub Macro2()
Range("K1").Select
Dim f As Single
f = ActiveCell.Value
Dim t As Integer
t = 1 / f
dTime = Now + TimeSerial(0, 0, t)
Application.OnTime dTime, "Macro2"
Range("J1").Select
Dim c As Integer
c = ActiveCell.Value
c = c Xor 1
ActiveCell.Value = c
End Sub
Is there any way to get Millisecond accuracy using this method?
有没有办法使用这种方法获得毫秒精度？
采纳答案by Andrew Leach
Excel does not work in anything less than whole seconds.
Excel 在不到整秒的时间内不起作用。
There are a couple of issues with your code:
您的代码有几个问题：
You define Dim t As Integerand go on to assign t = 1 / fwhich means that for any value of fgreater than one, twill be zero (because it's constrained to be an integer).
您定义Dim t As Integer并继续分配t = 1 / f，这意味着对于任何f大于 1 的值，t都将为零（因为它被限制为整数）。
TimeValue()can only work in whole seconds, so it doesn't matter whether tis a fraction or not because TimeValue()will discard any fractional part.
TimeValue()只能在整秒内工作，所以是否t是分数并不重要，因为TimeValue()会丢弃任何小数部分。
The macro will run with tbeing zero, but it's uncontrolled and irregular. I suspect that is part of the reason that Excel VBA doesn't work in milliseconds: the execution of VBA code just isn't accurate enough.
宏将以t零运行，但它不受控制且不规则。我怀疑这是 Excel VBA 无法以毫秒为单位工作的部分原因：VBA 代码的执行不够准确。
Unfortunately, because VBA can't work in milliseconds, I can't suggest a way of making it do so.
不幸的是，因为 VBA 不能在几毫秒内工作，所以我不能建议一种方法来让它这样做。
回答by R3uK
As simple as that Application.Wait (Now + 0.000001)for a 1/10sec :
就像Application.Wait (Now + 0.000001)1/10 秒一样简单：
Sub Macro2()
' Macro2 Macro
Range("C1").FormulaR1C1 = "1"
Application.Wait (Now + TimeValue("0:00:01")) '1sec
Range("C1").FormulaR1C1 = "2"
Application.Wait (Now + 0.000001) '1/10sec
Range("C2").Select
Sleep (100)
End Sub
回答by mwolfe02
Use the SleepAPI.
使用SleepAPI。
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Macro2()
Range("K1").Select
Dim f As Single
f = ActiveCell.Value
If f = 0 Then Exit Sub
Dim t As Long
t = 1000 / f
Sleep t
Range("J1").Select
Dim c As Integer
c = ActiveCell.Value
c = c Xor 1
ActiveCell.Value = c
Macro2
End Sub
回答by Leon Rom
if there is no need to use the exact value of milliseconds, but only "as soon as possible", you can use TimeSerial(0, 0, 0)
如果不需要使用毫秒的确切值，而只是“尽快”，则可以使用TimeSerial(0, 0, 0)
