Break When Value Changes

Mike Wolfe -

This is the third 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 third type in this article: Break When Value Changes. This type of Watch monitors the expression and halts code execution when the value of the watched expression changes.  In order to do this, it must evaluate the expression before executing every line of code.  This has important implications when it comes to debugging performance.

Demonstration

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)
#Else
    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:  • Break When Value Changes

Run Sub [F5] - No Breakpoints - MyVar = 0 at the Start

In the first test, we're just running the test function using Run > Run Macro (F5).  The initial value of MyVar is zero, which means the watched expression evaluates to False at the start.

Notice that VBA evaluates the expression before every single line of code and also when code execution ends.

The other thing to notice is that code execution halts AFTER the line that caused the value of the expression to change.  The line that caused the change was MyVar = 2, but code execution halts at the following line, Debug.Print "Charlie".  Keep this in mind when trying to track down the source of the change you are monitoring.

Run Sub [F5] - No Breakpoints - MyVar = 2 at the Start

In the second test, MyVar begins equal to 2.  This leads to an extra value change and, hence, an extra break in code execution:

Step Into [F8] - No Breakpoints - MyVar = 2 at the Start

In this third test, I stepped through the code one line at a time using the F8 key.  The screenshots below show that the expression was evaluated before executing each line of code and also at the very end AFTER exiting the routine.  (There are 7 lines of code but 8 timestamps written to the immediate window.)

A Surprising Difference

I noticed one surprising difference between the behavior of "Break When Value Is True" and "Break When Value Changes."  The "Break When Value Is True" Watch Type evaluates the watched expression one extra time compared with the "Break When Value Changes" Watch Type.  See the screenshots below for the side-by-side comparison.

I don't know why this is.  I can't imagine what difference this makes in the real world.  But I found it interesting nonetheless.

Lessons Learned

Here are the key takeaways from the behavior of the "Break When Value Changes" Watch Type:

  • VBA evaluates the watched expression before every line of code AND after code execution finishes
  • Since VBA evaluates the watched expression BEFORE executing the highlighted line of code, the line of code responsible for changing the value of the watched expression is the PREVIOUS line
  • Unlike the "Break When Value Is True" Watch Type, the "Break When Value Changes" Watch Type DOES NOT evaluate the watched expression an extra time when the Watch halts code execution

Image by Brigitte from Pixabay