Totaling

Check out this worksheet:

Here's code for the button:

1. Private Sub cmdRun_Click()
2.     Dim total As Single
3.     Dim row As Integer
4.     total = 0
5.     For row = 1 To 5
6.         total = total + Cells(row, 1)
7.     Next row
8.     Cells(8, 2) = total
9. End Sub

total is initialized to zero. Then the loop starts. The CPU runs line 6 again and again. The first time, row is 1. Then 2, and so on, up to 5. Line 6 grabs Cells(row, 1) and adds it to total. The first time through the loop, row is 1, so Cells(1, 1) is added to total. The next time through the loop, Cells(2, 1) is added, and so on, up to Cells(5, 1).

total is an accumulator. It's a variable that tracks something about a data set during a loop.

Accumulators should always be initialized. In this program, that's in line 4:

1. Private Sub cmdRun_Click()
2.     Dim total As Single
3.     Dim row As Integer
4.     total = 0
5.     For row = 1 To 5
6.         total = total + Cells(row, 1)
7.     Next row
8.     Cells(8, 2) = total
9. End Sub

total is added to each time through the loop, with a different cell.

1. Private Sub cmdRun_Click()
2.     Dim total As Single
3.     Dim row As Integer
4.     total = 0
5.     For row = 1 To 5
6.         total = total + Cells(row, 1)
7.     Next row
8.     Cells(8, 2) = total
9. End Sub

At the end of the loop, total describes something about the entire data set.

Pattern

Accumulator

Situation:

You want to summarize some aspect of a data set, like the average, or highest value.

Actions:

Use a variable as an accumulator.

• Initialize the variable.
• Loop over the cells you want to process. Update the value of the accumulator each time through the loop.
• After the loop, the accumulator has the value you want.

More…

Don't rely on the value of the index variable (row in the example) after a For-Next loop. It's predictable in some cases, but not in others.

Totals that aren't

In the example, total is the simple sum of the cells:

total = total + Cells(row, 1)

But it doesn't have to be. For example, in statistics, you sometimes want the sum of squares. That is, take each value, square it, and add those up. It's used in, for example, calculating standard deviations.

This line would compute the sum of squares:

sumSq = sumSq + Cells(row, 1) * Cells(row, 1)

You can do both calculations at once:

1. Private Sub cmdRun_Click()
2.     Dim total As Single
3.     dim sumSq as Single
4.     Dim row As Integer
5.     total = 0
6.     sumSq = 0
7.     For row = 1 To 5
8.         total = total + Cells(row, 1)
9.         sumSq = sumSq + Cells(row, 1) * Cells(row, 1)
10.     Next row
11.     Cells(8, 2) = total
12.     Cells(8, 3) = sumSq
13. End Sub

1. Private Sub cmdRun_Click()
2.     Dim sum As Single
3.     dim sumSq as Single
4.     Dim row As Integer
5.     sum = 0
6.     sumSq = 0
7.     For row = 1 To 5
8.         sum = sum + Cells(row, 1)
9.         sumSq = sumSq + Cells(row, 1) * Cells(row, 1)
10.     Next row
11.     Cells(8, 2) = sum
12.     Cells(8, 3) = sumSq
13. End Sub

Highest

Accumulators aren't just for totals. You can use them for all sorts of things, like finding the highest value. Here's that worksheet again:

Here's the code:

1. Private Sub cmdRun_Click()
2.     Dim highest As Single
3.     Dim row As Integer
4.     highest = Cells(1, 1)
5.     For row = 2 To 5
6.         If Cells(row, 1) > highest Then
7.             highest = Cells(row, 1)
8.         End If
9.     Next row
10.     Cells(8, 2) = highest
11. End Sub

highest is an accumulator. As the loop runs, it holds the highest value that the program has found so far. At the end of the loop, it holds the highest value in the entire data set.

The initialization is different:

1. Private Sub cmdRun_Click()
2.     Dim highest As Single
3.     Dim row As Integer
4.     highest = Cells(1, 1)
5.     For row = 2 To 5
6.         If Cells(row, 1) > highest Then
7.             highest = Cells(row, 1)
8.         End If
9.     Next row
10.     Cells(8, 2) = highest
11. End Sub

Rather than initializing to 0, the program grabs the first data value for highest. Then it loops through the rest of the data, comparing each element with highest.

Cell with the highest

Suppose that we didn't want to know the highest number, but the cell that contained the highest number. So for this…

… it's not 92 we want, it's 4. That's the row with the highest number in it.

Easy peasy.

1. Private Sub cmdRun_Click()
2.     Dim highestCell As Integer
3.     Dim row As Integer
4.     highestCell = 1
5.     For row = 2 To 5
6.         If Cells(row, 1) > Cells(highestCell, 1) Then
7.             highestCell = row
8.         End If
9.     Next row
10.     Cells(8, 2) = highestCell
11. End Sub

Instead of using an accumulator to track the highest value, we use an accumulator to track the cell with the highest value.

Lowest

Almost the same as the highest code. Let's add to the code we have.

1. Private Sub cmdRun_Click()
2.     Dim highest As Single
3.     Dim lowest As Single
4.     Dim row As Integer
5.     highest = Cells(1, 1)
6.     lowest = Cells(1, 1)
7.     For row = 2 To 5
8.         If Cells(row, 1) > highest Then
9.             highest = Cells(row, 1)
10.         End If
11.         If Cells(row, 1) < lowest Then
12.             lowest = Cells(row, 1)
13.         End If
14.     Next row
15.     Cells(8, 2) = highest
16.     Cells(9, 2) = lowest
17. End Sub

Exercises

Summary

An accumulator is a variable that tracks something about a data set during a loop. Accumulators should always be initialized.