function called from worksheet and bad result!

[@lan]

Hi,
in a worksheet "test" with autofilter mode, I'm using the formula:

=nb_row_aera(1)

which refers to my VBA function :

Function nb_row_area(n as Long) As Long
nb_row_area
Worksheets("test").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas(n).Rows.Count
End Function

the problem is that the result is always the nb of rows in the initia
range for Autofilter even if filters are activated in the worksheet!
On the contrary, the same code placed into a macro provides goo
results!!!

Sub Pr_nb_row_area()
n = 1
MsgBo
Worksheets("test").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas(n).Rows.Count
End Sub

Why and how should I modify the function

 

[Niek Otten]

Excel doesn't know when to recalculate your formula because no cells are
used as arguments.
You can add "Application.Volatile" as the first line in your function; then
it will always be recalculated.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[@lan]

Unfortunately no change with (even with F9):

Function nb_row_area(n As Long) As Long
Application.Volatile
nb_row_area
Worksheets("test").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas(n).Rows.Count
End Function

should I activate an option for use of Application.Volatile

 

[Tom Ogilvy]

Several methods don't work when used as a UDF. I believe specialcells is
one of those.

You may have to loop throught the range in your UDF or better, use the
already builtin Subtotal worksheet function.

=Subtotal(3,A1:A100)-1

Assumes that column A will contain an entry for each record.

 

[@lan]

in fact I would like to build a function to return the number o
different values in column A for example taking current filtering int
account (I think there's no appropriate function with subtotal).

My whole procedure using SpecialCells(xlCellTypeVisible) works well bu
not the same code in the function!

Do you have any other suggestions or tricks to get round the problem

 

[Tom Ogilvy]

Loop through the cells of the filtered area and check if the row is hidden
or not.

 

[@lan]

effectively method Hidden works in UDF unlike SpecialCells.

Tom and Niek, thank you