Looping Theory/Deeper Explanation

[YellowLabPro]

I have some basic questions/thoughts regarding Loops. I am still trying to understand this function fully.

When thinking about the way a loop actually works, does the loop, loop through the code, the range or a combination of both?

Meaning, does the code bounce from the code to the sheet, back to the code and so on until the code finishes running based on the instructions. Or does the code get read once and then retained in memory, heads off to the sheet and follows the instructions from the code in one continous motion?

I hope this makes sense....

Thanks,

YLP

[mdmackillop]

Hi Yelp,
I'm no expert here, but I would surmise a For Each cel in Range will read in the range and "remember" it. For i = 1 to 10 type will need to look at each Cells(i,1) reference in turn. The former should therefor be more efficient and quicker.

[YellowLabPro]

Sorry MD, I am not clear on your explanation... can you try it again?

[mvidas]

Meaning, does the code bounce from the code to the sheet, back to the code and so on until the code finishes running based on the instructions. Or does the code get read once and then retained in memory, heads off to the sheet and follows the instructions from the code in one continous motion?
Yelps,
I think you might be a little confused, as the code never actually 'goes' to the sheet or anything, you can just control the excel object through code. I'm guessing I missed an earlier thread on the subject. But yes, the code will be retained in memory (for the most part), it is compiled before being run.

To see an example of how some loops work, take a look at 3 versions of the same idea. It creates a one-sheet workbook, puts data in a1:a30, and then loops through that range, all using "for each". The first uses a "for each cell in range("a1",range("a65536").end(xlup)" idea, the second sets that range to a range variable then uses "for each cell in rg", and the third actually puts the range address into a string variable and loops through that. Each one inserts a row at row 29 halfway through completion, and each row number it looks at (and total iteration count) are displayed in the immediate window. Only the last one (with a string containing each cell's address) looks at the initially-specified cells (the debug cll.row skips from 28 to 30 in the last one since 29 was inserted after).

Looking at the results, and possibly even stepping through each one, might shed a little more light for you.

Sub UsingDynamicRange()
Dim CLL As Range, Cnt As Long
Workbooks.Add 1
Range("A1:A30").Value = 7
Cnt = 0
For Each CLL In Range("A1", Range("A65536").End(xlUp)).Cells
Cnt = Cnt + 1
Debug.Print CLL.Row
If CLL.Row = 15 Then Rows(29).Insert
Next
Debug.Print "Cells looped through: " & Cnt '31
End Sub
Sub UsingDefinedRange()
Dim CLL As Range, Cnt As Long, RG As Range
Workbooks.Add 1
Range("A1:A30").Value = 7
Cnt = 0
Set RG = Range("A1", Range("A65536").End(xlUp))
For Each CLL In RG.Cells
Cnt = Cnt + 1
Debug.Print CLL.Row
If CLL.Row = 15 Then Rows(29).Insert
Next
Debug.Print "Cells looped through: " & Cnt '31, no different than dynamic
End Sub
Sub UsingStringDefinedRange()
Dim CLL As Range, Cnt As Long, RG As Range, tStr As String
Workbooks.Add 1
Range("A1:A30").Value = 7
Cnt = 0
For Each CLL In Range("A1:A30").Cells
tStr = tStr & "," & CLL.Address(0, 0)
Next
tStr = Mid(tStr, 2)
Set RG = Range(tStr) '"A1,A2,A3,...,A30"
For Each CLL In RG.Cells
Cnt = Cnt + 1
Debug.Print CLL.Row
If CLL.Row = 15 Then Rows(29).Insert
Next
Debug.Print "Cells looped through: " & Cnt '30
End SubMatt

[johnske]

Hi yelp, as Matt said, the compiled code is what's actually running (Note: in the VBE window you should go to Tools > Options > General and make sure that 'Compile On Demand' option is checked and the 'Background Compile' option is not checked. {If it's unchecked visual basic is forced to always compile the code before it's run})

Instead of using an indexed loop, it's faster to use a For Each...Next loop to iterate through a collection or array. In most cases, using a For Each...Next loop is also more convenient and makes your macro smaller and easier to read and debug.

The following example is slow because it calls for the row variable i during each loop iteration. (I've put a timer in these procedures so you can see the speed difference for yourself)

Sub IndexedLoop()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
'code to be timed
Dim i As Long
For i = 1 To 10000
With Range("A" & i)
If .Value < 0 Then
.Font.ColorIndex = 5
End If
End With
Next
'--------------------------------------
Finish = Timer
MsgBox " run time was " & Finish - Start
End Sub

The following example is shorter and around two to three times faster than the preceding one because the For Each...Next loop automatically keeps track of the row count and position without having to call the variable i.

Sub ForEachLoop()
'~ twice as fast
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
'code to be timed
Dim Cell As Range
For Each Cell In Range("A1:A10000")
With Cell
If .Value < 0 Then
.Font.ColorIndex = 5
End If
End With
Next
'--------------------------------------
Finish = Timer
MsgBox " run time was " & Finish - Start
End Sub

[YellowLabPro]

Thanks guys,
I am tied up at work for now, but will have a go at it tonight.

YLP

[YellowLabPro]

I had posted this a while back. The two examples above are great. The thing that will help me most would be an example of when loops are used. Not an example of code, but an example of which explains a real hands-on use versus another method to solve. If I can visualize it, it will make things clearer.

Thanks in advance,

YLP

[Norie]

YLP

It might actually help if you gave us a 'hands-on' example of what you mean.

[YellowLabPro]

Norie,
Tough to do. I am reading about looping and having trouble grasping what it is used for.

[Bob Phillips]

Normally, OO is event driven, and is trigerred when sometimg happens to an object (such as a range, a sheet), or when an object does something. So, the event is triggered, and control is passed back to the user (or the UI).

Looping is generally used when do a batch process, that is a process that is repeated a number of times. For instance, to go down a list of cells to check any that are a certain value and take some action based upon that values, or a number of sheets, or a number of workbooks.

Loops HAVE to be controlled, otherwise they go on forever, and you never get a look in. That is why we have a counter (For i = min to max), or a pre-defined collection (For each obj In Collection), or just test the condition on entry or exit (Do ... Loop Until condition, Do Until condition ... Loop, While condition ... Wend).

[Norie]

I don't know of any exact technical definition but the simplest way I can think of to describe looping is to iterate (loop) through code a number of times based on criteria.

[YellowLabPro]

XLD, this is making more sense now. Would you mind taking it one more step?


For instance, to go down a list of cells to check any that are a certain value and take some action based upon that values, or a number of sheets, or a number of workbooks.



Can you take your explanation and put a scenario to it or a previous reason you used looping?

Thanks again,

YLP

[Bob Phillips]

Here is an example that I answered earlier today. This is what the OP wanted.

Can anybody help me with a vb code that that do this:
I have let's say the rows : 1,2,3,4,5,6,7,8,9,10,11,12 .... the code should heep row 1 and delete 2,3,4; keep row 5 and delete 6,7,8; keep row 9 and delete 10,11,12 ... etc.

This was my solution, complete with a loop.



Sub DeleteData()
Dim iLastRow As Long
Dim i As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = ((iLastRow \ 4) - ((iLastRow Mod 4) <> 0)) * 4 To 1 Step -4
.Rows(i - 2).Resize(3).Delete
Next i
End With
End Sub

[YellowLabPro]

Ok. If you don't mind, can we expand out a bit so that I might try to understand each part?

1. The use of Long as the variable type? Long is listed in my book as a value type of -2.1 million to 2.1 million. You are looking at rows and columns where the value falls somewhere inbetween this range, is this a correct understanding of the variable assignment?

2. You are using looping, as based from your earlier explanation: in this case deleting repeatedly based on your results from the calculation and then ending it through the end of the sheet by finding the last row in "A" and counting up?


Looping is generally used when do a batch process, that is a process that is repeated a number of times. For instance, to go down a list of cells to check any that are a certain value and take some action based upon that values, or a number of sheets, or a number of workbooks.

Loops HAVE to be controlled, otherwise they go on forever, and you never get a look in. That is why we have a counter (For i = min to max), or a pre-defined collection (For each obj In Collection), or just test the condition on entry or exit (Do ... Loop Until condition, Do Until condition ... Loop, While condition ... Wend).

[Bob Phillips]

1. The use of Long as the variable type? Long is listed in my book as a value type of -2.1 million to 2.1 million. You are looking at rows and columns where the value falls somewhere inbetween this range, is this a correct understanding of the variable assignment?

Not really how I would put it. You need a variable to hold the incrementing/decrementing index within the loop, and you could use a numeric variable type (Integer, Long, Single, Double). I use Long as it is the most effeicient, the OS converts integers to long and back again, so best to save the effort.


2. You are using looping, as based from your earlier explanation: in this case deleting repeatedly based on your results from the calculation and then ending it through the end of the sheet by finding the last row in "A" and counting up?

Not a calculation in this case. as you notice, I find the last row by working bottom up, and then adjust this up to the next multiple of 4. I step thorugh the rows 4 at a time, deleting that and the 2 rows above it.

[YellowLabPro]

You need a variable to hold the incrementing/decrementing index within the loop,
1) I think I understand now. So the variable is associated w/ size of the value w/in the procedure?

2) I referred to the second instance as a calculation because of:

For i = ((iLastRow \ 4) - ((iLastRow Mod 4) <> 0)) * 4 To 1 Step -4

[Bob Phillips]

2) I referred to the second instance as a calculation because of:


I see your point Yelp. I was trying to distimnguish between testing based upon the value of a cell identified within the loop, and a characteristic of the loop itself (the index, the object or whatever).

I hope we are not goiung too deep into esoteric aspects of loops and losing the core purpose of same.

[YellowLabPro]

Thanks xld,
sorry for the delayed response, I have very inconsistent responses to my email account when a new post has been posted.

I can only speak for me in that understanding the purpose of something assists me greatly in its use and potential use. I am a bit slow in picking up on certain things, this being one of them. But you explaining this has helped. Thanks for taking the time to do so.
I will keep plugging away....
YLP

[mdmackillop]

Hi Yelp,
I came across this site (http://www.fontstuff.com/index.html) which has a few looping examples, amongst other things

[mdmackillop]

This is how you code without looping (http://www.vbaexpress.com/forum/showpost.php?p=92948&postcount=1), if you have the patience.

[YellowLabPro]

Ok, this is good. I looked at the site you posted yesterday, Martin Green, but have not been able to read through it yet. I am stuck working on something for work.... deadlines you know.

Thanks Malcom,

YLP

[YellowLabPro]

Malcolm,
Can you answer some questions about the linked procedure "Condense this code?" ?

1) Variable dimensioning? You dimmed Cel as a Range. This is not a data type, but an object, right? I have yet to find anything regarding variable types except for data types. If this in-fact is correct, then can variables be other things too?

2) You dimmed Cel as a Range. Is "Cel" a standard abbreviation for Cell, so as not to use cell, which would be not permitted by VBA?

3) The loop you wrote follows what the author posted, which I now see how this works now. But it is curious to me, from what I am seeing, it saves the activeworkbook to the path with the name of every cell in the range. Do you think the author meant to save an individual workbook for every cell or resave the workbook as 1 workbook w/ the most current cell data? If so, how would you do that. (this is not important, just a curiousity question.)

Thanks Malcolm for all your continued help and support,

YLP

[mdmackillop]

1. When you type in "Dim as" intellisense will present a whole list of options.
2. I use cel as a meaningful name. Variables can be almost anything but Keywords. You could use Cell, but I find that confusing
3. You might use this to make multiple workbooks using a date (or similar) as a file name. 1000 files seems OTT to me, but we don't know the OP's requirements.
You could easily set the code to return the last cell in column A, the most recent if a date (WorksheetFunction.Max) etc.

[Bob Phillips]

One thing you should understand about the object variables is that intellisense will give you the option to declare a variable type Range in Excel VBA, but would not in say Access VBA, or Powerpoint VBA (unless you have a reference to Excel from with there, and then the type would be Excel.Range. which it implicitly is even in Excel), because Range is not part of VB, it is specifically part of the Excel type library.

As to Martin's point about cell being a confusing name for a variable (not because it doesn't work, but because there is a Cells property), this is where a naming convention can help again. In my real code (as agaianst what I may post in forums) I use a variable type prefix, o for objects, i for indexes, c for counters etc. So I would use

Dim oCell As Range

or

Dim oSh as Worksheet

as generic variables, and

Dim oRngCopy As Range

as say a variable that will be loaded with a range to copy from.

[YellowLabPro]

Great help from both of you guys, thanks for the inside scoop and clarifications.

Bob, I really like the approach you took on oCell and oSh. I will start using that.

Best,

YLP

[mdmackillop]

As to Martin's point about cell being a confusing name
Not the only one, obviously! :rotflmao:

[YellowLabPro]

Not the only one, obviously! :rotflmao:

Now I don't know about you, but I don't think Bob, used your name procedure that you wrote for us last week....:beerchug:

[Bob Phillips]

Sorry Malcolm, me bad.

[YellowLabPro]

Tools > Options > General and make sure that 'Compile On Demand' option is checked and the 'Background Compile' option is not checked. {If it's unchecked visual basic is forced to always compile the code before it's run})

Mine is currently checked on both. What will deselecting the Background Compile checkbox do, eg. forcing VB to compile before its run is good why?

[johnske]

When background compile is checked, it's possible for your code to run before it's fully compiled (i.e. the remainder's being compiled in the background). In that case it's then possible for your code to make changes to (say) the worksheet before the program later crashes due to a compile error that would otherwise be picked up before any changes to the data were made.

[YellowLabPro]

In that case it's then possible for your code to make changes to (say) the worksheet before the program later crashes due to a compile error that would otherwise be picked up before any changes to the data were made.
If I understand your point, changes can be made to a sheet, potentially some remaining code is compiling while the first part of the code is performing its instructions, changes get made to a sheet, the code has an error in the latter part of the code, causing it to crash. Some changes have been made, but the code cannot continue to run so you are left in sort of like Purgatory?

[YellowLabPro]

What is the upside to having the code compiled in the background? Does it just begin sooner, or run faster?

[johnske]

It begins sooner, but (generally) runs slower :)

BTW, for finished code you only have to compile the very first time (provided you then save it) and everything then runs from the saved compiled code

[johnske]

It would also pay you to look through the articles section - try this one (http://vbaexpress.com/forum/showthread.php?t=9772)

[YellowLabPro]

Looking at the results, and possibly even stepping through each one, might shed a little more light for you.

Matt, this does help. Thanks for providing the three examples.

I have some follow up questions. These are for example 1, For Each - Dynamic Range.

1) Cnt is a counter? It is set to zero and is incremented by 1 each time the loop runs?

2) CLL is Cell?

3) The value "7" is placed in the cells.range beginning "A1" through "A30"

4) Then the loop, For Each begins in Cell "A1" and starts counting. For every itiration, the counter increments by one, once it reaches 15, it inserts a row, and continues counting. The total count is 31, not because there is data, but because initially the last cell was established by finding data in the last cell, which established the range. Then the counter continues counting all the cells remaining in the range which has now been increased to row 31. This result is presented in the immediate window of the total cells it looped through.

This is what I see. If I misunderstood anything please correct me. I have not begun to compare the next two processes. I will get back to these and let you know what I see.

Thanks again Matt,

YLP

[Bob Phillips]

1) Cnt is a counter? It is set to zero and is incremented by 1 each time the loop runs?

In VBA it is not necessary to initialise as numeric variables default to 0. Matt is just being cautious (and applying good programming practices IMO, as not all systems will guarantee an initialised variable, and if you re-use later, you must set to 0).

The counter has to be manually incremented as the loop is of the For ... Next type.


2) CLL is Cell?

More accurately, CLL is a variable of type Range, referencing a particular cell, the next in the loop.


3) The value "7" is placed in the cells.range beginning "A1" through "A30"

In every cell within that range.


4) Then the loop, For Each begins in Cell "A1" and starts counting. For every itiration, the counter increments by one, once it reaches 15, it inserts a row, and continues counting. The total count is 31, not because there is data, but because initially the last cell was established by finding data in the last cell, which established the range. Then the counter continues counting all the cells remaining in the range which has now been increased to row 31. This result is presented in the immediate window of the total cells it looped through

Thew total count starts at 30, as the UP statement takes you to the last cxell WITH data, and as you say, when Cnt gets to 15 a new row is inserted, but because a For ... Next loop is being used against the cell, the newly inserted row then gets processed within the loop.

[YellowLabPro]

Thanks xld,
confirmation helps to ensure I am heading down the right path.

[mvidas]

Thanks for the explanation, Bob :) I was sleeping at the time yelp posted that (1:12am), wouldn't have gotten to it until now


In VBA it is not necessary to initialise as numeric variables default to 0. Matt is just being cautious (and applying good programming practices IMO, as not all systems will guarantee an initialised variable, and if you re-use later, you must set to 0).As I write as much VBS nowadays as I do VBA, I've basically just intermingled the way I write it, also helps to show people what the actual starting point is in case it is changed to a public variable for whatever reason (though I usually just use Cnt for array counters, and I'd never make that public, but thats another story)
Matt