I had some quirky problems in some coding using Application.OnTime.
I think I found a fix. I am not sure I understand what’s going on. So I thought I would post the fixes. Maybe someone has an idea what’s going on. If not, someone might find it useful to see the fixes I found.
I had a couple of problems.
_1 ) passing variables ByRef was not working. That was quite easy to fix.
_2) I got various errors, mostly that the macro to be scheduled could not be found. Occasionally other errors. I don’t think the given error was particularly meaningful. The fix was the only thing linking all the problems. That is to say, it solved all the problems.
Fix _ 1)
The fix to _1) was simple. The scheduled time needs to be set to at least a second in the future……. ….. ( Before you ask … why I often use Application.OnTime Now(), "…… ….’" ( which starts immediately after the scheduling macro ) rather than just adding the coding to the end of the main scheduling macro ??? – the answer to that is that it is another good tip: That is one of the best universal bug fixes I know. That overcomes many bugs in VBA : Sometimes things strangely don’t work as they should in a main macro, particularly if they are to do with getting other things up like shapes, other windows, forms , etc.. They will often work if you schedule them immediately after the main macro )
Fix _ 2)
If you are using module level variables in the argument of the Application.OnTime , then you need to qualify them explicitly, especially if they are Private variables.
… like
Code: Select all
Module1.MyVariable Otherwise, no problem, maybe someone else might find the fixes useful.
Alan
_.____________________________
Here is a set of demo macros to help make it a bit more clear what I am talking about
At the start is a module level variable section.
( The module code name of the module you paste all the coding into, needs to be Module1 for the demo to work ) Three variables are there, in that module level variable section.
Then there are 4 Main scheduling macros, which try to use those variables in the variable argument part of an Application.OnTime code line.
Each of those 4 macros try to schedule one of two macros: Those two macros are very similar. The main difference is that one takes the variables ByRef , the other takes the variables by ByVal
Inside each of the 2 scheduled macros is a similar Debug.Print code line which prints out the value of the variables that have been taken in
In addition , the 4 main scheduling macros also finally call a simple sub routine, Sub WtchaGotModScopeVariables() which Debug.Prints the value in the module level variables after the main scheduling macro is finished.
So each of the 4 main scheduling macros should produce two lines of Debug.Print results:
The first line tells us if the variables have been taken in as they should have been ,
The second line tells us if the variables have been changed , which we would only expect in the case of ByRef
The first two main macros are the ones looking at scheduling a ByVal use of the variables.
Macro 1 does not error, but the first line of the results show that it has not worked, at least for the Private variables .
The second line of the results is as expected – no change because we did it by ByVal
11 0 0
11 12 13
Macro 2 gets it right, both output lines,.. thanks to the Fix _ 2)
21 22 23
21 22 23
Those two lines are showing that the variables were taken into the scheduled macro, ( Sub CalledByVal( … ) ) , and the variables have not been changed , as we expect when using ByVal
The second two main macros are the ones looking at scheduling a ByRef use of the variables.
Macro 3 does not work, ( or more precisely line 3 causes an error)
Macro 4 uses both fixes to get it to work and give the correct result. The results show that the variables were taken into the scheduled macro, ( Sub CalledByRef( … ) ) , and the variables have been changed , as we expect when using ByRef
41 42 43
2041 2042 2043
Ref:
https://stackoverflow.com/questions/314 ... 2#59812342" onclick="window.open(this.href);return false;
https://tinyurl.com/v24hmgz" onclick="window.open(this.href);return false;
Code: Select all
Option Explicit ‘ https://tinyurl.com/v24hmgz
' Module scope variable code section
Public Eye As Long
Private Jay As Long
Dim KEh As Long
' Main scheduling macro 1
Sub ByVal_GEhtitsAppObj_1()
1 Application.OnTime Now(), "'CalledByVal Eye, Jay, KEh'"
Let Eye = 11: Let Jay = 12: Let KEh = 13
Application.OnTime Now() + TimeValue("00:00:01"), "'WtchaGotModScopeVariables'"
End Sub
' Main scheduling macro 2
Sub ByVal_GEhtitsAppObj_2()
2 Application.OnTime Now(), "'CalledByVal Module1.Eye, Module1.Jay, Module1.KEh'" '_--- Fix _ 2) Module1.MyVariable
Let Eye = 21: Let Jay = 22: Let KEh = 23
Application.OnTime Now() + TimeValue("00:00:01"), "'WtchaGotModScopeVariables'"
End Sub
' Main scheduling macro 3
Sub ByRef_GEhtitsAppObj_3()
3 Application.OnTime Now(), "'CalledByRef Eye, Jay, KEh'"
Let Eye = 31: Let Jay = 32: Let KEh = 33
Application.OnTime Now() + TimeValue("00:00:01"), "'WtchaGotModScopeVariables'"
End Sub
' Main scheduling macro 4
Sub ByRef_GEhtitsAppObj_4()
4 Application.OnTime Now(), "'CalledByRef Module1.Eye, Module1.Jay, Module1.KEh'" '_--- Fix _ 2) Module1.MyVariable
Let Eye = 41: Let Jay = 42: Let KEh = 43
' Application.OnTime Now(), "'WtchaGotModScopeVariables'" ' ByRef is not working if this is done
Application.OnTime Now() + TimeValue("00:00:01"), "'WtchaGotModScopeVariables'" '_--- Fix _ 1) to get ByRef to work
End Sub
Sub WtchaGotModScopeVariables()
Debug.Print Eye & " " & Jay & " " & KEh & vbCr & vbLf
End Sub
Sub CalledByVal(ByVal I As Long, ByVal J As Long, ByVal K As Long)
Debug.Print I & " " & J & " " & K
Let I = I + 1000: Let J = J + 1000: Let K = K + 1000
End Sub
Sub CalledByRef(ByRef I As Long, ByRef J As Long, ByRef K As Long)
Debug.Print I & " " & J & " " & K
Let I = I + 2000: Let J = J + 2000: Let K = K + 2000
End Sub