Application.OnTime not works if called with Evaluate function in VBA

43 Views Asked by At

The target is to reset public variables after calling one or more of the several functions from a sheet. If the process flows in a normal way resetting could be after finishing every functions which detection is not a simply way. Neither if there is any error occurs during run-time. So the idea was wait a short time and reset public variables, indifferentely of the results of the calling. Till now the only way i found, to create a button in a Userform and manually reset the variables which is not an ideal solution.

This issue seems a very simple one, but can't get to an acceptable workaround.

The attempt was to call nmn() as Function, it doesn't run the OnTime (no errors).

These are the Subs in a Standard Module which show the weird execution of the OnTime method.

Public val_called(1 to 7) as Integer, flags(1 to 7) as Integer

Sub reset_all()
Debug.Print Now, "reset"
For i = 1 To UBound(val_called)
  val_called(i) = 0
  flags(i) = True
Next i
End Sub

Sub nmn()
'Set my = New Class1
Application.OnTime Now + TimeValue("0:0:2"), "reset_all"
Debug.Print "we evaluate"
'my.reset_allcall
End Sub

Sub m1()
'Set my = New Class1
Evaluate ("filename.xlsm!nmn()")
'nmn
End Sub

The issue is the following:

  1. If start nmn() sub then the OnTime works and the printout
we evaluate
2024.02.18. 14:49:01        reset

2.If start from m1() sub the OnTime doesn't work and seems it is (nmn() sub) called two times immediately (there is no delay between the print of the 2 lines). The printout

we evaluate
we evaluate

SIDENOTE in other posts i found that evaluate is called with only the name of the sub like this: Evaluate "nmn()" which not results anything without error. It requires the name of the workbook like Evaluate "filename.ext!nmn()"

Disregarding the original target it is annoying that after a simple call why not executed the OnTime sub, and why the called Sub run two times.

I tried to place the two Subs into different modules, with no avail.

What did i miss, or how can solve to get OnTime to work if it is called by Evaluate?

This workaround is because the function will be a custom excel function called from a sheet, and the direct invoke of the OnTime method not avail. I tried create a Class also and invoke it but same result.

0

There are 0 best solutions below