CurrentRegion - Usual Behavior?

[Matthew Herbert]

All,

I'm receiving some unexpected results with .CurrentRegion, but my
observations lend me to believe that using .CurrentRegion within a UDF that
is called from a worksheet (as opposed to being called internally within VBA)
does not work. (I thought that I might have read somewhere that this is the
case, but my memory is fuzzy on this topic). I'm trying to confirm if this
is the case, and if so, does this mean that one has to write a custom
function that does what .CurrentRegion does? I've provided an illustrative
example, along with my code, below.

Enter the following into the first worksheet (i.e. the left-most worksheet):
A1: Name; B1: Date; C1: Value
A2: Matt; B2: 3/24/2010; C2: 1
A3: Matt; B3: 3/24/2010; C3: 3
A4: <blank>; B4: 3/24/2010; C4: <blank>

If you run the "TestCurrentRegion" code below (on the spreadsheet data
listed above), then you should get the following results printed to the
Immediate Window (VBE: View | Immediate Window -or- Ctrl+g):
Rng.Adrs:$A$1
Cnt Rows: 4
Cnt Cols: 3
Rng.CrRg:$A$1:$C$4

However, if you enter the following formulas into the worksheet itself, you
get (or at least I got) a 1 for both results:
E1: =GetRegionCount(A1,1) --> Result = 1; Expected = 4
E2: =GetRegionCount(A1,2) --> Result = 1; Expected = 3

Again, does .CurrentRegion work only when called internally within VBA and
not when called from the worksheet?

Thanks,

Matthew Herbert

Sub TestCurrentRegion()
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets(1).Range("A1")
Debug.Print "Rng.Adrs:"; Rng.Address
Debug.Print "Cnt Rows:"; GetRegionCount(Rng, xlRows)
Debug.Print "Cnt Cols:"; GetRegionCount(Rng, xlColumns)
Debug.Print "Rng.CrRg:"; Rng.CurrentRegion.Address
Set Rng = Nothing
End Sub

Function GetRegionCount(rngReference As Range, uRowCol As XlRowCol) As Long
Dim Rng As Range
Set Rng = rngReference(1).CurrentRegion
With Rng
Select Case uRowCol
Case xlRows
GetRegionCount = .Rows.Count
Case xlColumns
GetRegionCount = .Columns.Count
End Select
End With
Set Rng = Nothing
End Function

 

[Jim Cone]

You can return a value (no formatting) to the cell containing the UDF call, but that's it.
You can't change/affect other cells. There may be some esoteric exceptions, but don't waste your
time.
--
Jim Cone
Portland, Oregon USA



"Matthew Herbert" <[email protected]>
wrote in message All,

I'm receiving some unexpected results with .CurrentRegion, but my
observations lend me to believe that using .CurrentRegion within a UDF that
is called from a worksheet (as opposed to being called internally within VBA)
does not work. (I thought that I might have read somewhere that this is the
case, but my memory is fuzzy on this topic). I'm trying to confirm if this
is the case, and if so, does this mean that one has to write a custom
function that does what .CurrentRegion does? I've provided an illustrative
example, along with my code, below.

Enter the following into the first worksheet (i.e. the left-most worksheet):
A1: Name; B1: Date; C1: Value
A2: Matt; B2: 3/24/2010; C2: 1
A3: Matt; B3: 3/24/2010; C3: 3
A4: <blank>; B4: 3/24/2010; C4: <blank>

If you run the "TestCurrentRegion" code below (on the spreadsheet data
listed above), then you should get the following results printed to the
Immediate Window (VBE: View | Immediate Window -or- Ctrl+g):
Rng.Adrs:$A$1
Cnt Rows: 4
Cnt Cols: 3
Rng.CrRg:$A$1:$C$4

However, if you enter the following formulas into the worksheet itself, you
get (or at least I got) a 1 for both results:
E1: =GetRegionCount(A1,1) --> Result = 1; Expected = 4
E2: =GetRegionCount(A1,2) --> Result = 1; Expected = 3

Again, does .CurrentRegion work only when called internally within VBA and
not when called from the worksheet?

Thanks,

Matthew Herbert

Sub TestCurrentRegion()
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets(1).Range("A1")
Debug.Print "Rng.Adrs:"; Rng.Address
Debug.Print "Cnt Rows:"; GetRegionCount(Rng, xlRows)
Debug.Print "Cnt Cols:"; GetRegionCount(Rng, xlColumns)
Debug.Print "Rng.CrRg:"; Rng.CurrentRegion.Address
Set Rng = Nothing
End Sub

Function GetRegionCount(rngReference As Range, uRowCol As XlRowCol) As Long
Dim Rng As Range
Set Rng = rngReference(1).CurrentRegion
With Rng
Select Case uRowCol
Case xlRows
GetRegionCount = .Rows.Count
Case xlColumns
GetRegionCount = .Columns.Count
End Select
End With
Set Rng = Nothing
End Function

 

[Jacob Skaria]

Some properties and methods cannot be used within a UDF like

..CurrentRegion
..CurrentArray
..Find
..SpecialCells