Immediate window gives different results to a UDF

[Dave Mac]

Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address


Any help or pointers are most graciously appreciated.

Regards,

David

 

[Dave Mac]

Hello all,

Ok, now ive pulled all my hair out and called Microsoft every name
under the sun, i thought it about time I consulted some pros.

This works perfectly well in the immediate window (with debug.print),
however when used as an excel sheet function I just get the #VALUE!
error.

debug.print Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row

Private Function firstrow() As Variant
firstrow = Sheets("MVP DB
Model").Range("A:A").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1).Row
End Function

Please help me by tell me it is something rediculasly simple that am
overlooking or is it a daft excel quirk?

Ive a similer problem with returning the address for the auto filter
range.
With the immediate window this gives me a text string that would be
able to split() however used in a function it gives me 1 range value.

Sheets("MVP DB Model").AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).Address

Any help or pointers are most graciously appreciated.

Regards,

David

*bounce*

 

[Dave Peterson]

..specialcells won't work within a UDF when called from a cell on a worksheet.

 

[Dave Mac]

.specialcells won't work within a UDF when called from a cell on a worksheet.

Thanks Dave. I'll try a different approach.