Custom Function Won't Auto-Calculate

[MLT]

I'm having trouble with a custom function that won't automatically
recalculate like a built-in function would. So if I make a change to
the sheet that would affect the value of this custom function, it will
only recalculate if I go into the formula bar, then press enter. F9
doesn't do the trick either. Am I missing something?

 

[Charles Williams]

Make sure that ALL the cells/ranges used in the Function appear in the
argument list for the function.
Excel looks at the argument list to determine when to recalculate the
function.

A poor alternative is to add Application.Volatile to the function, but
this will make the function calculate at every recalculation even when
it does not need to.

Charles Williams
Excel MVP
The Excel Calculation Site
http://www.DecisionModels.com

 

[MLT]

Forgive my ignorance. This argument calls ranges that are in multiple
other tabs. How do I write that range into the function?

 

[Dave Peterson]

=myfunc(sheet1!a1:a9,'sheet 99'!x99,'even a third'!z32)

 

[MLT]

From Charles' post above, "Make sure that ALL the cells/ranges used in
the Function appear in the argument list for the function"

What does the syntax for that look like in the script for the function?

 

[Dave Peterson]

If the formula looks something like:

=myfunc(sheet1!a1:a9,'sheet 99'!x99,'even a third'!z32)

Then the VBA code could look like:

Function myFunc(rng1 as range, rng2 as range, rng3 as range) as Double
(or As Variant or As Long or As String or ...)

But this is all a guess. You've never shared what your function looks like and
what it does.