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.
The Institution of marriage is quite sufficient institutions for me to be in, but loops endanger that constraint.
Anthony
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 SubThe Institution of marriage is quite sufficient institutions for me to be in, but loops endanger that constraint.
Anthony
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.
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.
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:
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?
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?
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?
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?
logically the same thing goes on in the other loop; the counts are just differentThat'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).
ASKER
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.
Can you describe what particular aspect is confusing? You haven't given us much to go on.
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.
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"
:)
I think ged325 said it best: "It gets easier with practice"
:)
You simply do the job in an object oriented way:
Set myArray = Range("A1:C8").Offset(1, 1)
No loop necessary.
Set myArray = Range("A1:C8").Offset(1, 1)
No loop necessary.
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:
...or you might want to continue receiving input from a user until he gets it right:
...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:
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:
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).
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
NextIn 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
WendI 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).
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.
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.
ASKER
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
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
Can very easily be converted to this:
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:
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
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 SubCan 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 SubAnd 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 SubAlso 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
ASKER
This is going to take a while. :-)
ASKER
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
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
ASKER
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
@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
As I said insert the stop statement immediately after the function statement
Function abc(a,b,c)
Stop
for....
.
next..
end sub
Function abc(a,b,c)
Stop
for....
.
next..
end sub
ASKER
ok done, see what that does, how do you step? ok that's in ID: 37703077
From the debug menu there should be a "Step into". It might also show the shortcut key for it.
ASKER
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.co
note for me using this link
http://office.microsoft.co
ASKER
so I can step through code line by line, but what about each bit within the line?
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
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
ASKER
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.
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
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
@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
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 SubASKER
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.
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.
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.