Back in my newsgroup days, about once a month someone would ask how to have a formula that accumulated entries from another cell. For example, put a formula in C3 that refers to A3. Every time a value is entered in A3, have the value in C3 change by that amount. Impossible right?
The standard answer is that you can’t and that you should use a Worksheet_Change event to monitor A3. When it changes, change the value in C3. Then I read Charles Williams’ comments about the Text property
When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference).
I had no idea that was true. Now I can write a UDF like this:
Function UpDown(dChange As Double) As Double
Dim sOld As String
Application.Volatile True
sOld = Application.Caller.Text
UpDown = Val(sOld) + dChange
End Function
In C3, I put =updown(A3). Whenever I change A3, C3 changes by that amount. I’m sure there are all kinds of problems with this – dates and errors to name two – but I thought it was interesting nonetheless. Apologies if the entire Excel universe knows this already, but it’s new to me.
That’s interesting. I didn’t know that either. Curious about the volatility in your function though.
This approach, if I understand it correctly, will have a problem when the workbook is closed and reopened, because C3 will be recalculated then, even though A3 hasn’t changed. Changing the volatility to False seems to fix that, but it will still cause a wrong result if forcing a full recalc of the workbook.
Very interesting…I got all sorts of weird results initially – C3 changes by the amount in A3 even when a cell other than A3 is being changed. I changed to this:
Function UpDown(Change As Range) As DoubleDim dOld As Double
dOld = Val(Application.Caller.Text)
On Error Resume Next
If IsError(CDbl(Change)) Then
UpDown = dOld
Else
UpDown = dOld + Change
End If
End Function
It still doesn't recognize a change if you input the same number twice in a row, otherwise seems to work.
Hello,
Can you make a circular reference to =A3+C3 after you change in Excels option to allow for iterative calculations and only allow 1. Then when you change A3 it adds to C3. Is there a down side to doing it in this fashion?
Thanks,
Bryan B
thats ok, but you should get your user to update both cells!!! ;-)
There is one problem for sure…. lets say you start with 100, then put 2, 3, and 5. This will update in C3…
Post this, if you keep on putting 5 and press enter…nothing changes…basically the function is not called again unless the new value is different from the previous one…
So, 100, 1,2,3 will work…
100, 1, 2,2,2, will not work.
Not sure why..
~VijaySharma
I think you need to remove the Application.Volatile, otherwise the UDF will accumulate on every recalc rather than just when you change dChange
Good points about volatility. I originally had it non-volatile, but as Vijay points out typing in the same number doesn’t force a recalc. So I made it volatile. I guess the right answer is not to do this. :)
This will work even when you don’t change the actual value.
So you can enter:
100, 1,2,3
100, 1, 2,2,2
and both work.
Public Function UpDown(Change As Range) As Double
Application.Volatile True
Dim dOld As Double
Dim rSelection As Range
dOld = Val(Application.Caller.Text)
Set rSelection = Selection
On Error Resume Next
If IsError(CDbl(Change)) Then
UpDown = dOld
ElseIf rSelection.Address = Change.Address Then
UpDown = dOld + Change
Else
UpDown = dOld
End If
End Function
Let me try this one more time:
This will work even when you don’t change the actual value.
So you can enter:
100, 1,2,3
100, 1, 2,2,2
and both work.
Public Function UpDown(Change As Range) As Double
Application.Volatile True
Dim dOld As Double
Dim rSelection As Range
dOld = Val(Application.Caller.Text)
Set rSelection = Selection
On Error Resume Next
If IsError(CDbl(Change)) Then
UpDown = dOld
ElseIf rSelection.Address = Change.Address Then
UpDown = dOld + Change
Else
UpDown = dOld
End If
End Function
One more addition to what I wrote in my code. Instead of using .Address, it would probably be smarter to us .Address(,,,true), that way you don’t change some cell on a different sheet and it ends up updated you code when you shouldn’t.
OK, here’s a round about way to store your previous value so when you open your workbook it doesn’t recalculate to 0. Don’t know if there is a good solution to that one, it would be interesting to see one.
Public Function UpDown(Change As Range, dPreviousValue As Double) As Double
Application.Volatile True
Dim bContinue As Boolean
Dim dOld As Double
Dim rSelection As Range
bContinue = Application.Caller.Text <> "#NAME?"
On Error Resume Next
If bContinue Then
dOld = Val(Application.Caller.Text)
Set rSelection = Selection
If IsError(CDbl(Change)) Then
UpDown = dOld
ElseIf rSelection.Address(, , , True) = Change.Address(, , , True) Then
UpDown = dOld + Change
Else
UpDown = dOld
End If
Else
UpDown = dPreviousValue
End If
End Function
Keeping the values straight when you open a file is a real headache. This is where event handlers do a better job. Still, it’s a PITA to put all the logic into VBA if you need several different accumulators.
One way to handle this is to use an additional worksheet set up as a 2-column table. The first column has worksheet!cell addresses for cells to be accumulated, and the second column has the current accumulated value. You can make the addresses in the first column sensitive to move (CUT and paste) operations by using =CELL(“Address”,cell_ref). Use SheetCalculate to update values in the second column of the table where the changed cell’s (Target) address appears in the first column. Use a UDF like the following to fetch accumulated values from the table.
Function accum(r As Range) As VariantDim k As Long
With ThisWorkbook.Names("TblAccumulators").RefersToRange
On Error Resume Next
k = Application.Match(r.Address(1, 1, xlA1, 1), .Columns(1), 0)
On Error GoTo 0
If k > 0 Then
accum = .Cells(k, 2)
Else
Err.Clear
accum = CVErr(xlErrNA)
End If
End With
End Function
Keeping the values straight when you open a file is a real headache. This is where event handlers do a better job. Still, it’s a PITA to put all the logic into VBA if you need several different accumulators.
One way to handle this is to use an additional worksheet set up as a 2-column table. The first column has worksheet!cell addresses for cells to be accumulated, and the second column has the current accumulated value. You can make the addresses in the first column sensitive to move (CUT and paste) operations by using =CELL(“Address”,cell_ref). Use SheetCalculate to update values in the second column of the table where the changed cell’s (Target) address appears in the first column. Use a UDF like the following to fetch accumulated values from the table.
Function accum(r As Range) As VariantDim k As Long
With ThisWorkbook.Names("TblAccumulators").RefersToRange
On Error Resume Next
k = Application.Match(r.Address(1, 1, xlA1, 1), .Columns(1), 0)
On Error GoTo 0
If k > 0 Then
accum = .Cells(k, 2)
Else
Err.Clear
accum = CVErr(xlErrNA)
End If
End With
End Function
Sorry. Forgot the code tag syntax. The udf takes a range ref argument to the cell being accumulated, uses its address to look up into the table, and returns the accumulated value if the address is found or #N/A otherwise.
Interesting, I didn’t know about the .Text property.
Now for extra credit, how would you do the same thing without any code? Impossible right?
A trick to bypass circular references is to use data tables with the following setup:
A3 Value B3 =A3+C3 C3 {=TABLE(B2,)}Where C3 is populated by selecting B2:C3 and choosing Data Table… with row input cell: B2.
Changing values in A3 gave the same results as the Updown() udf in tests, you can reset values by clearing B3 and undoing. The same technique can be utilised to create a list of all previous cell values down column C. To do this enter down column B from B3: =A3 =C3 =C4 =C5 =C6 … and extend the data table down.