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:

R/C	C	D	E	F	G	H	J	K
5	Features	F1	F2	F3	F4	Ranges	UDF Results	Formulas
6	Weights	1	3	5	7	rngWeights = $D$6:$G$6	$D$6:$G$6	J6: =rangetest1(rngWeights)
7	Prod1	2	A	4.5	Y	rngRatings = $D7:$G7	$D$6:$G$6	J7: =rangetest1(rngWeights)
8	Prod2	4	B	6.2	Y	rngRatings = $D8:$G8	$D$8:$G$8	J8: =rangetest1(rngRatings)
9	Prod3	6	C	5.0	N	rngRatings = $D9:$G9	$D$9:$G$9	J9: =rangetest1(rngRatings)
10							$D$6:$G$6	J10: =rangetest2("rngWeights")
11							$D$6:$G$6	J11: =rangetest2("rngWeights")
12							$D$1:$G$1	J12: =rangetest2("rngRatings")
13							$D$1:$G$1	J13: =rangetest2("rngRatings")

<tbody>
</tbody>

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?