I've got a vba function with one static variable. Is there any way the
value in the static variable can be unique to the cell calling the
function? That is, is there any way to force Excel to keep a separate
static variable for each cell that calls this function?

In short no, but maybe you can circumvent it.

If there will not be many, you could create a workbook name say Sheet1_A1
and store the value there, and update it. If there are too many, is it
feasible to use cell comments to store it?

--

HTH

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


wrote in message
oups.com...
I've got a vba function with one static variable. Is there any way the
value in the static variable can be unique to the cell calling the
function? That is, is there any way to force Excel to keep a separate
static variable for each cell that calls this function?

Your comment storage solution sounds like it might work. Two
questions:
How do I find out the cell address from which the function is being
called?
and How do I store/retrieve the value in a cell comment?

Thanks,
Marc

Marc,

Actually that won't work at all. I was missing a beat when I suggested it,
because a worksheet function cannot modify it's environment, not setting
cell attributes (such as comments) or worksheet/workbook attributes (such as
names).

You could always use worksheet events, but it would depend upon what and how
your function works.

Apologies for that.

--

HTH

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


wrote in message
oups.com...
Your comment storage solution sounds like it might work. Two
questions:
How do I find out the cell address from which the function is being
called?
and How do I store/retrieve the value in a cell comment?

Thanks,
Marc

Oh well, thanks for the stab at it though. I think I'll just go with
my first kludgie idea, which is to pass in a second argument so that
the function knows where it's being called from, and then store the
static info in an array to match against the extra argument.

-Marc

How will you save the data over different sessions of Excel?

--

HTH

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


wrote in message
ups.com...
Oh well, thanks for the stab at it though. I think I'll just go with
my first kludgie idea, which is to pass in a second argument so that
the function knows where it's being called from, and then store the
static info in an array to match against the extra argument.

-Marc

On 14 Mar 2005 10:59:53 -0800, "
wrote:

I've got a vba function with one static variable. Is there any way the
value in the static variable can be unique to the cell calling the
function? That is, is there any way to force Excel to keep a separate
static variable for each cell that calls this function?

Look at the Caller Property of the Application object to obtain the address
from of the cell calling the function.

For example:

Function foo()
Dim StaticVariable As Double

Select Case Application.Caller.Address
Case Is = "$I$1"
StaticVariable = 1
Case Is = "$I$7"
StaticVariable = 7
Case Else
StaticVariable = 0
End Select

foo = StaticVariable
End Function


--ron