Excel VBA Watch Window

In this article, we are going to learn how we can use the Excel VBA watch window to monitor the variable values and speed up the debugging process. In VBA, we frequently face errors related to variable values. Often we get erroneous values in code, and we spend a myriad of hours on the issues. This article will cover how to add and use the VBA watch window, moreover, we will discuss some of the benefits of using it.

In most cases, there is an instance where you may have to face the error. Checking individual values can be daunting and cause inefficiencies. To tackle this, we can use the VBA watch window to speed up the process. Below, we can see how the watch window monitors the variable value in Excel VBA.

Excel Watch Window


How to Add a Watch Window in Excel

We can easily add a watch window from the VBA code editor window.

  • For this, open the VBA code editor window from the helper article.
  • And then go to View > Watch Window.

VBA watch window inserting

  • After that, we will see that there is a watch window at the bottom of the VBA code editor window.

The watch window in the bottom of the window


How to Use Watch Expressions in Excel

Before we can use the watch expression feature, we need to add a watch variable in the watch window.

  • For this, right-click on the watch window, and then from the context menu, click on the Add Watch option.

Adding the watch in the watch window

  • After clicking on the Add Watch option, we will see that there is a window named Add Watch…
  • In that window, enter the Variable name of the variable we are going to track in the Expression
  • Then select watch type options based on the match we want to see in the watch window.
  • Click OK after this.

Mention the variable name to watch in the Expression box.

Read More: How to Monitor Cells Using Excel Watch Window


How to Use a Watch Window in Excel

Below, we have presented a sample of watch window initiation and how we can use it to monitor the values.

Example of dataset

  • Here we are going to get the summation of the prices of product A and product B.
  • We are also going to monitor the summation of the product values and the summation of the total price of the product per month.
  • For this, we are going to present a sample VBA code below.
Sub CalculateTotalSales()
Dim ws As Worksheet
Dim lastRow As Long
Dim totalSales As Double
Dim productA As Variant
Dim productB As Variant
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 5 To lastRow
If IsNumeric(ws.Cells(i, 3).Value) Then
productA = ws.Cells(i, 3).Value
Else
productA = 0
End If
If IsNumeric(ws.Cells(i, 4).Value) Then
productB = ws.Cells(i, 4).Value
Else
productB = 0
End If
totalSales = productA + productB
ws.Cells(i, 5).Value = totalSales
Next i
End Sub
  • The code is going to sum the values and present the summation value in the next column.
  • And each time the totalSale variable value changes the watch window is going to be updated.
  • Then add a watch window, following the methods mentioned earlier.
  • And in that watch window add a watch variable.

Adding watch to the VBA code editor

  • In that Add Watch window, we can mention the variable name that we want to follow.
  • We will input
  • Also select Break When Value Changes. And then click

Adding watch to the VBA code editor

  • After pressing OK we will see that the variable totalSales variable is now added to the watched variable list.
  • Now if we try to run the code, we will see the variable totalSales is now enlisted for tracking and now starts updating as we press the
  • The code will run and update the variable value as long as we keep running it.

Value flactuating after adding the watch first

  • The value of the totalSales after the second iteration.

Second iteration of the values after presssing the Run button

  • The value of totalSales after all the Run sequences ran out of the loop.

Final iteration after all the run sequence done

  • By doing so, we can see how we can monitor the variable values. If there is any kind of wrong behavior in the code, then we can certainly detect it from this window.

Read More: How to Trace Errors in Excel


What Are the Benefits of Using a Watch Window?

Using a Watch Window in Excel VBA code provides several benefits that can greatly enhance the debugging and development processes. Some of the key advantages include:

Real-time monitoring

The Watch Window allows you to monitor the values of specific variables in real time as your VBA code executes. This can help you understand how the variables change throughout the execution and identify any unexpected behaviors.

Simplified Debugging

When encountering errors or unexpected results in your VBA code, you can set up watches on relevant variables. By observing their values, you can quickly pinpoint the source of the issue and address it more efficiently.

Minimizes Repetitive Checks

Instead of constantly adding debugging statements to display variable values, the Watch Window provides a central location to monitor multiple variables simultaneously, saving you time and effort.

No Code Modification

Unlike adding temporary debugging statements in your code (e.g., MsgBox or Debug.Print), using the Watch Window does not require modifying your code. It allows you to inspect variables without altering the original code, making it a non-intrusive debugging method.

Supports Complex Data Structures

The Watch Window is versatile and can handle complex data structures, such as arrays, objects, or multi-dimensional arrays. You can expand and inspect nested elements within these structures, providing deeper insights into the data.

Pausing Code Execution

You can pause your VBA code execution at any point and check the values of variables in the Watch Window. This feature helps you analyze the state of your code mid-execution, making it easier to identify logic errors or unexpected data.

Efficient Tracking

For lengthy or iterative processes, the Watch Window helps you track the progress and values of variables at different stages, providing a better understanding of the code’s behavior.

Variable Manipulation

In some cases, you can change the value of a variable directly from the Watch Window while the code is paused, allowing you to experiment with different scenarios without modifying the code.


Things to Remember

Variable scope: The Watch Window can only monitor variables that are in the current scope of the code. Make sure the variables you want to watch are in the correct scope, and accessible to the point where you added the watch.

Pausing code execution: To observe variable values accurately, you may need to pause code execution (using breakpoints or stepping through the code). Remember to resume code execution after observing the variables to continue the flow.

Watch expressions: You can watch not only single variables but also expressions. For instance, you can enter an expression like “productA + productB” to observe the total sales as a calculated value.

Updating the Watch Window: The values in the Watch Window update only when the code is paused. If you want to see updated values during execution, you need to manually pause the code at different points.

Memory usage: Adding multiple variables or complex expressions to the Watch Window can increase memory usage and slow down the debugging process. Be cautious when adding too many watches, especially to large projects.

Watch Window Limitations: The Watch Window can display a limited number of variables simultaneously. If you exceed this limit, you may need to remove some watches or use other debugging techniques.

Tracking object properties: When watching object variables, you can expand the object’s properties in the Watch Window. However, not all object properties are readable or may not update in real-time, depending on the object’s design.

Datasets: When watching ranges or arrays, be mindful of the size of the data. Large datasets can cause the Watch Window to slow down or become unresponsive.

Floating-point accuracy: Be aware of potential floating-point precision issues, especially when comparing or calculating double or single-precision floating-point variables.

Disable Watches: Once you have finished debugging, consider disabling or removing any unnecessary watches to keep your VBA code running efficiently.


Frequently Asked Questions

Q1. What is the shortcut for the watch window in VBA?

The shortcut to open the Watch Window in the VBA editor is CTRL + SHIFT + W. Pressing this key combination will open the Watch Window, allowing you to add and monitor variables and expressions while debugging your VBA code.

Q2. How do I activate a window in VBA?

To activate a window in VBA, you can use the Activate method of the window object. For example:

WindowObject.Activate

Replace WindowObject with the name of the window you want to activate. This method brings the specified window to the fore front and gives it the focus.

Q3. What is the watch window function in Excel?

The Watch Window function in Excel is a powerful debugging tool available in the Visual Basic for Applications (VBA) editor. It allows developers to monitor the values of specific variables and expressions in real time while running VBA code. By adding variables to the Watch Window, developers can easily track and analyze their values, facilitating the identification and resolution of errors and issues in their VBA code.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

In this article, we are going to see how we can use the Excel VBA watch window to monitor variable value changes. We presented an example of how we can use the watch window to monitor the variable values so that we can detect anomalies in the code procedures.


<< Go Back to Auditing FormulasExcel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

2 Comments
  1. All is fairly well presented except for no mention of displaying Public Variables which are supposed to be active in ALL procedures and modules. I put a Public Variable in to display in all procedures and I get an ‘out of scope’ kind of report. I was hoping for a thorough explanation.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 13, 2024 at 11:02 AM

      Hello Peter Clarke

      Thanks for reaching out and sharing your problems and suggestions. You put a Public Variable to display in all procedures but got an “out of scope” report. You want a thorough explanation.

      This issue could arise due to several reasons:

      • Proper Declaration: Ensure the Public Variable is declared correctly at the module level, typically at the top of a module and outside any specific procedure.
      • Module Inclusion: Check that the module containing the Public Variable is included in the project. If it’s not included or there are visibility issues, the variable may not be accessible across all procedures.
      • Reference Check: If multiple modules or projects exist, ensure no conflicts or reference issues. Public Variables should be accessible across all referenced projects and modules.

      I have demonstrated watching a Public Variable in the Watch Window. I am using three modules: one for the public variable, one for displaying the public variable, and one for watching the public variable.

      VBA Code in Module1:

      Public totalSales As Long

      VBA Code in Module2:

      Public Sub DisplayTotalSales()
          Debug.Print totalSales
      End Sub

      VBA Code in Module3:

      Sub CalculateTotalSales()
          
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim i As Long
          Dim productA As Variant
          Dim productB As Variant
          
          Set ws = ThisWorkbook.Sheets("Sheet1")
          lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
          
          For i = 6 To lastRow
              
              If IsNumeric(ws.Cells(i, 3).Value) Then
                  productA = ws.Cells(i, 3).Value
              Else
                  productA = 0
              End If
              
              If IsNumeric(ws.Cells(i, 4).Value) Then
                  productB = ws.Cells(i, 4).Value
              Else
                  productB = 0
              End If
              
              totalSales = productA + productB
              ws.Cells(i, 5).Value = totalSales
              
              Call DisplayTotalSales
          
          Next i
      
      End Sub

      OUTPUT Overview:

      Hopefully, the idea will overcome your situation. Good luck.

      Regards

      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo