Evaluate() in VBA
Hi and welcome to the Evaluate() mystery
The MSDN Office Developer Reference (2013) Documentation says:
Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument.
So, I have ran a very simple code to see how accurate the Microsoft's Documentation of the Evaluate() method is.
Not surprisingly, I am getting a strange albeit consistent result.
note: execute each of the 4 commands in the Immediate Window CTRL+G. See the difference in each of the calls. Notice the built-in bug which shows each MsgBox twice . Just keep that in mind and do not get confused...
Stick this code in a module
Private Sub SleepESub()
Application.Wait Now + TimeValue("0:00:20")
MsgBox "w8'd "
End Sub
then execute these 4 commands ( 1 at a time ) in the Immediate Window
? Evaluate ("SleepESub()")
? [SleepESub()]
? [SleepESub]
? SleepESub
The first 2 execute the code right away; means to me they have evaluated the code. The third one (according to the documentation) should be Evaluating but it doesn't act the same way as it does in a module's body. The Immediate Window is giving an Error 2023 however the same call from within a module's body executes it as if you were calling a sub.It waits the 20 seconds like if it was a normal Call SleepESub() which is the number 4 call.
Can anyone explain what I am missing here? Is the line number 3 not a proper Evaluation call? or does it evaluate the call to sub itself (if that makes sense)
Update:
I think some people are misunderstanding what I am evaluating here - don't worry it is an advanced topic and I am not a book writer and you are not mind readers. (forgive me...)
To get a better idea you can compare results from the immediate window vs. module's body. Try this code:
' Run each of the calls separately
' in a module's body and compare it with
' the previous calls from the Immediate Window
Sub ModuleBody()
Evaluate ("SleepESub()")
'[SleepESub()]
'[SleepESub]
'SleepESub
End Sub
It would appear to me that what differs in the different ways of executing the code would be the thread that it runs on - the UI thread or a background thread, and the parser. Evaluate executed functions would be handled differently to explicitly defined functions, and functions called from the Immediate window would be handled slightly differently also.
In:
Sub ModuleBody()
Evaluate ("SleepESub()")
[SleepESub()]
[SleepESub]
SleepESub
End Sub
Evaluate ("SleepESub()") and [SleepESub()] appear to be expecting a formula, and Private Sub SleepESub() is not being executed at all.
Depending on how the parser handles the procedure, each command may be executed in sequence in a single thread, resulting in the delay from the Application.Wait , or the Application.Wait may be considered to be valid only on the UI thread, and skipped when run on a background thread.
This can be confirmed by the following code, executed by ?[SleepESub()] or ?Evaluate("SleepESub()") in the Immediate window:
Private Declare PtrSafe Sub sapiSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Private Sub SleepESub()
'Application.Wait Now + TimeValue("0:00:05")
sapiSleep 5000
MsgBox "w8'd "
End Sub
When using the sapiSleep 5000 API call, the wait occurs (twice! - that bug that was mentioned), but when using Application.Wait Now + TimeValue("0:00:05") , no delay occurs.
I think that it is wrong to state that the 3rd call is not evaluating : it does indeed evaluate the provided object, and return its value (as documented).
I have slightly modified the Sub to illustrate:
Private Function SleepESub()
Debug.Print Application.Wait(Now + TimeValue("0:00:02"))
MsgBox "w8'd "
SleepESub = 42
End Function
Each of the 4 evaluation calls will indeed return 42 as expected.
What is different is:
Application.Wait succeeds, in the other it fails - notice the debug output which returns either true or false) I have no explanation for either of these differences, though.
链接地址: http://www.djcxy.com/p/72638.html下一篇: 在VBA中评估（）