Search code examples
excelvbatimercall

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

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?

Solution

  • 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