Why are loops such a pain in VBA?

[JMJimmy]

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.

   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:

   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

   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:

$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?

 

[xenou]

        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.

 

[Rick Rothstein]

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

   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

 

[xenou]

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?

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:

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

 

[JMJimmy]

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

        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:

    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

 

[xenou]

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

 

[Rick Rothstein]

Whoops! I wasn't even looking up there for the problem. How about this modification then...

   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

 

[mikerickson]

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

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.

 

[Jaymond Flurrie]

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

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.

 

[xenou]

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.