Making a function somewhat volatile.

[Bura Tino]

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]

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]

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.

 

[Charles Williams]

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