excel - Incorrect result from CurrentRegion when used in a function called from a cell

Question

I'm trying to write a VBA function to be called from a cell. In this function I need to use the CurrentRegion property but it returns an unexpected result.

The function:

Function GetVarRange() As String

    Dim rngRangeToLeft As Range, wks As Worksheet
    Set wks = Application.Caller.Worksheet
    Set rngRangeToLeft = wks.Range("A1").CurrentRegion

    GetVarRange = rngRangeToLeft.Address

End Function

The result I get in the cell is $A$1.

There are values in A1, and all around it, the CurrentRegion is actually A1:AD618 and this is the result I get when I access the property within the code editor (not in this function) and when I press ctrl+A when A1 is selected.

Any ideas anyone?

Answer 1

CurrentRegion does not work correctly in UDF()'s called from worksheet cells. I have also found problems with FindNext and SpecialCells.

Called from a Sub, it is a different story:

Function GetVarRange(rng) As String
    Dim rngRangeToLeft As Range, wks As Worksheet
    Set rngRangeToLeft = rng.CurrentRegion
    GetVarRange = rngRangeToLeft.Address
End Function

Sub MAIN()
MsgBox GetVarRange(Range("A1"))
End Sub

appears to work just fine.