Accumulator (Range)

Tags
Summary

Use a For loop to run through a range, adding (or counting) as you go.

Situation

You have records in a worksheet, and you want to find out something about the entire record set, like the average of a field.

Needs

A Range object, representing records in the worksheet.

Provides

A variable with a characteristic of the record set, like a field's total, or a record count.

Action

Use the Create a Range pattern to, er, create a Range. Then:

  • Dim sTotalHappiness As Single
  • Dim sTotalCoolness As Single
  • Dim sNumberOfRecordsInRange As Single
  • Dim sCurrentRecordNumber As Single
  •  
  • ...
  •  
  • 'Get the number of rows in the range.
  • sNumberOfRecordsInRange = rGoatDataTable.Rows.Count
  •  
  • 'Initialize totals.
  • sTotalHappiness = 0
  • sTotalCoolness = 0
  •  
  • 'Compute totals
  • For sCurrentRecordNumber = 1 To sNumberOfRecordsInRange
  •     sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)
  •     sTotalCoolness = sTotalCoolness + rGoatDataTable.Cells(sCurrentRecordNumber, COOLNESS_FIELD)
  • Next sCurrentRecordNumber

Use other patterns to limit the data you process. For example, you can validate the records, and filter to process a subset.

Where referenced