Hi,

Suppose my function makes a reference to a range (named "MyRange"). So, in a
way, the function depends on it. Is there a way to cause the function to
recalculate when the vlaue of "MyRange" changes? I don't want to make
volatile since too many cells depend on it and "MyRange" changes
infrequently.

Thanks!

Bura

Hi Bura

That is the default behavior. Try this:

Function Half(WholeValue As Double) As Double
Half = WholeValue / 2
End Function

In Cell B1:
=Half(A1)

and then in A1 any number or any formula. It recalculates whenever A1 does.

So why doesn't yours ? I can only guess; you have something hardcoded into
the function instead of passed to it? Please post it for suggestions.

HTH. Best wishes Harald

"Bura Tino" skrev i melding
...
Hi,

Suppose my function makes a reference to a range (named "MyRange"). So, in
a
way, the function depends on it. Is there a way to cause the function to
recalculate when the vlaue of "MyRange" changes? I don't want to make
volatile since too many cells depend on it and "MyRange" changes
infrequently.

Thanks!

Bura

"Harald Staff" wrote in message
...
Hi Bura

That is the default behavior. Try this:

Function Half(WholeValue As Double) As Double
Half = WholeValue / 2
End Function

In Cell B1:
=Half(A1)

and then in A1 any number or any formula. It recalculates whenever A1
does.

So why doesn't yours ? I can only guess; you have something hardcoded into
the function instead of passed to it? Please post it for suggestions.

Yes, it is hardcoded. That's what I was trying to convey by saying
"references". How about

Function ValueOfA1() as String
ValueOfA1 = Worksheets("MyWorksheet").Range("A1")
End Function

I use this in an add-in to getthe value of a cell that belong to the add-in.
Why this is necessary is another question. What I want to achieve is that if
the value A1 changes, all cells which depend on ValueOfA1 are recalculated.

HTH. Best wishes Harald

"Bura Tino" skrev i melding
...
Hi,

Suppose my function makes a reference to a range (named "MyRange"). So,
in
a
way, the function depends on it. Is there a way to cause the function to
recalculate when the vlaue of "MyRange" changes? I don't want to make
volatile since too many cells depend on it and "MyRange" changes
infrequently.

Thanks!

Bura

Hi Bura,

The only methods of getting a function into the dependency tree so that it
recalculates are to make it volatile or change a cell it refersto in its
argument list or reenter the formula that contains the function.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Bura Tino" wrote in message
...

"Harald Staff" wrote in message
...
Hi Bura

That is the default behavior. Try this:

Function Half(WholeValue As Double) As Double
Half = WholeValue / 2
End Function

In Cell B1:
=Half(A1)

and then in A1 any number or any formula. It recalculates whenever A1
does.

So why doesn't yours ? I can only guess; you have something hardcoded
into
the function instead of passed to it? Please post it for suggestions.

Yes, it is hardcoded. That's what I was trying to convey by saying
"references". How about

Function ValueOfA1() as String
ValueOfA1 = Worksheets("MyWorksheet").Range("A1")
End Function

I use this in an add-in to getthe value of a cell that belong to the
add-in.
Why this is necessary is another question. What I want to achieve is that
if
the value A1 changes, all cells which depend on ValueOfA1 are
recalculated.

HTH. Best wishes Harald

"Bura Tino" skrev i melding
...
Hi,

Suppose my function makes a reference to a range (named "MyRange").
So,
in
a
way, the function depends on it. Is there a way to cause the function
to
recalculate when the vlaue of "MyRange" changes? I don't want to make
volatile since too many cells depend on it and "MyRange" changes
infrequently.

Thanks!

Bura