PDA

View Full Version : Duplicate names for same range



shankar
04-21-2009, 10:06 AM
I am still trying to get a hang of named ranges in VBA.

I was curious to find out what happens when the same range is assigned to two different names, and which one would the name property of the range refer to in that case. So I wrote this small piece of code-

Names("name1").Delete
Names("name2").Delete
Set range1 = .Range(Cells(1, 1), Cells(2, 2))
Set range2 = .Range(Cells(1, 1), Cells(2, 2))
Names.Add Name:="name1", RefersTo:="=" & range1.Address
Names.Add Name:="name2", RefersTo:="=" & range2.Address
MsgBox range2.Name.Name
I initially assumed that "name2" would get displayed in the message box, but oddly enough, it displayed "name1". However it displays "name2" if I change range1 to refer to a different set of cells from range2.

I see three possible scenarios.
1. Two range variables referring to the same cells
2. Two names referring to the same range variable
3. Two names referring to two range variables as in 1.

I didn't come across any documentation as to how VBA sets the name property for the range variables in each case. As in the above example, it seems to depend on if there is another named range pointing to the same group of cells.

I do realize that this is more out of curiosity. In real programming I guess one should not create such multiple references, either at the range level, or at the name level.

Kenneth Hobs
04-21-2009, 10:28 AM
It will pick the one first sorted alphabetically.