VBA: Four Ways To Change Variable Values In Break Mode

Zack’s recent post over at Excel & Access Experts got me thinking: how many ways are there to change a variable’s value in break mode? Needing no further excuses to give Cropper a try, here’s what I came up with.

Edit The Code

You can either edit an existing line of code so it assigns a different value, or you add  a new line of code to do it. Keep an eye on the watch window as I step through and edit this code:


Use The Immediate Window

The immediate window is a powerful beast well worth learning about. You can ask it questions by starting a line with a question mark (?) and you can also write code statements (at run time and design time) which it will execute.


Use The Locals Window

Funnily enough, I only found out about this option when I was reading a chapter in a .NET book about Visual Studio and I thought ooh…. does that work in the VBA IDE too? And the answer, of course, is yes, otherwise I wouldn’t be writing about it here.


I rarely use it because the only way I’ve found to edit the value is by double clicking on it with the mouse, but I guess it’s nice to know.

Use The Watch Window

This one’s similar to the locals window: you can add a watch and then use the value column to edit the variable’s value.


That’s all I could think of. Have I missed any?


About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , . Bookmark the permalink.

2 Responses to VBA: Four Ways To Change Variable Values In Break Mode

  1. zbarresse says:

    I knew all of them except the Locals window – and I have it open all the time lol. So simple. Thanks Colin!


  2. wessexbob says:

    I never use the Locals window, far too much going on in there for me. I prefer the Watch window, where I can control what is displayed. That said, I never set variables there, always use the Immediate window.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s