How to deal with "runtime error 50290" when using SetTimer API?

Question

I've run into this error when trying to make a stopwatch timer in Excel. Here's a simple test code. Create an empty Excel workbook with a button. And assign a macro to it:

Sub Button1_Click()
    TimerID = SetTimer(0&, 0&, 0.5 * 1000&, AddressOf TimerProc)
End Sub

Also, add this code to the module:

Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    Range("A1") = "test"
End Sub

Then click the button and start clicking random cells. Shortly you will get the following window:

And after that Excel crashes.

What am I doing wrong?
How to handle this?

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

I was able to solve it with

On Error Resume Next

at the beginning of TimerProc. Some more (in Russian) or less related links.

Or probably even better:

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    On Error GoTo BeforeExit
    Range("A1") = "test"
BeforeExit:
End Sub

edited May 23 '17 at 12:29

Community

1
1

answered Sep 19 '15 at 14:32

x-yuri

16,722
15
114
161

1

What might be happening was that you had clicked a cell and so Excel was in edit mode which means Excel stops & waits for you to enter something. This would probably block the code from writing to 'Range("A1")` and create the error. – ChipsLetten Sep 19 '15 at 20:29
Related: [Limitation of Asynchronous Programming to the Excel Object Model](https://support.microsoft.com/en-gb/help/2800327/limitation-of-asynchronous-programming-to-the-excel-object-model) – Greedo Sep 08 '19 at 15:00

score 0 · Answer 2 · answered Aug 13 '20 at 10:05

I found using OnTime with current time will cause your code to run on the main thread immediately or fail. I found this better because you only need to trap the OnTime error.

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal 
dwTimer As Long)
    On Error GoTo BeforeExit
    Application.OnTime Now, "updateWorksheet"
BeforeExit:
End Sub

Sub updateWorksheet
    Range("A1") = "test"
End Sub

How to deal with "runtime error 50290" when using SetTimer API?

2 Answers2

Linked