custom function doesn't update value when source value changes

[Mark Kubicki]

i seem to remember that there needs to be some line of code addied for this
to occur

thanks in advance,
mark

 

[Gary''s Student]

Application.Volatile

This MAY help.

 

[Jim Thomlinson]

There are 2 ways to make a user defined function update. The one you are
thinking of is adding Application.Volatile to the function. This means that
the function will recalculate every time a calculation runs anywhere. There
is a lot of overhead added by doing this.

The other method is to add the appropriate range arguments to your funtion
something like this

Public Function MySum (byval SumRange as range) as double
MySum = Application.sum(sumrange)
end Function

Now if you use it in a spreadsheet something like this...
=MySum(A1:A10) any changes to the range A1:A10 will trigger this function to
recalculate. This is a much better method than making the funciton volatile...