# Why are loops such a pain in VBA?

#### JMJimmy

##### New Member
I've been working on a cashflow which ZVI was kind enough to help me get started, however, after working with it a bit I found the code didn't handle things the way I needed.

What the below code does is loop through a given column, totalling the rows based on cell colour. Yellow = y_total, light purple = x_total, everything else is added to total and it's all added into the grand_total.

Code:
``````   With Worksheets(Worksheet).Range(start_cell)
Do Until IsEmpty(.Offset(i).Value)
With (.Offset(i))
v = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + v
ElseIf CIndex = 36 Then
y_total = y_total + v
Else
total = total + v
End If
grand_total = grand_total + v
i = i + 1
End With
Loop
End With``````

Works great, except that not every item is purchased every month so there are blank cells throughout the data.

So I thought, simple enough, just a quick modification should solve things:

Code:
``````   With Worksheets(Worksheet).Range(A2)
Do Until IsEmpty(.Offset(i).Value)
With Worksheets(Worksheet).Range(start_cell)
With (.Offset(i))
v = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + v
ElseIf CIndex = 36 Then
y_total = y_total + v
Else
total = total + v
End If
grand_total = grand_total + v
i = i + 1
End With
End With
Loop
End With``````

So rather than the loop going through the pricing data which has blank cells it goes through the item names which doesn't have blank cells. Then, inside the loop with the pricing column add up the totals. Seems to make sense but vba doesn't like it for whatever reason.

So, trying a different route... lets change the offset to reference the A column

Code:
``````   With Worksheets(Worksheet).Range(start_cell)
Do Until IsEmpty(Offset(A2, i, 0, 1, 1).Value)
With (.Offset(i))
v = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + v
ElseIf CIndex = 36 Then
y_total = y_total + v
Else
total = total + v
End If
grand_total = grand_total + v
i = i + 1
End With
Loop
End With``````

Doesn't like this either, and I'm assuming it's because A column is not part of the with statement's object.

This went on and on... for each loops, .End(xlUp), etc, etc - something wrong with each and every method and most of them spitting out generic/unhelpful errors.

Meanwhile in php/mysql the exact same operation is as simple as:

Code:
``````\$x = query("SELECT * FROM expenses WHERE type = \$type AND month = \$month);
while(fetch_assoc(\$x))
{
\$x_total += \$x['x_cost'];
\$y_total += \$x['y_cost'];
\$total += \$x['shared_cost'];
\$grand_total += \$x['shared_cost'] + \$x['x_cost'] + \$x['y_cost'];
}``````

Is it just me or should there not be a much simpler way to loop to the end of a set of data in a macro?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Code:
``        Do Until IsEmpty(.Offset(i).Value)``

This will stop at any empty cell. Short answer is: do not use formatting to indicate data values (data vs. presentation). You are looping a spreadsheet, not an PHP array.

Just guessing here, but maybe this modification to the first code snippet you posted....

Code:
``````   With Worksheets(Worksheet).Range(start_cell)
Do Until IsEmpty(.Offset(i).Value)
With (.Offset(i))
v = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + v
ElseIf CIndex = 36 Then
y_total = y_total + v
ElseIf[SIZE=3][COLOR=red][B] V <> ""[/B][/COLOR][/SIZE]
total = total + v
End If
grand_total = grand_total + v
i = i + 1
End With
Loop
End With``````

Is it just me or should there not be a much simpler way to loop to the end of a set of data in a macro?

Code:
``````Dim r As Range
Dim c as Range

Set r = Range("A1:A10")
For each c in r
msgbox C.value
next c``````

If the last row is unknown:
Code:
``````Dim r As Range
Dim c as Range
Dim LR as Long

LR = Worksheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row
With Worksheets("Sheet1")
Set r = .Range(.Cells(1,1),.Cells(LR,1))
For each c in r
msgbox C.value
next c``````

@Rick: The code would never reach that ElseIf as it would break out on the Do Until

Code:
``        Do Until IsEmpty(.Offset(i).Value)``

This will stop at any empty cell. Short answer is: do not use formatting to indicate data values (data vs. presentation). You are looping a spreadsheet, not an PHP array.

This is true, however, in this particular case there are already column headers and row identifiers so the complexity of the data and code would balloon without using presentation to give meaning to the data. Not best practice but this is a home project not a business solution.

As to looping a spreadsheet and a PHP array... they are fundamentally the same thing - a two dimensional list of values without a fixed end point.

Shortly after I posted this I stumbled into the solution which is far more simple:

Code:
``````    LastRow = Worksheets(Worksheet).UsedRange.Rows.Count

With Worksheets(Worksheet).Range(start_cell)
Do Until i = LastRow
With (.Offset(i))
v = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + v
ElseIf CIndex = 36 Then
y_total = y_total + v
Else
total = total + v
End If
grand_total = grand_total + v
i = i + 1
End With
Loop
End With``````

As to looping a spreadsheet and a PHP array... they are fundamentally the same thing - a two dimensional list of values without a fixed end point.

Are you saying a PHP array has both a color and an IsEmpty() value? If so ... my bad. Otherwise, no, not the same, as it turns out. Though if you want to be "fundamental" about it, it is all ones and zeros.
This is true, however, in this particular case there are already column headers and row identifiers so the complexity of the data and code would balloon without using presentation to give meaning to the data. Not best practice but this is a home project not a business solution.
Even for a home project I would not recommend using color as a way to store data. For instance, with cell values storing data you can use sum or sumif formulas to add up data, which requires no VBA loops at all. It may be that newer versions of Excel add up color-formatted cells (if so, I'm mistaken again, although the the issue is then complicated by the fact that we have many more colors now too).

Note: Personally I like PHP but I don't go to PHP forums asking why it is such a pain compared to VBA. I can't see any reason why you come to an Excel forum asking why it isn't as great as PHP - these are not the same things, regardless of any "fundamentals".

Last edited:

Code:
``````   With Worksheets(Worksheet).Range(start_cell)
Do Until [SIZE=3][COLOR=red][B].Offset(I).Row > Cells(Rows.Count, .Column).End(xlUp).Row[/B][/COLOR][/SIZE]
With (.Offset(I))
V = .Value
CIndex = .Interior.ColorIndex
If CIndex = 39 Then
x_total = x_total + V
ElseIf CIndex = 36 Then
y_total = y_total + V
ElseIf [SIZE=3][COLOR=red][B]V <> "" Then[/B][/COLOR][/SIZE]
total = total + V
End If
grand_total = grand_total + V
I = I + 1
End With
Loop
End With``````

I've heard that Do Loop is the slowest of the looping methods. Perhaps

Code:
``````Dim oneCell As Range

With Worksheets(Worksheet).Range(start_cell)

For Each oneCell In Range(.Cells, .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
With oneCell

v = Val(CStr(.Value))
cIndex = .Interior.ColorIndex

If cIndex = 39 Then
x_total = x_total + v
ElseIf cIndex = 36 Then
y_total = y_total + v
ElseIf v <> "" Then
Total = Total + v
End If

grand_total = grand_total + v
End With
Next oneCell

End With``````

None of these codes will work if the cells get their color from Conditional Formatting.

I agree with xenou, color should not be used as data in Excel.

Last edited:
I've heard that Do Loop is the slowest of the looping methods. Perhaps

Code:
``````Dim oneCell As Range

With Worksheets(Worksheet).Range(start_cell)

For Each oneCell In Range(.Cells, .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
With oneCell

v = Val(CStr(.Value))
cIndex = .Interior.ColorIndex

If cIndex = 39 Then
x_total = x_total + v
ElseIf cIndex = 36 Then
y_total = y_total + v
ElseIf v <> "" Then
Total = Total + v
End If

grand_total = grand_total + v
End With
Next oneCell

End With``````

None of these codes will work if the cells get their color from Conditional Formatting.

I agree with xenou, color should not be used as data in Excel.

I agree with this too. Personally I always use the "take end towards start from the last possible cell" and get the range from there, then do the loop for each cell (in an array) and start with "if not vbnullstring" to check if there is anything.

But colors should be only used when you print out the final data and want to format it. They are great in presenting data, but horrible for processing it. Think for example using "just yellow" - what if the next person understands the yellow a bit or two differently than you do? In representing data it's not a big deal, but when you compare if yellow is yellow, then you easily run into problems.

JMJimmy - sorry about being a little harsh. I was (or am) a little annoyed because the problem isn't about looping being a pain in VBA. It's just a matter of setting the range properly so you do loop through all the cells. Also working with cells and worksheets is not quite the same as just having number (primitive values) in an array: ranges are complex objects with various idiosyncracies because of their nature as members of a GUI spreadsheet application. Doing a loop is basically easy in VBA. Best practices with VBA programming and Excel is not, however, something quite so easy. Anyway, it sounds like you found a good answer quickly on your own anyway.

Replies
2
Views
37
Replies
5
Views
377
Replies
3
Views
154
Replies
4
Views
172
Replies
0
Views
49

1,203,125
Messages
6,053,655
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back