milliseconds-timer returns Unexpected error 35010

Question

I have code in Excel VBA which I like to run several times a second.

Before I used this method :

Application.On Time Now + Timevalue("00:00:01")

Although this method can only handle seconds (and no milliseconds).

For that reason I replaced this timer with a milliseconds timer (see code below). Although since I integrated the milliseconds timer with my other Excel VBA code it sometimes gives this error: "Unexpected error 35010".

There are several reasons mentioned for this error:

VBA compile Error: unexpected error 35010

I did test the code on two computers and on both it returns the error. I also did test on Excel Pro Plus 2019 and Excel Pro Plus 2016. Both 64 bit versions. Both return the error.

Since I expected it was due to a corrupt file, I did made a new workbook and copied all VBA code into this workbook. I started with empty sheets and copied all formulas from the old workbook to the new workbooksheets. I did this twice although these files still return the error.

The error always occurs directly after I start the code. The error does not refer to a specific line of code (in yellow).

Somtimes the error occurs, while with exactly the same file and contents the error does not occur.

This is the code for the timer:

Option Explicit

Private Declare PtrSafe Function SetTimer Lib "user32" _
(ByVal hWnd As LongPtr, _
ByVal nIDEvent As LongPtr, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As LongPtr) As LongPtr

Private Declare PtrSafe Function KillTimer Lib "user32" _
(ByVal hWnd As LongPtr, _
ByVal nIDEvent As LongPtr) As Long

Private m_TimerID As LongPtr

'Note: The duration is measured in milliseconds.
' 1,000 milliseconds = 1 second
Public Sub StartTimer(ByVal Duration As Long)
    'If the timer isn't already running, start it.
    If m_TimerID = 0 Then
        If Duration > 0 Then
            m_TimerID = SetTimer(0, 0, Duration, AddressOf TimerEvent)
            If m_TimerID = 0 Then
                MsgBox "Timer initialization failed!"
            End If
        Else
            MsgBox "The duration must be greater than zero."
        End If
    Else
        MsgBox "Timer already started."
    End If
End Sub

Public Sub StopTimer()
    'If the timer is already running, shut it off.
    If m_TimerID <> 0 Then
        KillTimer 0, m_TimerID
        m_TimerID = 0
    Else
        MsgBox "Timer is not active."
    End If
End Sub

Public Property Get TimerIsActive() As Boolean
'A non-zero timer ID indicates that it's turned on.
    TimerIsActive = (m_TimerID <> 0)
End Property

This is example code how I use the timer:

Public CountSomething As Variant
Private timerRunning As Boolean


Sub UPDATECLOCK()


''Application.Calculation = xlManual


If Not timerRunning Then
            CountSomething = 0
            r = 0
            StartTimer 100
            timerRunning = True
End If
 

End Sub

Sub STOPCLOCK()


            StopTimer
            timerRunning = False



End Sub

Sub TimerEvent()


On Error GoTo ErrHandler

    'force code to run on the main thread instead of Windows api thread:
    If timerRunning Then Application.OnTime Now, "updateWorksheet"
    Exit Sub

ErrHandler:
    Debug.Print Err.Number

End Sub

Sub updateWorksheet()
    
    'call code to process here
    'or this example code in this case:
    
    CountSomething = CountSomething + 1
    Worksheets("Sheet1").Cells(2, 9).Value = CountSomething
    
    'write 1 in cel A1 to stop code running:
    knop = ActiveSheet.Cells(1, 1)

    If knop = 1 And timerRunning Then
        StopTimer
        timerRunning = False
    End If
    
    
End Sub

Does anyone know what the cause of the error can be?

Thanks a lot!

If you are working with miliseconds, it's easier to do something like `(TimeValue("00:50:00") / 1000)`. As example `Application.Wait Now + (TimeValue("00:50:00") / 1000)` will pause the code for 3000 miliseconds (3 seconds) — Foxfire And Burns And Burns, Sep 20 '20 at 12:23
@ Foxfire And Burns And Burns Thank you, although the Excel application is frozen while using Wait which is not feasible for me. — user2165379, Sep 20 '20 at 12:57
Since your `Timer` function only has one second accuracy, are you running on a Mac? — Ron Rosenfeld, Sep 20 '20 at 13:08
@ Ron Rosenfeld Yes indeed, that is the reason I try to use this Windows API solution. I am running on Windows 10 Pro 64 bit with Excel Pro Plus 64 bit. — user2165379, Sep 20 '20 at 13:12
Didn't know you could use Windows API on the Mac. I think you can use `evaluate("=now()")` to get msec accuracy. On Windows, I generally use the hiRes timer which uses tick counts to obtain the timing interval. Examples abound on the internet. — Ron Rosenfeld, Sep 20 '20 at 13:21
@ Ron Rosenfeld Sorry, I do not run a Mac. Do you think it is possible use the hiRes timer to call a loop in Excel VBA every 200 ms? Or does it only measure the duration of a proces? — user2165379, Sep 20 '20 at 13:28
Yes, but the VBA timer function should have sufficient resolution to do that. — Ron Rosenfeld, Sep 20 '20 at 13:41
Not sure how you can use either to generate an interrupt to trigger the VBA loop. Now that I think of it you probably have to do some fancy Windows API programming to generate that interrupt. Otherwise you would have the time overhead of checking to see if the timer had counted off your 200 milliseconds. Perhaps in your application that overhead is insignificant. — Ron Rosenfeld, Sep 20 '20 at 13:44
@ Ron Rosenfeld As far as I understand, would this not be a replication of my current solution which calls the Windows API? — user2165379, Sep 20 '20 at 13:53
I've never used the timer that you're using. I they use the VBA timer function or the high-res timer which runs on tick counts. — Ron Rosenfeld, Sep 20 '20 at 14:02
@ Ron Rosenfeld I think the overhead is very important to me. I suppose the result is that I can still modify the sheet while running the code (opposite to a continious loop). If I understand correct you mean a hiRes timer like this: https://rotchvba.wordpress.com/2016/12/12/a-high-resolution-timer-in-vba/ Although I am not familiair with Windows API programming to make the interrupt. Can I still modify the sheet during the interrupt from the Windows API you mentioned? Do you mean to run the code continious with an interrupt from the Windows API? A problem could be that my loop is not constant — user2165379, Sep 20 '20 at 14:22

score 0 · Answer 1 · answered Sep 20 '20 at 13:52

0

There are some conceptual errors in the code: using OnTime Now ... is equivalent to immediately calling updateWorksheet. The timerRunning variable is superfluous, you can use the TimerIsActive function. The code for updateWorksheet can (should) be inserted into the TimerEvent and there is no reason to check if the timer is active; the routine is called only if the timer is active. This code is equivalent

Sub UPDATECLOCK()

    If Not TimerIsActive Then
        CountSomething = 0
        r = 0
        StartTimer 100
    End If
 
End Sub

Sub STOPCLOCK()

    StopTimer

End Sub

Sub TimerEvent()

    CountSomething = CountSomething + 1
    Worksheets("Sheet1").Cells(2, 9).Value = CountSomething
    'write 1 in cel A1 to stop code running:
    If ActiveSheet.Cells(1, 1) = 1 Then 'why ActiveSheet?
        StopTimer
    End If
    
End Sub

answered Sep 20 '20 at 13:52

Zer0Kelvin

334
2
7

The OnTime Now forces the code to run on the main thread instead of Windows api thread. This is a huge improvement since I initially got many errors since Excel doesn't like to be timed by an external timer. With OnTime Now all the code which is called runs on the same thread (initiated by Excel itself). I agree the code could be organised a bit different although this would not solve the unexpected error 35010. – user2165379 Sep 20 '20 at 14:04
I have seen; but can't you just ignore the error? what problems does it give you? – Zer0Kelvin Sep 20 '20 at 15:56
Errors 1004 and 50290. I can suppress the errors but I prefer to prevent them. – user2165379 Sep 20 '20 at 16:32

milliseconds-timer returns Unexpected error 35010

1 Answers1