Can't Make CurrentRegion Work

[s.c.wilson]

In a block of cells (a prototape that the end user may copy and paste
anywhere and repeatedly) I have a cell that calls a VBA function. Using
the ActiveCell in the block, I want the function to find the range of
the entire block to which it belongs -- the CurrentRegion. Finding that
range works in a macro:

Dim r As Range
Range(ActiveCell.Address).CurrentRegion.Select
Set r = Selection
Debug.Print r.Address ...shows "$D$1:$L$7" as it should

But every variation of this that I try in the VBA function returns only
the single cell -- the ActiveCell rather than the CurrentRegion. For
example:

Dim r as Range
Set r = Worksheets("Sheet1").Range(ActiveCell.Address).CurrentRegion
Debug.Print r.Address ...results in just "$H$1"

Anyone know what I should be doing?

Steve

 

[Zack Barresse]

Hello,

How are you calling the function?? Shorten to..

Function CurrentRegionAddy(rngRet as variant) as string
Dim strAddy as string
If Typename(rngRet) = "Range" then
straddy = rngret.address
End if
if straddy = "" then straddy = rngret
Currentregionaddy = "ERROR!"
if activesheet is nothing then exit function
Currentregionaddy = range(straddy).currentregion.address
End Function

No real need to select anything. Note that I have not tested this function
myself (unable to at the moment).

HTH

Regards,
Zack Barresse, aka firefytr

 

[Zack Barresse]

Doing a little more digging, apparently the CurrentRegion won't work via
UDF. Sorry.

Regards,
Zack Barresse, aka firefytr