Running a loop while debugging VBA

Question

The Problem

I am trying to debug some code, and somewhere in the middle I stopped at a breakpoint. Now I want to change some variables and run a certain loop several times.

How far did I get?

I know how to change the variables, but somehow I get stuck when trying to run the loop in the immediate window. Here is an example:

Dim i As Integer 
Dim j As Integer 
For i = 0 To 6
       j=i    ' Do something
Next i 

I tried several variations of the code, but each time I get the following error:

Compile error: Next without for

Other relevant information

I tried searching but mostly found information about problems with loops, whilst I am quite sure the loop itself is fine. (Especially as I reached it before arriving at the breakpoint).

The only place I saw someone addres this situation, he reduced the loop to a single line, however to do this every time would be very impractical in my case.

I realize that I could call a function containing the loop, and then the function call would probably work, but again this feels quite impractical. So I guess it boils down to the following question.

The question

What is a practical way to run a loop whilst debugging VBA code in Excel?

Foldboat

So you want to enter the loop code into the immediate pane, or you want to re-run the loop out of sequence? – Hyams

Answer 1

I think I understand your question. You want to run a multi-line code block (i.e. the loop) in the Immediate Window. This throws errors because the Immediate Window is only intended for single lines of code.

I don't have any suggestions other than those you already mentioned. I'd recommend putting your test loop into a separate function and calling that from the Immediate Window:

Sub Test()
Dim i As Integer 
Dim j As Integer 
For i = 0 To 6
       j=i    ' Do something
Next i 
End

Another option is to set several breakpoints. You can also run one line of code at a time with F8.

What is likely the preferred method (i.e., what most people actually do) is use the full power of the IDE, which includes the Immediate, Locals and Watch panes. You can change the value of most variables at runtime by direct assignment in the Immediate Pane (i=6 will do exactly what you think it should do). The IDE also allows you to set breakpoints, add watch conditions, step through code line-by-line using the F8, step through function or procedure calls using Shift+F8, stepping over (and back) through code using the mouse/cursor, and with a few exceptions, you can even add new variables during runtime.

Dryden

Not the answer I was hoping for, but you indeed got the point of my questiohn. If there is no convenient way that's an answer too of course. – Foldboat

@David Zemens, I agree with your answer but are you sure it belongs here as an edit to my own answer ? I'll leave it if you think it's the best place. – Dryden

I think it's OK as an edit to your answer -- it expands on something you already touched on (breakpoints), and may be useful to anyone who stumbles upon this question with similar problems in the future. – Smattering

Answer 2

There is actually a way for using loops or other multi-line statements in the Immediate Window - using a colon : to separate statements instead of a new line. Full solution is described here. Note that in the Immediate Window you also don't have to declare the variables using a Dim statement.

To summarize, your snippet would look something like this:

For i = 0 To 6: j=i: debug.Print i+j: Next i 

Rozamond

As this is merely a workaround, i will not make this the accepted answer, but you do get my vote! – Foldboat

@DennisJaheruddin, it is of course up to you, but I think that your currently accepted answer is the workaround. The question asks how to run a loop during debugging. The way to that is to submit the loop as a multi-line statement in the Immediate Window, which is achieved by using a ':', as described in my answer. – Rozamond

Answer 3

The solution is to use the Statement Separator ":" (Colon) and the Line Continuation Character "_" (Underscore).

Note the spaces between the Colon : and the Underscore _

Here is what I have in my Immediate Window:

UpperRow = selection.end(xldown).row
UpperCol = selection.column
LowRow = selection.rows(1).row
LowCol = 5
Set Rng = Cells(LowRow, LowCol)
For i = LowCol To UpperCol Step 2 : _
  cl = Split(Cells(1, i).Address, "$")(1) : _
  s = "$" & cl & LowRow & ":$" & cl & "$" & UpperRow : _
  Set Rng = Union(Rng, Range(s)) : _
Next i 

? rng.address
$E$301:$E$302,$G$301:$G$302,$I$301:$I$302,...

Here is a screenshot from my MSOffice 365 Excel immediate window

Excel VBA Immediate Window clip

Nerves

This seems to be exactly what the accepted answer already states? – Foldboat

@DennisJaheruddin Not exactly, mine adds the Multiline facet to it, which also fixes the compile error message you were receiving. – Nerves