VB Excel宏毫秒计时 [英] VB Excel Macro Millisecond Timing

问题描述

我正在尝试在VB中为Excel电子表格编写一个宏，该宏以特定的时间间隔执行，该间隔由电子表格本身中包含的值(以Hz为单位)定义.我的问题是，我发现以这种方式完成自动宏的代码似乎只允许秒精度，因此1Hz以上的任何频率都是不可能的.如果可能的话，我希望能够达到10Hz左右，这需要毫秒级的精度.

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的新手，所以对此我不太了解.这是代码:

I am new to VB, so I do not know much about it. Here is the code:

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?

推荐答案

Excel在不到整秒的时间内无法正常工作.

您的代码有两个问题:

您定义Dim t As Integer并继续分配t = 1 / f，这意味着对于f的任何大于1的值，t都将为零(因为它被限制为整数).

You define Dim t As Integer and go on to assign t = 1 / f which means that for any value of f greater than one, t will be zero (because it's constrained to be an integer).

TimeValue()只能在整秒内工作，因此t是否为分数都无关紧要，因为TimeValue()会舍弃任何分数部分.

TimeValue() can only work in whole seconds, so it doesn't matter whether t is a fraction or not because TimeValue() will discard any fractional part.

该宏将在t为零的情况下运行，但它不受控制且不规则.我怀疑这是Excel VBA在几毫秒内无法运行的部分原因:VBA代码的执行不够准确.

The macro will run with t being 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.

不幸的是，由于VBA不能在毫秒内工作，因此我无法提出一种使之可行的方法.

Unfortunately, because VBA can't work in milliseconds, I can't suggest a way of making it do so.

这篇关于VB Excel宏毫秒计时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！