Custom Worksheet Function not updating

[Hawki]

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

 

[Jake Marx]

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]

 

[Don Guillett]

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

 

[Frank Kabel]

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.