Stopping User Defined Functions for Recalculating
 

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.

Stopping User Defined Functions for Recalculating
 

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

--
Cheers
Nigel



"Jim" wrote in message
...
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.

Stopping User Defined Functions for Recalculating
 

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.

"Nigel" wrote:

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

--
Cheers
Nigel



"Jim" wrote in message
...
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.

Stopping User Defined Functions for Recalculating
 

On Wed, 1 Jun 2005 16:54:03 -0700, Jim
wrote in microsoft.public.excel.programming:

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.

On Fri, 3 Jun 2005 18:54:02 -0700, Jim
wrote in microsoft.public.excel.programming:

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.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Stopping User Defined Functions for Recalculating
 

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



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

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"