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?

 

[Norie]

Just out of curiousity, but how is the data organised?

Is it all just in one column?

ie the item and value are in the same column?

 

[Jaymond Flurrie]

Not an expert in PHP, but what in the example code does comparisons based on colors?

 

[Norie]

The reason VBA doesn't like this is that A2 should be in quotes

 With Worksheets(Worksheet).Range(A2)

should be

With Worksheets(Worksheet).Range("A2")

 

[xenou]

Ah - good catch Norie. To follow upon,

Range() syntax requires the address in quotes: Worksheets(1).Range("A1")
Cells() syntax works with row, column numbers: Worksheets(1).Cells(1,1)

Though you can also use shortcut syntax, set reference variables, use variables instead of text or numeric literals, construct a range reference out of other range references, etc. etc. etc.


@Jaymond: I'm not an expert in PHP but I'd guess we get that from the use of a SQL-based fetch operation, which we then interrogate for it's field values (cost, type of cost). It would be relatively easy drum up with ADO or DAO, assuming we have a database with type and cost (with or without loops). But methinks you are correct that the PHP example doesn't rely on colors so it's not the same as working with a range object and color-formatted cells.

 

[Norie]

The PHP doesn't actualy have any conditions.

There are three fields, x_cost, y_cost and shared_cost that are respectively added to variables x_total, y_total and total respectively.

All the fields are added to the variable grand_total.

The first part could probably be done in the SQL with an aggregrate query.

PS Apologies, all that's probably really obvious though.