Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-09
31
Medium Priority
?
341 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Anthony Mellor
  • 11
  • 7
  • 5
  • +5
31 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37703075
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37703077
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37703079
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:pony10us
ID: 37703088
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37703104
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37703317
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37703333
Can you describe what particular aspect is confusing? You haven't given us much to go on.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37703335
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
 
LVL 26

Expert Comment

by:pony10us
ID: 37703346
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
 
LVL 40
ID: 37703357
You simply do the job in an object oriented way:

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

No loop necessary.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37703452
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
 
LVL 26

Expert Comment

by:pony10us
ID: 37703605
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37703833
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37704037
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37705143
This is going to take a while. :-)
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37705262
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37705301
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37705384
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
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 37705496
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37705611
@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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37705619
As I said insert the stop statement immediately after the function statement

Function abc(a,b,c)
   Stop
   for....
     .
   next..
end sub
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37706620
ok done, see what that does, how do you step? ok that's in ID: 37703077
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37706626
From the debug menu there should be a "Step into". It might also show the shortcut key for it.
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37706627
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37706633
so I can step through code line by line, but what about each bit within the line?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37706656
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37707680
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37707717
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37707733
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709242
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
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37714472
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question