Watch Expression

A deeper than necessary dive into the exact expression evaluation behavior of the basic "Watch Expression" in VBA.

Watch Expression

This is the first in a series of articles that will dive into the behavior of the different Watch Types in VBA:

  • Watch Expression
  • Break When Value Is True
  • Break When Value Changes

We'll focus on the first type in this article: Watch Expression.  This most basic type of Watch will not force your code to halt the way the other two types can.  This has important implications when it comes to debugging performance.  


To demonstrate the behavior of the different Watch Types, I'm going to use my own custom Watch() method to illustrate just how often these expressions are being evaluated.

Sample Code

Here's the sample code I will be using for the demonstration:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Dim MyVar As Integer

Function Watch(Val, Optional CalledFrom As String = "")
    Debug.Print Val, CalledFrom
    Sleep 1000   'Sleep for one second for demo purposes
    Watch = Val
End Function

Sub TestWatch()
    MyVar = 0
    Debug.Print "Alpha"
    Debug.Print "Bravo"
    MyVar = 2
    Debug.Print "Charlie"
End Sub

Here's the expression I will be watching.  Note that each time this expression is evaluated, the value of MyVar will be printed to the immediate window, along with the current date and time.

Watch(MyVar, Now) = 2

Throughout this series of articles, the code itself and the expression being watched will remain the same.  The only things I will be changing are:

  • the Watch Type
  • the setting of breakpoints
  • method of code execution (e.g., [F5] "Run" vs. [F8] "Step Into")

Watch Type:  • Watch Expression

Run Sub [F5] - No Breakpoints

In the first test, we're just running the test function using Run > Run Macro (F5):

No timestamp written to the Immediate Window because the expression never got evaluated

Run Sub [F5] - Breakpoints as indicated

In this next test, I've added a couple of breakpoints on the lines where I'm setting the value of MyVar.  Here's the result:

The expression is evaluated each time code execution halts; in this case, at each breakpoint

Notice that the expression is evaluated when the code halts, not when it starts.  That's why at the first breakpoint, the value of MyVar is 2 (it's left over from the previous run).  Then, at the second breakpoint, the value of MyVar is 0 (as set earlier in the function).

Step Into [F8]

For the final test, I'm showing what happens when we step through the TestWatch() function one line at a time.

  1. This is the starting point; I have not pressed F8 yet.
  2. The first F8 press sets the next statement to the routine declaration line.  The value of MyVar is 2 from the previous run.  No lines of code have actually executed yet.  However, the Watch expression has been evaluated.
  3. The second F8 press executes the routine declaration line.  Note that the value of MyVar is still 2 because the MyVar = 0 line has not yet been executed.
  4. The third F8 press executes the line that sets MyVar to zero.  Note that the value of MyVar is now 0 in the immediate window.
  5. The fourth F8 press prints "Alpha" to the immediate window.  And then the Watch Expression is evaluated.
  6. The fifth F8 press prints "Bravo" to the immediate window.
  7. The sixth F8 press sets the value of MyVar to 2.
  8. The seventh F8 press prints "Charlie" to the immediate window.
  9. The eighth F8 press executes the End Sub line and exits the routine.  After exiting the routine, the watched expression is evaluated one last time.

Image by JL G from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0