CurrentRegion works different in the Editor than in the workbook

[Guest]

Hi all,

I have tried to use CurrentRegion to define a range, and it doesn't work in
the workbook, but works in the editor. I have used it on a function, and the
function doesn't get the right region when called from the worksheet, but it
does from the Inmediate window.
This is the function:

Function Test()
Dim rngData As Excel.Range
Set rngData =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Test = rngData.Rows.Count
End Function

I have a region of 2 columns and 12 rows in A1 of sheet1, if I try the
function in the inmediate window with ?Test(), it returns 12; If I use it on
the workbook with =Test(), it returs 1.
The version of Excel is 2003 SP1. Is there anything that I am doing wrong?
I would like to know why it doesn't work.

Miguel.

 

[Norman Jones]

Hi Miguel,

Unfortunately, the CurrentRegion property cannot be used successfully in a
UDF.

In a similar way, the SpecialCells method does not work correctly in a
worksheet function.

 

[Guest]

Thanks, I didn't knew that and it was weird finding it for the first time.

Miguel.