3

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.

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?

Hugues Paquet Blanchette
  • 612
  • 3
  • 11
  • 24
Luke
  • 2,434
  • 9
  • 39
  • 64
  • Luke, Can you explain what it is that you try to achieve in more detail? Does this macro have to run in Excel or can you use something outside of excel (e.g. AutoHotKey)? – Robert Ilbrink May 04 '12 at 06:06
  • Basically, I want to simulate a CPU. Using Cells as Registers and Memory. Then it can run software, and a user can watch it working and see what's going on in all the registers. They can step through at their own pace, or they can clock it at a frequency of their choice... up to maybe 10-15Hz maximum. This particular Macro is supposed to just be the automatic clock... – Luke May 04 '12 at 10:07
  • I see, this probably requires Excel for the visualization of the registers and memory. – Robert Ilbrink May 04 '12 at 16:28

4 Answers4

3

As simple as that Application.Wait (Now + 0.000001) for a 1/10sec :

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
R3uK
  • 14,417
  • 7
  • 43
  • 77
1

Excel does not work in anything less than whole seconds.

There are a couple of issues with your code:

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() 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.

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.

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

Andrew Leach
  • 12,945
  • 1
  • 40
  • 47
  • I understand that t is not a floating point value. I made it an Integer for this because TimeValue() takes Integers as its parameters, so t had to be an Integer. If, indeed, there was a possiblity of using a Time which had millisecond accuracy, I would of course make t a Single. I don't mean to say that I do not believe what you are saying, but I do find it very hard to believe that Excel cannot schedule regular execution of what is pretty simple code to millisecond accuracy. Especially given the nanosecond accuracy of a modern x86 machine. – Luke May 04 '12 at 10:12
  • Wrong! Excel does work with less than a whole second! – R3uK Nov 30 '15 at 14:54
  • @R3uK Note when I wrote my answer. Things may well have changed since then. And you are welcome to write your own answer. – Andrew Leach Nov 30 '15 at 14:57
  • @AndrewLeach : already wrote an answer, but I definitly didn't saw that it was on this "original" and not on the "duplicate", my bad for that! ;) – R3uK Nov 30 '15 at 15:12
0

Use the Sleep API.

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
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • 1
    Sorry, I'm not sure that solves my problem. I want the Macro to run at regular increments defined in milliseconds. I may be wrong, but as far as I can tell, this will merely run once and sleep for so many milliseconds in the middle. – Luke May 04 '12 at 13:53
  • The last line of the sub is a recursive call to itself. So it sleeps for the given number of milliseconds...executes its code...then calls itself...sleeps for the given number of milliseconds...executes its code...etc. – mwolfe02 May 04 '12 at 13:56
  • The line `If f = 0 Then Exit Sub` provides a way to break out of the infinite loop. You would set the ActiveCell to 0, the same way you would break out of the timer in your original code. – mwolfe02 May 04 '12 at 13:58
  • Oh, I see. Sorry, I didn't see the recursion. Does sleep allow other processes in Excel to run whilst it is invoked? – Luke May 05 '12 at 16:26
  • I'm pretty sure it allows processes outside of Excel to run. I'm not sure about other threads within Excel. – mwolfe02 May 06 '12 at 13:24
  • Ahh. That could be a problem, then. – Luke May 06 '12 at 21:06
  • Excel is frozen while `sleep` runs, so if it's looping then it's frozen "forever". You'd be better off to use a simple `pause` sub in it's place, like: `Sub pause(sec): Dim tmr As Single: tmr = Timer: Do: DoEvents: Loop Until Timer >= tmr + sec: End Sub` (a 'one-liner') Since [`Timer`](https://learn.microsoft.com/office/vba/language/reference/user-interface-help/timer-function) has 10ms resolution, you can specify partial seconds. For example, `pause 1/3` will pause for *about* a third of a second. – ashleedawg Sep 11 '20 at 05:31
0

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)

Leon Rom
  • 537
  • 4
  • 6