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)


"Bob Doyle" wrote
in message ...

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 Doyle
------------------------------------------------------------------------
Bob Doyle's Profile:
http://www.excelforum.com/member.php...o&userid=25473
View this thread: http://www.excelforum.com/showthread...hreadid=389218