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

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.
How do you set about thinking about it?

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

Open in new window


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

Anthony
LVL 9
Anthony MellorChartered AccountantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
Saqib Husain, SyedEngineerCommented:
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.
0
käµfm³d 👽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
...

Open in new window


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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Steven CarnahanNetwork ManagerCommented:
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?
0
käµfm³d 👽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).
0
Anthony MellorChartered AccountantAuthor 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.
0
käµfm³d 👽Commented:
Can you describe what particular aspect is confusing? You haven't given us much to go on.
0
Saqib Husain, SyedEngineerCommented:
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.
0
Steven CarnahanNetwork ManagerCommented:
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"

:)
0
Jacques Bourgeois (James Burger)PresidentCommented:
You simply do the job in an object oriented way:

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

No loop necessary.
0
käµfm³d 👽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

Open in new window


...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

Open in new window


...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

Open in new window


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

Open in new window


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).
0
Steven CarnahanNetwork ManagerCommented:
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.
0
Anthony MellorChartered AccountantAuthor 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
0
dlmilleCommented:
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.

This with some editorial license
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

Open in new window


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

Open in new window


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

Open in new window


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
0
Anthony MellorChartered AccountantAuthor Commented:
This is going to take a while. :-)
0
Anthony MellorChartered AccountantAuthor 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
0
Anthony MellorChartered AccountantAuthor Commented:
right then, finished reading.. hmmm!

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
0
Saqib Husain, SyedEngineerCommented:
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
0
käµfm³d 👽Commented:
You do? How do you know that? What behaviour?
Because the language defines that given a loop:

For i = m To n
...
Next

Open in new window


...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++)
{
    ...
}

Open in new window


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.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony MellorChartered AccountantAuthor 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
0
Saqib Husain, SyedEngineerCommented:
As I said insert the stop statement immediately after the function statement

Function abc(a,b,c)
   Stop
   for....
     .
   next..
end sub
0
Anthony MellorChartered AccountantAuthor Commented:
ok done, see what that does, how do you step? ok that's in ID: 37703077
0
Saqib Husain, SyedEngineerCommented:
From the debug menu there should be a "Step into". It might also show the shortcut key for it.
0
Anthony MellorChartered AccountantAuthor 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
0
Anthony MellorChartered AccountantAuthor Commented:
so I can step through code line by line, but what about each bit within the line?
0
Saqib Husain, SyedEngineerCommented:
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)
myarray(i, j).Address
0
Anthony MellorChartered AccountantAuthor 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.
0
Saqib Husain, SyedEngineerCommented:
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
0
dlmilleCommented:
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
0
Rory ArchibaldCommented:
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

            Debug.Print Range("A1:C8").Cells(lRow, lColumn).Address
        
        Next lColumn

    Next lRow

End Sub

Open in new window

0
Anthony MellorChartered AccountantAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.