Showing the updates being performed by macro

1
votes

So I have seen all the ways to hide the screen updating in excel, what I'm looking for is a data problem, so I WANT to see the screen update. Is there a way to code the screen to update during the macro slowly? I tried the following:

'//////adding screen updating to watch what's happening Application.ScreenUpdating = True Application.Wait Now + TimeValue("00:00:01") '/////

and all I got was screen flicker.

Thanks for being excellent unto me.

2
vbaexcel
Step through your code using F8 in the debugger. You can literally go through it line by line this way. Cheers. – David Zemens
umm don't really understand why you'd want to do this. if you have a problem why not use a break point to debug? – Alistair Weir
Also, use the Locals window or add watches so that you can monitor the values held by your variables, and the Immediate window can also help debug. – David Zemens
@DavidZemens, I think you might be onto what I'm looking for...how do I code the Locals or Immediate code into the loop? – SQLHound
You don't code this. It's something you can look at in the VBE. I will add an answer with some screenshots. – David Zemens

2 Answers

7
votes

What you're after is a better method of debugging your errors. ScreenUpdating property of the Application is not what you want :)

Instead, set a breakpoint in your code using F9, at or before where you suspect the error is happening. If necessary, you can put this on your first line of code (if you literally have no idea where the error happens).

Here is a breakpoint, it's the red/maroon highlighted line:

Breakpoint

Then, using the F8 key, you can step through the code line by line.

You can add as many breakpoints as you want. Pressing F5 will execute the code only up to the next breakpoint, then you would have to F8 or F5 to resume execution.

Some additional things to help you debug

Use the Locals window.

Locals Window

This shows you the vairables in scope, and you can click on them to view their properties/etc. As you can see, I have a shitload of variables in a fairly complicated application. I've found the immeidates window is very helpful for Object variables and arrays. Here is what it looks like when I expand an array variable, so that I can see all its contents:

Locals window closeup on an array variable

Use the Immediate window to query variables or execute statements outside of your code, this is equivalent to Debug.Print statements. For example, let's check to see if the value of some variable is "9", you can:

?someVar = 9 and then press enter. It will show you True or False. You could query the value directly by ?someVar and it would print the value.

In this screenshot, I am checking the value of a boolean, and it shows me that the value is False. I could also verify this in the Locals window.

Immediate Window

0
votes

The screen updating must be set only once, at the start of the code. But the Wait you will need to put inside your loop, or spread between your code lines.