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:
- 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.