Watching and Waiting
Careful what you watch for! Monitoring changes to a slow expression can grind your debug session to a halt.
In an earlier article, I wrote about how the simple act of Watching an expression in VBA can alter the behavior of your program:
Mike Wolfe
Today I'd like to talk about how often VBA evaluates the expressions we watch. For certain types of watches, it's probably more than you think.
Not Responding
Recently, I was troubleshooting a bug that made no sense. I was running code to relink tables and one of the tables was not relinking. As it turns out, the table was relinking correctly, but then a separate process was deleting the linked table.
In the course of troubleshooting the error, I created the following Watch:
When I ran my code with the above Watch in place, everything ground to a halt. I received the dreaded "(Not responding)" treatment from Access.
Prior to adding the Watch, my code ran relatively quickly. The only thing that changed was the addition of the Watch.
The Watch was the culprit in my code's sudden slowdown.
Three Ways to Watch
There are three different Watch types in VBA:
- Watch Expression: allows you to gather all of your relevant expressions into one centralized area for easier monitoring while debugging
- Break When Value Is True: stops execution of the code as soon as the watched expression evaluates to True
- Break When Value Changes: stops execution of the code whenever the evaluated result of the watched expression changes
Two Frequencies of Evaluation
How often VBA evaluates your watched expression depends on the Watch type:
When code execution stops
Applies to: Watch Expression
If you simply watch an expression, then VBA only evaluates that expression when code execution is halted. This could be due to a failing Debug.Assert method, a Stop statement, manually stepping through the code, or using Ctrl+Break to force a halt.
Unless the watched expression is very slow (e.g., each evaluation takes several seconds to process), creating a "Watch Expression" watch type should have no noticeable impact on your application's performance.
Before each line is executed
Applies to: Break When Value Is True, Break When Value Changes
For these types of watches, VBA halts execution based on the results of the evaluated expression. It stands to reason, then, that the only way to determine if the expression meets the halting criteria is to evaluate it whenever any line of code is executed. And I mean...any...line...of...code.
For a simple expression that compares a scalar variable to a constant, such as i = 42, the evaluation happens so quickly that there is no perceptible change to the execution time.
However, for a more complex expression that may takes tens or hundreds of milliseconds to evaluate, the effect can be to grind your program's execution to a halt.
Watching Complex Expressions
What do I mean by a "complex expression" in this context? Basically, any code that is relatively slow, including:
- any kind of database access
- domain aggregate functions (DCount(), DLookup(), etc.)
- references to collections (e.g., Forms, Reports, Controls, etc.)
- opening, connecting, and closing database connections
- hard disk operations
These expressions might never cause bottlenecks when used within the context of your application. As a result, you probably wouldn't give a second thought to using them as the basis for a Watch (I know I certainly didn't). But if you're not careful, you can end up grinding your application to a halt when you start debugging.
Complex Expression: An Example
In the screenshot above, I showed the actual expression I was watching when my program stopped responding. Executing the expression a single time was very fast. In fact, there was no perceptible delay when I ran it in the immediate window. However, there were several complexities in my example that led to it being a relatively slow expression.
- CurrentDb: From the docs, "the CurrentDb method creates another instance of the current database"; because of my watch, every single executing line of code was creating a new instance of the current database
- TableDefs: A collection of every table definition in the current database, including both linked and local tables; I happened to be looking up the last table added to the collection, which meant navigating the entire linked list every time VBA evaluated the expression
- Connect: A string property of the TableDef object; this portion of the expression probably added little extra processing time
The cumulative effect of these attributes resulted in a relatively slow-to-evaluate expression (probably between 10 - 100 ms). An expression that executes in 100 ms appears to be instant when it's run once. But, run that same expression ten or more times consecutively, and suddenly it drags down the performance of the entire application.
Recap and Next Steps
In this article, we covered some potential pitfalls of working with Watches in VBA. In particular, we covered those situations where the watched expression gets evaluated before every line of code.
In the next article, we'll demonstrate the Watch behavior itself in greater detail with a series of examples. Stay tuned.
