Stopping User Defined Functions for Recalculating

[Guest]

I have a user defined function that performs a calculation on a range of
cells. If a row or column outside of the range used by the function is
deleted the function automatically recalculates. How do I stop the function
from recalculating? I have tried using the Application.Volatile (False)
statement but the function still will recalculate whenever a row or column is
deleted. I only want the function to recalculate when the one of the cells
it is referencing changes.

 

[Nigel]

Maybe you can adapt this worksheet change event (placed in the relevant
worksheet code). In this case the function would only be called if cell A1
(row1 / column 1) where to change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
Call yourfunction
End If
End Sub

 

[Guest]

Thanks but this will not work for me. The UDF is part of an add-in. Also I
need the function to update any cells the function refers to changes.

 

[Michael Bednarek]

I have a user defined function that performs a calculation on a range of
cells. If a row or column outside of the range used by the function is
deleted the function automatically recalculates. How do I stop the function
from recalculating? I have tried using the Application.Volatile (False)
statement but the function still will recalculate whenever a row or column is
deleted. I only want the function to recalculate when the one of the cells
it is referencing changes.

Set Tools/Options.../Calculation to Manual; then calculate with F9 on
demand.

Thanks but this will not work for me. The UDF is part of an add-in.

Eh? So where did you put the .Volatile statement? It has to be within
the UDF in question, not just anywhere.

 

[Guest]

I did placed the application.volatile statement within the UDF. I have it as
the first statement in the UDF.