Getting Started with the Locals Window

by Sola Bode
in Up and Running with Office VBA
May 18, 2020

The Excel VBA coding dojo, the VBA Editor, has many useful windows. Here, you'll explore its Locals window which lets you dynamically track variable values.

7 minutes read

This article is part of the Visual Tour of Excel’s Coding Dojo series, exploring each of the Excel VBA Editor’s menus, toolbars, and windows. Here, we’ll take a close look at the Excel VBA editor’s Locals window.

“Cookies used to cause cavities only in our teeth. Now they also cause cavities in our privacy.”
Khang Kijarro Nguyen

If you wish to skip to any of the other posts in the series, click on any of the topics listed below:

Meet your Mat! The VBA Editor

Most great martial artists first had to get intimate with their dojo. Likewise, to master Excel VBA, you’ll have to get comfy loitering in its coding dojo, the VBA Editor. Naturally, the first step is to open the VBA editor.

“The Dojo is the only place you can die and come back to life, so use it.”
kyoshi Larry Mabson (American karateka)

I, for one, know how daunting the Excel VBA editor can appear to beginners – just take a look at the overview below! However, looks can be deceiving; at least that’s true here. I say that because anyone familiar with programming IDEs will tell you that the VBA editor is one of the more user-friendly IDEs out there.

IDE stands for Integrated Development Environment. They are software that consolidates application development tools.

Laying out the different areas and windows of the Excel VBA Editor (VBE)

Most developers only use a few of the VBA Editor’s menus, toolbars, and windows. As a result, they miss out on several tools that would make their work less stressful and enhance their productivity.

But you are not most developers, are you? For one thing, you are here, so I take it you would like to max out your VBA programming potential. Brilliant! You are well on your way there.

Locals Window – Overview

Like the Immediate window, the Locals window opens below the Code window. However, you can drag and place it as required. Also, the Locals window is hidden by default.

To display the Locals window, you select it from the View menu or click its icon on the Debug toolbar. Moreover, the “Alt + V + S” keyboard shortcut also displays the Locals window.

Illustrating the multiple ways of displaying the Locals Window

The Locals window (shown in the image below) lists the name, value, and datatype of all local variables.

Local variables refer to variables declared within the current scope of program execution. These are variables declared in the current procedure or the General or Declarations area atop the current module. Hence, the variables shown in the Locals window change as execution (scope) shifts between procedures.

Anatomy of the Locals Window

The Expression column lists the names of the Local window variables. It begins with the active module’s name, followed by all module-level variables. Note, the current procedure’s locally declared variables sit at the bottom of the list. Also, the list is collapsible and vice versa; however, like the Type column, it is not editable.

The Value column stores the current values stored in the Local window’s variables. Variables not yet defined in execution hold the default or initial value of their data type (e.g., zero for integers). The list is collapsible and expandable for variables with sub-variables. You expand/collapse the list using the +/- icons that appear before the variable’s name.

Also, the values in the Value column are editable. To edit a value, you click on it and make the change you want then hit Enter to finish. Also, there are value entry rules (e.g., you can’t enter text for numeric variable). Expectedly, an error message pops-up when you violate these rules.

Locals Window – Use(s)

The Locals window does its work while unhidden, and the VBA Editor is in Break mode. As such, its primary use is for code testing and debugging, as indicated by its icon’s location on the Debug toolbar.

The VBA Editor works in any one of three modes – Design, Run (a.k.a. Runtime) and Break modes. These modes complement the primary development stages – coding and GUI (graphic user interface) design, running/testing, and debugging.

Once in Break mode, the scope-local variables appear in the Locals window to allow tracking or editing of their values. Also, note that:

Dragging column borders resize them;
Clicking the Close box (at the top right – double-click the window’s title bar if it is missing) hides the window;
Click the Calls Stack button (below the Close box) to display the Call Stack dialog box. This dialog box lists all the procedures in the Call Stack;

The Call Stack is a queue of all procedures scheduled for execution in the current scope. Often, you’ll link your procedures using Call statements. So, completing a task will require running a series of procedures (think of them as sub-tasks). The Call Stack lets you can see this queue of procedures.

The disabled textbox atop the window, left of the Call Stack button, displays the presently running procedure’s name.

The Locals Window in Action

The images below show the Locals window in action. They speak for themselves and illustrate just how useful the Locals window can be to you.

The Locals window shows all the active module’s module-level variables and only the active procedure’s procedure-level variables

All variables displayed in the Locals window update automatically as their values (and data type, for variant types) change while the procedure runs

The Locals window lets you 'watch' all module and procedure level variables in the current scope of execution. However, a different window is needed to monitor only selected variables — The Locals window lets you ‘watch’ all module and procedure level variables in the current scope of execution. However, a different window is needed to monitor only selected variables

The Long and Short of It

So, there you have it! Now, you know all you need to incorporate the Locals window into your development workflow. The Locals window lets you inspect the values stored in all the variables declared in the current scope of execution. As the scope changes, so too do the variables displayed in the window.

The window isn’t displayed by default in your VBA editor, so you’ll need to select it from the View menu or click its icon on the Debug toolbar, or press “Alt + V +S” keyboard shortcut.

You can also check out the Office Dev Center page for more info on the VBA editor’s Immediate window.

As I mentioned earlier, this article is part of the Visual Tour of Excel’s Coding Dojo series exploring each of the Excel VBA Editor’s menus, toolbars, and windows. To check out any of the other posts in the series, click on topic links below: