Custom Worksheet Function not updating
 

I have created a function that returns a range. The
function is called getRange(intColumn as Integer). I use
it in in a worksheet function as follows in cell A1:

=count(getRange(2))

It works when I type in the function, but when I make
changes on the worksheet, cell A1 is NOT updated
accordingly. It appears as if the cell is NOT re-
evaluating the getRange(2) when the worksheet is updated.
Any help would be appreciated. TIA...

Custom Worksheet Function not updating
 

Hi Hawki,

A UDF typically gets reevaluated only when its input parameters (in this
case, A1) or one of their precedents change. If you want to force a recalc
on every worksheet change, you can put Application.Volatile as the first
statement in your UDF.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Hawki wrote:
I have created a function that returns a range. The
function is called getRange(intColumn as Integer). I use
it in in a worksheet function as follows in cell A1:

=count(getRange(2))

It works when I type in the function, but when I make
changes on the worksheet, cell A1 is NOT updated
accordingly. It appears as if the cell is NOT re-
evaluating the getRange(2) when the worksheet is updated.
Any help would be appreciated. TIA...

Custom Worksheet Function not updating
 

You didn't post your code but it may just need
application.volatile
as the first line

--
Don Guillett
SalesAid Software

"Hawki" wrote in message
...
I have created a function that returns a range. The
function is called getRange(intColumn as Integer). I use
it in in a worksheet function as follows in cell A1:

=count(getRange(2))

It works when I type in the function, but when I make
changes on the worksheet, cell A1 is NOT updated
accordingly. It appears as if the cell is NOT re-
evaluating the getRange(2) when the worksheet is updated.
Any help would be appreciated. TIA...

Custom Worksheet Function not updating
 

Hi
one way:
use the column as range parameter in your function

or add the line
application.volatile at the beginning of your function

Note: As you don't use a cell/range reference in your function as
parameter Excel does not know if it should re-calculate the function if
something in column 2 changes.


--
Regards
Frank Kabel
Frankfurt, Germany


Hawki wrote:
I have created a function that returns a range. The
function is called getRange(intColumn as Integer). I use
it in in a worksheet function as follows in cell A1:

=count(getRange(2))

It works when I type in the function, but when I make
changes on the worksheet, cell A1 is NOT updated
accordingly. It appears as if the cell is NOT re-
evaluating the getRange(2) when the worksheet is updated.
Any help would be appreciated. TIA...