0

I am looking for a solution to call a MySub every 100 milliseconds. MySub contains a loop with a variable length (and variable execution time).

Currently I can do this every second with this code:

Sub UPDATECLOCK()

Call MySub

NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "UPDATECLOCK"
End sub

I read in the first answer in this post that it is not possible:

VB Excel Macro Millisecond Timing

Although in the second answer this solution is given:

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

If I understand correctly Application.Wait is added to let Excel wait for several milliseconds. https://learn.microsoft.com/en-us/office/vba/api/excel.application.wait Although the Excel application is frozen while using Wait which is not feasible for me.

Maybe an alternative could be to measure the time my loop takes to execute on the system clock.

I found this website https://www.aeternus.sg/best-millisecond-timer-vba/ with this code:

' Performance counter API's
#If VBA7 And Win64 Then
    'for 64-bit Excel
    Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
    'for 32-bit Excel
    Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If


'Purpose: Measure elapsed time in milliseconds
Sub TimeACode()
    Dim curFrequency As Currency
    Dim curStartPerformanceCounter As Currency
    Dim curEndPerformanceCounter As Currency
    Dim lgResult As Long

    'obtain no: of counts per second
    lgResult = QueryPerformanceFrequency(curFrequency)
    If lgResult > 0 Then
        Debug.Print "Frequency: " & curFrequency
    End If

    'measure start count
    lgResult = QueryPerformanceCounter(curStartPerformanceCounter)
    If lgResult > 0 Then
        Debug.Print "Start Count: " & curStartPerformanceCounter
    End If
    

    '*****************************************
    'Insert the code to measure elapsed time
    '*****************************************
    
    
    'measure end count
    lgResult = QueryPerformanceCounter(curEndPerformanceCounter)
    If lgResult > 0 Then
        Debug.Print "End Count: " & curEndPerformanceCounter
    End If

    'measure elapsed time
    Debug.Print "Elapsed time (ms): " & (curEndPerformanceCounter - curStartPerformanceCounter) / curFrequency
End Sub

I do not understand this code very well. Although it seems to me it counts the processor ticks since the last reboot of the system. Would it be possible to alter this code in a way that it will call MySub after a certain number of ticks of the processor?

If I call this function on my system: lgResult = QueryPerformanceFrequency(curFrequency) The function returns 1000. Does this mean my processor makes 1000 ticks per second? Would it be possible to call MySub after every 100 ticks?

Thanks a lot!

user2165379
  • 445
  • 4
  • 20
  • 1
    Sounds like an [XY Problem](http://xyproblem.info) to me. What's the problem you are trying to solve? – IInspectable Aug 08 '20 at 13:16
  • Someone asked about the same question back in 2013 here: https://stackoverflow.com/questions/20269844/how-to-make-safe-api-timers-in-vba it's a long story, i have not read it all, because i do not think one should do something that often (more then once a second) in Excel. – Luuk Aug 08 '20 at 13:39
  • @IInspectable I would like to call MySub every 100ms. – user2165379 Aug 08 '20 at 14:08
  • @Luuk thank you. So I understand it is risky to call such a Windows API function from Excel VBA. – user2165379 Aug 08 '20 at 14:11
  • Excel is not the tool do do something like this. You better write something in C#, or so. Those tools are also capable of reading/writing Excel. But i do not know what you are trying to do every 100ms. – Luuk Aug 08 '20 at 14:13
  • @Luuk I am processing a stream of incoming data. Since I have all my legacy code in Excel VBA it is hard to switch. Are there any other languages you would suggest? – user2165379 Aug 08 '20 at 14:16
  • Excel is not a language , it's a tool to show data. – Luuk Aug 08 '20 at 14:27
  • 1
    If you need to stream data, why don't you *wait* for the data to arrive, rather than blindly polling for data every 100ms? As I said, this is an XY Problem. The X you are trying to solve is responding to incoming data. – IInspectable Aug 08 '20 at 16:07
  • @IInspectable thanks, good point, although I want evenly time-intervals with logged data. Ofherwise I could make a continious Do While-loop as well. Although such a loop would make the logging uneven since MySub contains a loop with variable length. I can make the logging dependent on the number of loops during a minute although this has many complications in my code. Optimal would be a timer in milliseconds although maybe I should accept this is not possible in Excel VBA. – user2165379 Aug 08 '20 at 16:53
  • It's not at all obvious, why you think that your data *processing* needs to run at any given pace. If your source data isn't timestamped already you can add a timestamp during processing, and have it available when you later need to display the data. That allows you to run your data processing at any time when new data has arrived. – IInspectable Aug 08 '20 at 18:59
  • @ llinspectable MySub also contains code which does other actions additional to processing the incoming data. I don't want MySub to become responsive to the incoming data. – user2165379 Aug 09 '20 at 08:26
  • @IInspectable With my current Sub UPDATECLOCK() it is also possible to interact with the worksheet since MySub takes less than a second to execute. This functionality is important to me. I am losing this functionality if I use a Do While- or For Next-loop. Adding DoEvents will make the code vulnerable to halt. So it seems it would be optimal for me if I could use the sub UPDATECLOCK() with milliseconds instead of seconds. – user2165379 Aug 09 '20 at 13:41
  • I'm not proposing to exchange one way of polling with another way of polling. I'm suggesting that you replace your polling with an event-based architecture. Though that point is probably tough to drive home. VBA encourages a strictly imperative way of writing (and thinking about) code. If you haven't had any previous exposure to an event-based architecture, you're likely locked into one way of thinking about potential solutions, that completely shadows a far superior approach. – IInspectable Aug 09 '20 at 13:59
  • @IInspectable I appreciate your suggestion and I have thinked about it, although 1) the current set-up works fine, apart from the millisecond need. 2) MySub reads currently several datafeeds every time it is called. 3) I dont want to be responsive to the feed. 4) potentially it can become difficult to interact I suppose if one of the streams sends a lot of data during a certain period. – user2165379 Aug 09 '20 at 14:32
  • @ Luuk I have tried to run the code in the link you mentioned although I do not succeed yet. I don't see the pop-up in the code from Goldmeier. If I add the code from Hefferman I get an error Ambigious name : TimerProc. Before I try further, did you provide the link as a solution for my issue to have "Application.OnTime NextTick," in milliseconds or is it code to measure time in milliseconds? Thanks! – user2165379 Aug 11 '20 at 07:36

0 Answers0