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

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

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"
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

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 Doyl
-----------------------------------------------------------------------
Bob Doyle's Profile: http://www.excelforum.com/member.php...fo&userid=2547
View this thread: http://www.excelforum.com/showthread.php?threadid=38921

You could create workbook events, ore even application events.

--

HTH

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


"Bob Doyle" wrote
in message ...

Tim and Bob

Thanks for your suggestions.

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

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

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


--
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

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?

--
Tim Williams
Palo Alto, CA


"Bob Doyle" wrote
in message ...

Tim and Bob

Thanks for your suggestions.

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

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

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


--
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