Application.Volatile

This MAY help.

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...