Excel macro call does not work from timer, but does from another macro

Question

I am trying to get excel to run an update every XX seconds.

Sub StartTesting() 
       Call RunAll_ISP   'this call works 
End Sub

Sub macro_timer() 'This sub schedules next run in XX seconds.
    Dim ScanInterval
    ScanInterval = Format(ThisWorkbook.Sheets("Configuration").Range("B5").Value, "hh:mm:ss")
    Application.OnTime Now + TimeValue(ScanInterval), "RunAll_ISP", Schedule:=True  ' !this macro call throws error!
End Sub

If I run Start_testing macro, call works. If I run macro_timer macro, it waits desired number of seconds, and then throwsthis error:

Cannot run the macro "C:\location_of_file\filename.xlsm'!RunAll_ISP'. The macro may not be available in this workbook or all macros may be disabled

The settings for macros in the Trust center are set to "Enable VBA macros"

What am I doing wrong?

Where is the RunAll_ISP procedure located? Is it in a standard module? Is it declared Public? — Pᴇʜ
– Pᴇʜ, Commented Nov 5, 2021 at 12:17
All subs are in ThisWorkbook. Tried Public - made no difference — Eki
– Eki, Commented Nov 5, 2021 at 19:55

Pᴇʜ · Accepted Answer · 2021-11-08 06:59:15Z

1

Your problem cannot be reproduced. This simple Example works. So since your procedure StartTesting worked that means macros are definitely enabled, so the only option left is that your macro RunAll_ISP does not exist (or wrongly named).

Option Explicit


Public Sub RunAll_ISP()
    MsgBox "I run"
End Sub


Public Sub CreateTimer()
    Application.OnTime Now + TimeValue("00:00:05"), "RunAll_ISP", Schedule:=True
End Sub

answered Nov 8, 2021 at 6:59

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eki Over a year ago

I started with the blank book, and added the code above to it. However I got exactly the same result. Here is a screenshot: link. BTW I'm using Office 365

Eki Over a year ago

And here is the screenshot when running the RunAll_ISP : link

Pᴇʜ Over a year ago

@Eki because you put the code in ThisWorkbook and not into a Module! Als I asked in my first comment the code needs to be inserted into a standard (non class) module. So add a module and move the code there and it will work.

Eki Over a year ago

Thank you very much ! adding the module and having code there did work. I need to read about it more. for anyone with the same issue, here is a screenshot how it supposed to look like: link

Collectives™ on Stack Overflow

Excel macro call does not work from timer, but does from another macro

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related