CODEWITHSUNDEEP
vbarange
Riccardo La Marca
Riccardo La Marca

Reputation: 895

How to extended the range variable with CurrentRegion property (vba)?

The range variable extends with the following code:

Option Explicit
Function ESTENDISELEZIONE(Area As Range) As Range
    Set ESTENDISELEZIONE = Range(Area, Area.End(xlDown))
End Function

But it doesn't extends with:

Option Explicit
Function ESTENDISELEZIONE(Area As Range) As Range
    Set ESTENDISELEZIONE = Range(Area, Area.CurrentRegion)
End Function

And with:

Option Explicit
Function ESTENDISELEZIONE(Area As Range) As Range
    Set ESTENDISELEZIONE = Area.CurrentRegion
End Function

The procedure extends the range!

Sub EXTENDSELECTION()
    Selection.CurrentRegion.Select
End Sub

Can someone help me?

I've added an example to the question.

In the cell: 

=CONTA.SE(ESTENDISELEZIONE(B2);"Orange")

In the formula:

Option Explicit
Function ESTENDISELEZIONE(Area As Range) As 
Range
    MsgBox(Area.Address)
    Set ESTENDISELEZIONE = Area.CurrentRegion
    MsgBox(ESTENDISELEZIONE.Address)
End Function

Upvotes: 1

Views: 389

Answers (1)

YowE3K
YowE3K

Reputation: 23984

The CurrentRegion property of a Range object won't work in a UDF.

And it doesn't make much sense to try, as it will cause untold problems. Imagine that you changed cell C6 in your spreadsheet - the cell containing =CONTA.SE(ESTENDISELEZIONE(B2);"Orange") will not be recalculated (because it isn't dependent on cell C6) which means you start getting inconsistent data in your worksheet.

Note: Your code works fine if just being used as a normal Function. It is only when used as a UDF that it will have problems.

Upvotes: 2

Related Questions