We help IT Professionals succeed at work.

# How do you think about loops without going loopy? A vba beginner asks,

on
for example I saw this fragment of a snippet  (with apols to the author if you recognise it)

Now then every time I try to read this stuff my brain shuts down.

I can say out loud what a loop and a counter does, but reading it and doing it is quite another thing.

``````   For i = 0 To iBound
For j = 0 To jBound
Set myarray(i, j) = Range("A1:C8").Cells(i + 1, j + 1)
Next j
Next i

For i = 0 To UBound(myarray, 1)
For j = 0 To UBound(myarray, 2)
Debug.Print myarray(i, j).Address 'demonstrate it works
Next j
Next i
End Sub
``````

The Institution of marriage is quite sufficient institutions for me to be in, but loops endanger that constraint.

Anthony
Comment
Watch Question

## View Solution Only

Senior .Net Developer
CERTIFIED EXPERT

Commented:
When I look at a loop I start with the top statement to figure out the range or scope that I'm working with.

Then figure out the internal statement to figure out what's it's doing for just one cell.  After that, it should become clear what it will do the next time through.

It gets easier with practice.
CERTIFIED EXPERT

Commented:
One easy way is to step through the code using the F8 key and trying to understand what the line is doing. To be able to monitor this you can switch on the "Locals window" from the "View" menu to help you see when and how the variables are changing.

In the above example there is a nested loop. The j loop is nested within the i loop.

The i in the i loop is incremented by 1 until it reaches the value of ibound. After each increment it executes the embedded code.

Every time i is incremented the j in the j loop is incremented by 1 until it reaches the value of jbound. After each increment it executes the embedded code.

the embedded code for the j loop assigns a cell from the given range to an array variable.

The second loop is similar.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
The inner loop (iteself) is executed iBound + 1 times. This means the code within the inner loop is executed (iBound + 1) * (jBound + 1) times (logically the same thing goes on in the other loop; the counts are just different). If you were to add another inner or outer loop, then you would multiply again to determine the total number of iterations. For example:

``````For i  = 0 To 10                   ' Will be executed 11 times
For j = 0 To 10                ' Will be executed 11 * 11 times
For k = 0 To 10            '  Will be executed 11 * 11 * 11 times
MsgBox "Hello World!"  ' you will see 1331 message boxes
...
``````

Event though the inner loops are repeated because of any outer loops, they are still re-initalized when the next iteration of the outer loop is executed. You can circumvent this with the appropriate coding, but as written that is the behavior you will witness.

Note, I say 11 times because the To is inclusive. Since the initial value is zero, you have to add one more to your count (when thinking about the behavior) to get the correct number of iterations. Had the initial value been one, then the number of iterations in each loop would have been 10.

Is that the confusion you are having?
Assistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
Personally I find it easiest to work from the inside out when reading loops in code.

For example the code you have will perform the function For J = ... functions will perfom entirely for each result of For i = ....

Does that help at all?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
logically the same thing goes on in the other loop; the counts are just different
That's a bit ambiguous. By that statement, I was referring to the loops you have in lines 7 - 11. The "counts" I was referring to were the calls to UBound(myarray, 1) and UBound(myarray, 2).
Chartered Accountant
CERTIFIED EXPERT

Commented:
it's every loop, not just this one which is just a convenient example - quite a good one as it happens judging by the explanations - just does my head in, loopy like.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Can you describe what particular aspect is confusing? You haven't given us much to go on.
CERTIFIED EXPERT

Commented:
Just step through as I have suggested and it will make your life easier. It would be even better if you monitor the proceedings on a piece of paper as you go along.
Assistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
While the explanations that others have given are very detailed it is hard to explain without examples so it was easiest to use what you supplied.

I think ged325 said it best: "It gets easier with practice"

:)
CERTIFIED EXPERT
Top Expert 2015

Commented:
You simply do the job in an object oriented way:

Set myArray = Range("A1:C8").Offset(1, 1)

No loop necessary.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
I'll take another stab at it  = )

A loop is nothing more than repeating the same operation(s) X number of times. If you were listening to your favorite CD and you put it on "repeat", then you would have yourself a loop.

In code, it is often useful to repeat the same operation multiple times. You might want to add the numbers 1 through 10 to see what their total is:

``````Dim total As Integer

total = 0

For i = 1 To 10
total = total + i
Next
``````

...or you might want to continue receiving input from a user until he gets it right:

``````While Not input = "1"
input = InputBox("Please enter the number '1'")
Wend
``````

...or any other infinite number of repeatable processes. You have to think about what the goal is firstly. Let's look at the summation example.

The goal in the summation is to get a total. A total of what? We want a total of the number between 1 and 10. I mentioned before that a For loop is inclusive on its bounds (I said To earlier; I should probably have said For as I am now). That means we start at the number on the left side of the To, and we end when our variable is incremented to the point that it is greater than the number on the right side of the To. I did not say "equal to" because even when the variable of the For is equal to the number on the right side of the To, another iteration runs (because the bounds are inclusive). Since I am wanting to calculate the sum of 1 to 10, I can use those two numbers for the bounds of my For loop. Because of the For loop's behavior, I know that the first number added to total will be the value 1, and the last value added will be 10, and all the intermediate integers will be added along the way. My goal entailed just a single loop, and it was satisfied simply by setting the appropriate bounds on the For, and by using the appropriate operation within the body--the part between For... and Next--of the For--in this case addition.

What if the goal were different, and more complex? What if I had a matrix (a grid or table) of dimensions 4x4? (You can think Excel spreadsheet here.) Let's say I wanted to add all of the values within this 4x4 grid. In adding a series of numbers (above), I only have one dimension to account for--think number line. However, with the grid, I need to look horizontally and vertically for my values. Since I have two dimensions (hor. and vert.), I need two loops--one for each dimension. Here's an example:

``````Dim total As Integer

For h = 1 To 4
For v = 1 To 4
total = total + grid(h, v)
Next
Next
``````

In the above, my grid has two dimensions:  horizontal and vertical. I am representing each as "h" and "v", respectively. Now these two variables are completely arbitrary in name; I am just using "h" and "v" for demonstration purposes. In order to access any given cell in my grid, I need to know these two components. In order to prevent hard-coding values, though, I am using two For loops--one to iterate over the horizontal component, and one to iterate over the vertical component. The same thing would happen if you drew a grid on a piece of paper, and you placed your pencil in each square successively. You might start on the upper left corner and proceed to the right. Once you reach the right-most square, you would return to the left side of the grid, and you would proceed down one row. You are now on row 2, column 1. This is what two nested loops would be doing. The outer loop ("h") corresponds to you being in the first row; the inner loop ("v") corresponds to you being in the first column. When "h" and "v" are both 1, you are in the upper-most, left-most corner of the grid.

The inner loop will completely execute before your "h" increments. This corresponds to you proceeding right-ward with your pencil within the grid. Once you reach the right-most square, you have completed your inner loop. The act of moving your pencil back to the left and down to the next row correcponds to "h" being incremented. The "v" component/loop starts back over at 1, since you are in the left-most grid of row 2.

Now these two examples are just that--examples. Loop behavior depends on the type of loop, the conditions for exiting the loop, and the goal of the loop. But for For loops, you can think of the behavior in this manner. The more nested For you have, the more dimensions you would have. For instance, if you had two nested loops (for a total of 3 loops), you could think of that as looping over a cube as opposed to a grid. Also know that you can often (I venture to say "always") convert one kind of loop to another (e.g. For to While). You just have to keep the conditions consistent. For example:

``````Dim i As Integer
Dim total As Integer

i = 1

While i <= 10
total = total + i
i = i + 1
Wend
``````

I am still adding the numbers 1 - 10, I'm just using a While loop with matching conditions (i.e. start at one; stop when you are greater than 10).
Assistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
kaufmed:

Very nice.  I really like the comparison to Excel.  If I may go back to your original analogy of the CD I would like to take a stab at making your description a little shorter:

I have a full 5 disk CD changer and I want to play songs 2-4 of each CD in order.

Dim total As Integer

For disk = 1 To 5
For track = 2 To 4
Play track
Next
Next

The "in order" is very important since this snippet does not allow for ANY random shuffle.

This is in NO way attempting to take away from your excellent description but just meant as another way to demonstrate what you said.
Chartered Accountant
CERTIFIED EXPERT

Commented:
Nearly 2am here, too tired to read all this, but very grateful for all your descriptions. I will read your missives tomorrow with great interest and fascination. Thank you. Anthony
Most Valuable Expert 2012
Top Expert 2012

Commented:
Anthony, my brain shuts down, too.  You matrix brains out there, I appreciate you very much, so don't get miffed just because I share a different perspective, as there's more than one tool in the toolbox these days.

``````Sub test1()
Dim i As Long
Dim j As Long
Dim ibound As Long
Dim jbound As Long
Dim myarray() As Variant

ibound = Range("A1:C8").Rows.Count
jbound = Range("A1:C8").Columns.Count

ReDim myarray(0 To ibound, 0 To jbound)
For i = 0 To UBound(myarray, 1)
For j = 0 To UBound(myarray, 2)
Set myarray(i, j) = Range("A1:C8").Cells(i + 1, j + 1)
Next j
Next i

For i = 0 To UBound(myarray, 1)
For j = 0 To UBound(myarray, 2)
Debug.Print myarray(i, j).Address    'demonstrate it works
Next j
Next i
End Sub
``````

Can very easily be converted to this:
``````Sub test2()
Dim i As Long
Dim j As Long
Dim vArray As Variant

vArray = Range("A1:C8")

For i = LBound(vArray, 1) To UBound(vArray, 1)
For j = LBound(vArray, 2) To UBound(vArray, 2)
Debug.Print Range("A1").Cells(i, j).Value, vArray(i, j)  'demonstrate it works
Next j
Next i

End Sub
``````

And I only use the second part like the first to demonstrate the values in vArray are EXACTLY what are in the respective cells.  Odds are you're dealing with the VALUES not the addresses of the cells.  Generally that's the case as the array index tells you already the address, hence it would be redundant to do otherwise, I think.

Also, its just as easy to traverse the ranges and not pull anything into an array, unless it is explictly needed.  Using the range object and collections is more state of the art with object oriented programing IMHO, though arrays have their place -in the old days that's pretty much all we had, now we can choose when to use them and their multi dimensions to do impressive things, but they are not the only tool in the toolbox.

And, to be a bit short on the subject, this could have the same function, depending on what you want to DO with the data in the range:
``````Sub test3()
Dim rng As Range
Dim r As Range

Set rng = Range("A1:C8")

For Each r In Range
'do something with r
r.Value = r.Value + 1
Next r

'I need to show no code to PROVE I was addressing the correct cells, lol

End Sub
``````

Also we read ranges into dictionaries or collections as well - again having an operation/function in mind should help you choose the right tool, though you can HAMMER with just about any of them, the resulting cabinetry might not look/function as woodcraft.

PS you can also write to a range using a variant in one step, as well.  And you can use the transpose function on variant arrays to shift rows to columns/columns to rows.

cheers,

Dave
Chartered Accountant
CERTIFIED EXPERT

Commented:
This is going to take a while. :-)
Chartered Accountant
CERTIFIED EXPERT

Commented:
ID: 37703079 kaufmed
Is that the confusion you are having?

Possible, but no :-)

ID: 37703088 pony10us
Does that help at all?

Possible but, no :-)

ID: 37703335 ssaqibh
Just step through as I have suggested and it will make your life easier

I'm trying with that, struggling a bit as the commands are for pc and mac differs a bit, also how to step through a udf is baffling me, it seems I have to make a change in sheet to make it activate, run doesn't seem to run it - and what to click on to make it step and where should I be expecting it to step to? is that break points it steps to or does it just step thro each command? I find everything seems to freeze sometimes and then come  back, feels like the macro is running but I can't see the code running.  I am accustomed to seeing code running as highlighted and stepping through it with say the space bar or whatever, it sounds like I should expect similar, but haven't got there yet.

ID: 37703357 JamesBurger
How on earth can you see into it like that?

ID: 37703452 kaufmed
Since I am wanting to calculate the sum of 1 to 10, I can use those two numbers for the bounds of my For loop. Because of the For loop's behavior, I know that the first number added to total will be the value 1, and the last value added will be 10, and all the intermediate integers will be added along the way.

You do? How do you know that? What behaviour?

I am still reading.......... only as far a sthe above at this time...  anthony
Chartered Accountant
CERTIFIED EXPERT

Commented:

I used to love playing battleships, the one where you scribble out a box of squares, write numbers across and letters down, shade in a few squares and start firing grid references at each other. Seems to me these arrays, 2d and onwards are battleship charts (a.k.a 3d+ spreadsheets).

So how are you referring to them? Rows and Columns is my guess, whether row 1 col 3
or row 1 col1 col2 col3 etc (which would be an inner loop far as I can see) and then back (increment 1) to row 2, col 1 col2 etc Having said that it looks like the way Index works, "hitting" a location. Yet having said that the location could be one on Mars and another on Venus, it is the order of work being applied rather than any remotely physical location which it would be in a spreadsheet, so these arrays not located anywhere are more of an idea than they are real. So like target co-ordinates given to operators of something being aimed. These computer program loops goto each location sequentially, subject to the loop being written that way: I suppose any algorithm could be applied to generate an/the order of work.

I liked my battleship charts.

Anthony
CERTIFIED EXPERT

Commented:
struggling a bit as the commands are for pc and mac differs

I have to make a change in sheet to make it activate,

I do not know anything about the Mac but I think that this will surely work:
Make this the first statement of the UDF

Stop
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015
Commented:
You do? How do you know that? What behaviour?
Because the language defines that given a loop:

``````For i = m To n
...
Next
``````

...the loop will execute (n - m) + 1 times, provided no other behavior within the loop affects the value of n. Different languages define how their loops behave. In C# for example, a loop is not implicitly inclusive. You can make it that way, or you can make it exclusive--hell, you can even make the condition for exiting the loop something completely bonkers (as long as it is a boolean condition). In C# the same For loop would be:

``````for (int i = m; i <= n; i++)
{
...
}
``````

It's up to the designer of the language as to the syntax a loop uses and the behavior a loop exhibits. It is up to you, the user of the language, to know how the constructs have been defined, the same way you have to know that you can't assign a String directly to an Integer, you must use a corresponding End If if you want the body of your If to consists of multiple expressions, and you use Exit Sub to return from a subroutine earlier than the end of said subroutine. (These are just a subset of things you must know about the language, mind you.)
Chartered Accountant
CERTIFIED EXPERT

Commented:
@ssaqibh I think the mac works the same, just different keys occasionally, on the pc how do you step through a UDF? A sub appears on the run macro list, but a UDF does not.

@kaufmed hi , interesting you refer to the language the language is written in as it were, during my time studying this VBA I often find I am wishing I could be allowed to read the code behind the code so I can understand what it will do. It feels as if I have to guess, which suggests I might be missing an aspect of how one goes about understanding the language, what makes it tick.

I'll revisit after sleeping on your above post. Thank you for putting in all this time and thought.

@Dave, not forgotten you, just not taken it in yet.

Amused me to see the author of my op snippet commenting without mention he wrote it. :-)

Anthony
CERTIFIED EXPERT

Commented:
As I said insert the stop statement immediately after the function statement

Function abc(a,b,c)
Stop
for....
.
next..
end sub
Chartered Accountant
CERTIFIED EXPERT

Commented:
ok done, see what that does, how do you step? ok that's in ID: 37703077
CERTIFIED EXPERT

Commented:
From the debug menu there should be a "Step into". It might also show the shortcut key for it.
Chartered Accountant
CERTIFIED EXPERT

Commented:
good morning! just exploring now, yes we have step into/over/out all with short cuts.

note for me using this link
http://office.microsoft.com/en-us/help/debug-errors-in-office-visual-basic-for-applications-code-HA001042819.aspx
Chartered Accountant
CERTIFIED EXPERT

Commented:
so I can step through code line by line, but what about each bit within the line?
CERTIFIED EXPERT

Commented:
For each bit, you can highlight a valid expressions like any one of the following and then do debug > quick watch    or whatever shortcut key is appropriate for this.

r.value
i
iBound
j
jBound
myarray(i, j)
Range("A1:C8").Cells(i + 1, j + 1)
Range("A1:C8").Cells(i + 1, j + 1).address
UBound(myarray, 1)
Chartered Accountant
CERTIFIED EXPERT

Commented:
when does hover work? I see it does, but not sure exactly what activates it and if activated when it operates edit: yes giving me the menus allows me to see the relevant mac shortcuts... though in the end I will be doing this pc based, so menu learning is bi-platform.
CERTIFIED EXPERT

Commented:
I am not sure what really triggers hover. There are many times when hover does not work and then I have to revert to watch or quick watch
Most Valuable Expert 2012
Top Expert 2012

Commented:
Some things are because "it was designed that way".

@ssaqibh - Believe it or not, intellisense will not work if there's a current compile error.  I usually find if my intellisense is not working, then I have a syntax error - a quick debug/compile, fix, and my intellisense,  etc., work yet again.

It could be some type of current error prevents hover from displaying results, as well.

Dave
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Since we are in Excel, perhaps a specific range example with more meaningful variable names would help:

``````Sub LoopArray()
Dim lRow             As Long
Dim lColumn          As Long

' loop through each row
For lRow = 1 To Range("A1:C8").Rows.Count

' for each row, loop through each column
For lColumn = 1 To Range("A1:C8").Columns.Count

Next lColumn

Next lRow

End Sub
``````
Chartered Accountant
CERTIFIED EXPERT

Commented:
ok, maybe I should say enough! thank you.

I can't close this until I understand, which may take me some time as there is a great deal to take in, for which again I offer my thanks.

Anthony

p.s. if you wish to add anything please feel welcome, just that you understand why this isn't closed yet.