Evaluate() in VBA

Question

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

interesting. i need to remove the `?` for the last two. `Run("SleepESub")` and `Run("SleepESub()")` are similar but `Evaluate("SleepESub")` doesn't work. What's going on?! — lori_m, Jul 05 '13 at 19:50
With Excel 2010 (_Evaluate_ doc is same as 2013), `? Evaluate("SleepESub")` returns `Error 2029` ; same for `? [SleepESub]`. I would conclude that 2010 is "clean" (matches the documentation) — d-stroyer, Jul 09 '13 at 07:45
@d-stroyer `? [SleepESub]` (*called from the Immediate Window*) does return a `Error 2029`, however If you make the same call within a module's body it does work, although is not Evaluating it just executes as if you were calling a sub. I am starting to wonder if the square brackets are just being ignored in this case as the compiler *thinks* `[SleepESub] = SleepyESub`. On the other hand, if the Immediate Window is giving an error how come the same call from a modules body works? — , Jul 09 '13 at 08:00
If you change the declaration of SleepESub to make it `Public`, then `[SleepESub]` works from the Immediate Window. Same result as from module's body (no apparent evaluation). — d-stroyer, Jul 09 '13 at 08:11
`Expected function or variable.` error appears even in case when its not made private in *Excel 2010*. I dont think it's the visibility issue. Also, I am not sure why I am getting an error but it works for you :o — , Jul 09 '13 at 08:21

score 7 · Accepted Answer · answered Jul 23 '13 at 00:18

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.

You are right! I was wrong assuming the the first two calls have been evaluated, they were not - they were partially executed. The `Application.Wait` was omitted even though the msgbox was shown it did not execute the `SleepESub` properly. It does make a difference calling the `ModuleBody` from the Module vs. the Immediate Window. It's worth mentioning that the `[SleepESub] = SleepESub`. — , Jul 23 '13 at 08:06
Actually, in Evaluate ("SleepESub()") and [SleepESub()], Private Sub SleepESub() is not being executed at all - try setting breakpoints on both the Sub ModuleBody() and Private Sub SleepESub() lines, and you'll see when you single-step through the code that Private Sub SleepESub() is not hit on these two lines in ModuleBody(). — Monty Wild, Jul 23 '13 at 22:27

score -1 · Answer 2 · answered Jul 09 '13 at 08:27

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:

the application context (in one case the call to Application.Wait succeeds, in the other it fails - notice the debug output which returns either true or false)
the number of calls to the routine (one or two calls)

I have no explanation for either of these differences, though.

Evaluate() in VBA

2 Answers2

Linked