Cannot run the macro

Question

I have encountered a problem in the macro below

Sub RefreshAction()
    Range("b7").Select
    Application.Run "RefreshCurrentSelection"
    Application.OnTime (Now() + TimeValue("00:00:05")), "thisworkbook.Action"
End Sub

The cell refreshes when I run the macro the first time but I get the error message immediately after

Message: Cannot run the macro "C\Desktop\XYZ.xlsm'!thisworkbook.Action'. The macro may not be available in this workbook or all macros may be disabled.

I have already gone through "Trust Center->Trust Center Settings->Macro Settings->Enable all macros and it didn't work.

The "Trust access to VBA project object model" box is also clicked.

Answer 1

See the absolute reference for more details : CPearson OnTime

First issue, you need to store the time that you'll input in your OnTime method to be able to stop it. (Here I declared a Public TimeToRun As Date)

Second Point To use the OnTime method continuously, you need to reset the timer at the end of your timed procedure (here RefreshAllStaticData).

So your whole code should look like this :

Public TimeToRun As Date 'so that TimeToRun can be used in both the functions

Sub RefreshAction()
    Range("b7").Select
    Application.Run "RefreshCurrentSelection"
    DoEvents
    'Store the next date of execution in TimeToRun
    TimeToRun = Now() + TimeValue("00:00:05")
    'Launch the next OnTime
    Application.OnTime TimeToRun, "RefreshAllStaticData"
End Sub


Sub RefreshAllStaticData()

'--++-- Place your code here, as it is now --++--

'----Call RefreshAction to reset the OnTime method
'---------to another 5 seconds and keep "looping"
RefreshAction

End Sub


Sub Kill_OnTime()
'Launch this to stop the OnTime method
Application.OnTime _
    earliesttime:=TimeToRun, _
    procedure:="RefreshAllStaticData", _
    schedule:=False
End Sub

Answer 2

First of all, here is a snapshot of the error you get when you attempt to run OnTime from a worksheet (not a module) as I will explain. I was getting this error too and trying to figure out why.

It looks like a security error, but in this case it isn't exactly a normal security error.

To run code on a timer you have to add it to a VBA module. Go to the VisualBasic editor and right click the VBAProject (book). In Excel it looks like the following:

Once the module is added you add your timer code there.

Since you want to call RefreshAction every 5 seconds you would do something like the following:

Sub StartProcess()
    Debug.Print Now()
    Application.OnTime Now() + TimeValue("00:00:05"), "RefreshAction", Schedule = True
End Sub

Sub RefreshAction()
    Application.EnableEvents = True
    Debug.Print Now() + TimeValue("00:00:05")
    Application.OnTime Now() + TimeValue("00:00:05"), "RefreshAction", Schedule = True
End Sub

I'll let you add the code that you want it to do each time in the RefreshAction subroutine.

Here's what it will look like in the Module. Make sure yours shows that it is in a module as it does in the image:

Also, I found it to be quite flaky. If you have anything even slightly wrong in the OnTime call it will fail silently. Copy my code (I tested it) and try it first. Once it runs, just add your code to the RefreshAction sub.

StartProcess()

Run the StartProcess to start the thing going.

Additionally Odd Thing

After I added that Module, I still had my code in the Worksheet and I went back and attempted to run it to see the error again and the odd thing is that once the code is in the Module you won't get the error from the Worksheet any more. It's probably referencing the code in the Module now.