This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
An accumulator is a cell or cells that keep a running sum of data entered. They can be single cell, so that if A1=6 and the user enters 2 into A1, the result displayed is 8. Or they can be two-celled, where, say, numbers entered in A1 are immediately added to the value in B1. I dislike accumulators on a spreadsheet, primarily because they retain no history - if an error is made, it may be impossible to track down in an accumulator, where if the numbers were entered sequentially in a column of cells, they could be examined and corrected. Nonetheless, there are times and places where an accumulator is desirable.
The most reliable way to build an accumulator is with a Worksheet_Change() event macro. When a value is input into the cell, that value is added to the accumulated value and placed back in the accumulator cell.
In the two cell accumulator, entries are made in one cell and the total is displayed in a second cell. To reset the accumulator, the value in the accumulator cell can be cleared manually. Here's one way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
In a single cell accumulator it is especially important to disable events before writing the value back to the cell - otherwise the loop will continue until XL runs out of stack space. In addition, since the accumulator cell's value is being overwritten by the user, a static variable is necessary. It is also desirable to provide a way to clear the accumulator. Here's one way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static dAccumulator As Double
With Target
If .Address(False, False) = "A1" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End If
End With
End Sub
The accumulator is cleared by entering a blank or non-numeric value.
A two cell accumulator can also be constructed without VBA using circular references. To avoid an error, first choose Tools/Options/Calculation (Mac: Preferences/Calculation) and check the Iterations checkbox. Enter 1 in the number of iterations box. Now enter the following
B1: =A1 + B1
B1 will immedately take on the value of A1, then will update every time A1 is changed (or the entire worksheet is calculated). Note that it is very difficult to clear this accumulator - deleting the value in B1 deletes the formula. One can either enter the negative of B1 or edit the cell.
More complex accumulators can be constructed that add conditionally (e.g., only if A1 > B1), but in general, this type of accumulator is unreliable since you can't always prevent the user from recalculating, and there's little or no opportunity to make corrections.
This page last updated on
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Building an accumulator with worksheet functions.
New to macros? See David McRitchie's Getting Started with Macros and User Defined Functions