Passing range as range vs name of range

[JenniferMurphy]

I've run into a little speed bump in some code that is trying to access a named range of data using the name of that range, rather than the range itself. I know some will argue against doing that, but let's leave that aside for now.

To illustrate the problem, I wrote the two little test UDFs below. They both return the address of the target range. The first one, RangeTest1, gets the range passed as a range parameter. It works perfectly.

The second, RangeTest2, gets the name of the range passed as a string parameter. It also works, but with one little glitch. If the range is absolute ($C$R), it returns that address. But if either part of the range address is relative (C$R, $CR, or CR), it returns the address as if it were called from A1.

Here's the code:

'Return address of range, passed as range argument
Public Function RangeTest1(pRange As Range) As String
RangeTest1 = pRange.Address
End Function

'Return address of range, name passed as string argument
Public Function RangeTest2(pRangeName As String) As String
Dim rng As Range
On Error Resume Next
Set rng = Range(pRangeName)
On Error GoTo 0
If rng Is Nothing Then
  RangeTest2 = "n/a"
End If
RangeTest2 = rng.Address
End Function

And here's a sample of it in action:
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Features[/TD]
[TD]F1[/TD]
[TD]F2[/TD]
[TD]F3[/TD]
[TD]F4[/TD]
[TD="align: center"]Ranges[/TD]
[TD="align: center"]UDF Results[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Weights[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]rngWeights = $D$6:$G$6[/TD]
[TD]$D$6:$G$6[/TD]
[TD]J6: =rangetest1(rngWeights)[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Prod1[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]4.5[/TD]
[TD]Y[/TD]
[TD]rngRatings = $D7:$G7[/TD]
[TD]$D$6:$G$6[/TD]
[TD]J7: =rangetest1(rngWeights)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Prod2[/TD]
[TD]4[/TD]
[TD]B[/TD]
[TD]6.2[/TD]
[TD]Y[/TD]
[TD]rngRatings = $D8:$G8[/TD]
[TD]$D$8:$G$8[/TD]
[TD]J8: =rangetest1(rngRatings)[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Prod3[/TD]
[TD]6[/TD]
[TD]C[/TD]
[TD]5.0[/TD]
[TD]N[/TD]
[TD]rngRatings = $D9:$G9[/TD]
[TD]$D$9:$G$9[/TD]
[TD]J9: =rangetest1(rngRatings)[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$D$6:$G$6[/TD]
[TD]J10: =rangetest2("rngWeights")[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$D$6:$G$6[/TD]
[TD]J11: =rangetest2("rngWeights")[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$D$1:$G$1[/TD]
[TD]J12: =rangetest2("rngRatings")[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$D$1:$G$1[/TD]
[TD]J13: =rangetest2("rngRatings")[/TD]
[/TR]
</tbody>[/TABLE]

The range names and definitions are shown in Col H. rngWeights is absolute ($D$6:$E$6). rngRatings is half relative ($Dn:$En) so the row numbers adjust to match the row number of the calling cell.

The results of the UDF calls are in Col J and the expressions for those calls are in Col K.

In J6 & J7, RangeTest1 returns the correct address for rngWeights. Similarly, in J10 & J11, RangeTest2 also returns the correct address for rngWeights.

In J8 & J9, RangeTest1 returns the correct address for rngRatings adjusting the row numbers to match the row numbers of the calling cells. In J12 & J13, however, RangeTest2 returns the address for rngRatings as if it had been called from A1.

Is there a way that I can get RangeTest2 to return the correct relative address?

Now I know I can get the address of the calling cell and replace the row number, but I'd like a command that does that itself.

Thanks

 

[Tetra201]

Try

RangeTest2 = Names(pRangeName).RefersToRange.Address

 

[JenniferMurphy]

Try

RangeTest2 = Names(pRangeName).RefersToRange.Address

Spectacular! It works perfectly. I would never have figured that out on my own. I tried a bunch of things, but not that.

 

[JenniferMurphy]

(sigh) Your solution works for getting the address associated with the name, but what I really need is the range. I know I said address in my original post, but I knew what I meant.

Your code returns the correct address as a string (e.g., "$E10:$S10").

Here's what I tried to convert that to a range so I can loop through each cell:

Public Function AddEm(RngName)

Dim Addr As String
Dim Rng As Range

On Error Resume Next
Addr = Names(RngName).RefersToRange.Address
Set Rng = Range(Addr)
On Error GoTo 0
AddEm = Rng(1, 1) + Rng(1, 2) + Rng(1, 3) + Rng(1, 4)

End Function

It seems to work, but is there a better way?

I suppose I could combine the Names and Range calls like this:

Public Function AddEm(RngName)

Dim Rng As Range

On Error Resume Next
Set Rng = Range(Names(RngName).RefersToRange.Address)
On Error GoTo 0
AddEm = Rng(1, 1) + Rng(1, 2) + Rng(1, 3) + Rng(1, 4)

End Function

Comments?

 

[Tetra201]

Try

AddEm = Application.Sum(Names(RngName).RefersToRange)

 

[JenniferMurphy]

Try

AddEm = Application.Sum(Names(RngName).RefersToRange)

Thanks, but summing was just an example. What the code actually does is a lot more complicated.

I just wanted to know if my code to obtain the range was correct. From your answer, I concluded that it can be simplified to this, which seems to work:

Public Function AddEm(RngName)

Dim Rng As Range

On Error Resume Next
Set Rng = Names(RngName).RefersToRange
On Error GoTo 0
AddEm = Rng(1, 1) + Rng(1, 2) + Rng(1, 3) + Rng(1, 4)
  . . .

Did I get it right?