Accumulator

Tags
Summary

Loop over a data table. For each record, add a field to a total, count, or whatevs.

Situation

You have a data set, and you want to total up or count the values.

Needs

A data set.

Provides

A variable with a total or count.

Action

An example, totaling goat weight in an array:

  1. sTotalWeight = 0
  2. For sCurrentRecordNumber = 1 To sNumberOfRecords
  3.   sTotalWeight = sTotalWeight + asGoatWeight(sCurrentRecordNumber)
  4. Next sCurrentRecordNumber
  5. MsgBox "Total weight: " & sTotalWeight

Notice that sTotalWeight is initialized in line 1.

Often used with a filter, like this one to count light goats:

  1. sCountLightGoats = 0
  2. For sCurrentRecordNumber = 1 To sNumberOfRecords
  3.   If asGoatWeight(sCurrentRecordNumber) < 20 Then
  4.       sCountLightGoats = sCountLightGoats + 1
  5.   EndIf
  6. Next sCurrentRecordNumber
  7. MsgBox "Number of light goats: " & sCountLightGoats

The If statement in line 3 accesses the data in asGoatWeight() directly. If there are several fields in each record, you might put the field values in their own variables, if that simplifies things for you.

  1. sCountLightGoats = 0
  2. sCountTallGoats = 0
  3. sCountCuteGoats = 0
  4. sCountHappyGoats = 0
  5. For sCurrentRecordNumber = 1 To sNumberOfRecords
  6.   ' Get fields of current record.
  7.   sCurrentGoatWeight = asGoatWeight(sCurrentRecordNumber)
  8.   sCurrentGoatHeight = asGoatHeight(sCurrentRecordNumber)
  9.   sCurrentGoatCuteness = asGoatCuteness(sCurrentRecordNumber)
  10.   sCurrentGoatHappiness = asGoatHappiness(sCurrentRecordNumber)
  11.   ' Process fields for current record.
  12.   If sCurrentGoatWeight < 20 Then
  13.       sCountLightGoats = sCountLightGoats + 1
  14.   EndIf
  15.   If sCurrentGoatHeight > 40 Then
  16.       sCountTallGoats= sCountTallGoats+ 1
  17.   EndIf
  18.   ...
  19. Next sCurrentRecordNumber

Range processing is the same, except for the way fields are accessed.

  1. ...
  2. For sCurrentRecordNumber = 1 To sNumberOfRecords
  3.   ' Get fields of current record.
  4.   sCurrentGoatWeight = rDataRange.Cells(sCurrentRecordNumber, 1)
  5.   sCurrentGoatHeight = rDataRange.Cells(sCurrentRecordNumber, 2)
  6.   sCurrentGoatCuteness = rDataRange.Cells(sCurrentRecordNumber, 3)
  7.   sCurrentGoatHappiness = rDataRange.Cells(sCurrentRecordNumber, 4)
  8.   ' Process fields for current record.
  9.   ...
  10. Next sCurrentRecordNumber
Where referenced