LastValue Function

[Bob Doyle]

I am trying to write a function to return the last (most recent prior)
value of a reference cell. For instance, say A1 is the reference cell
and B1 contains the LastValue function referencing A1. If A1 last
previous value was, say, 5, and it is now 10, B1 would show the value
5. If A1 were to change to 20, say, then B1 would show 10.

I've written a function that seems to work as long as it is the only
instance of the function in the workbook. However, since it relies on
static variables, that are static at the module, rather than at the
procedure or function level, any additional instance of the function
within the spreadsheet references the same static variables and causes
both instances of the LastValue function to fail to achieve the desired
goal.

Any ideas how to fix this as a function (rather than by using coding
with the worksheet_change subroutine)?

Function LastValue(RefCell As Range)
Static CurrentValue
Static PriorValue
PriorValue = CurrentValue
CurrentValue = RefCell
LastValue = PriorValue
End Function

Bob Doyle

 

[Bob Phillips]

Why not using worksheet change?

As this could apply to any cell, you need to be able to save any cell's
value. Worksheet change would be useful, as that can trap that change. The
UDF should then be simple.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tim Williams]

You could create a static dictionary object and stash the values in
that using the cell address as a key.
If you have more than one sheet that might be a problem though....

Using a sheet-level event would be much easier.


Tim.





"Bob Doyle" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Doyle]

Tim and Bob

Thanks for your suggestions.

However, I'm looking for a function solution, if it exists, so that
can use the function anywhere, anytime, without having to do an
special worksheet-level event handlers that are, by necessity, uniqu
to each sheet in which I would want to trap values.

In other words, I want a generic function to return the most recen
prior value of a reference cell that I or anyone else could use jus
like any other funtion in any worksheet or any workbook without an
special coding.

Is there perhaps some way to do this by having the function stor
values in a comment to the cell in which the funtion resides and the
retrieve and update the values from the comment each time the value i
the reference cell changes or by some other such "indirect" method

 

[Bob Phillips]

You could create workbook events, ore even application events.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tim Williams]

A function cannot modify the environment (eg by updating a cell comment) -
it can only return a value.

What are your needs as far as persisting the "previous" values? Should they
be available even between sessions?