.RefersTo with local range (VBA)

[Gates Is Antichrist]

Puzzler...I have a range MyRange on Sheet2. I copied Sheet2 (alt-e-m) as Sheet1, so there's a local Myrange there now.

If I look at the refers-to with control-F3 while on sheet1, it refers to Sheet1, and you can see that it's local.

If I look at the refers-to with control-F3 while on sheet2, it refers to Sheet2, and you can see that it's NOT local. So far all cool.

Now in the VBA, ?Names(myRange).RefersTo
refers to sheet1 - EVEN WHEN Sheet2 is active.

Why in the heck would it do that?

 

[Norie]

Wouldn't any unqualified references to ranges etc in code refer to the active worksheet?

You really should try and explicitly qualify things in code, or you're just going to have trouble.

 

[Gates Is Antichrist]

Wouldn't any unqualified references to ranges etc in code refer to the active worksheet?

Obviously not.

I would be interested to hear from someone who really understands why it chooses what it does. .refersTo is seemingly not only going out of its way to avoid the active sheet, it's picking a local range instead of the 'global' one.

 

[Scott Huish]

The code as you posted it
?Names(myRange).RefersTo
even when substituting the named range in for myRange returns an error of
Method not valid without suitable object.

Did you create the names manually or via code?

In my test I created a named range called named and ran both of the following and it returned the correct sheet and range:

Sub test()
Dim nm
For Each nm In ActiveWorkbook.Names
    MsgBox nm.RefersTo
Next
End Sub

Sub TEST2()
MsgBox Names("named").RefersTo
End Sub

Can you post the code you actually used?

 

[Gates Is Antichrist]

Sorry, I did omit quotation marks. Make that
?Names("myRange").RefersTo
However I stand by the post. I described exactly what I did, so anyone can duplicate it. FWIW, I'm XL03.

Where I said "in the VBA" try "in the VBE." Details...

 

[Gates Is Antichrist]

FWIW, there need be no code at all; just type the "?" part in the Immediate Window. But you can just as well make that the sole statement in a sub from a button on a form.

 

[Legacy 98055]

Norie said..

Wouldn't any unqualified references to ranges etc in code refer to the active worksheet?

To ranges, yes. To names, not neccesarily. Read on.

You really should try and explicitly qualify things in code, or you're just going to have trouble

. This is Gates problem. Read on.

Gates,

Norie was on the right track. You are not disclosing exactly which "MyRange" you are refering to because you are not qualifying the correct collection. You are not indicating which member of the names collection you wish to return. It does not matter if the range refered to is local to a specific worksheet or not.

Application.Names
WorkBook.Names
WorkSheet.Names

Your example: ?Names(myRange).RefersTo

will default to workbook or worksheet level qualification. It depends on where your code is.

Sub Example()
Debug.Print TypeName(Names.Parent)
End Sub

In a worksheet, the parent object is a worksheet.
In the workbook class or a standard module, the parent object is a workbook.

This workbook names collection contains two members with the same key. "myRange".

Sheet1 and Sheet2 belong to the workbook and also the names contained therein. Because you have multiple names with the workbook names collection, your statement, as is, will simply return the first "myRange" it comes across. That is, the "myRange" with the lowest index.

=Sheet1!$A$1 Index:1
=Sheet2!$A$1 Index:2

I understand the confusion. This is not standard behavior for a collection but MS had to allow it or you would not be able to simply copy or move worksheets containing names without changing the keys for each.

Note that internally, excel does not really see two "myRange" keys but...

Sheet1!myRange
myRange

HTH

 

[Gates Is Antichrist]

That makes very good sense. Good job. And thank you for taking the trouble to provide the expansive detail - it's all useful.

 

[Gates Is Antichrist]

Rant alert. I can't tell just what is going on here, and I think we'd all like to know. I thank the previous poster for good stuff, but it's still pretty vague as to what's going on with the "first [one] it comes across" business.

As to Names("myRange") resolution: in the Watch window (while stepping code for cboRanges_Change, change event on a combo box):
names/Item 1/Name is " : Name : "Sheet1!MyRange" : String : pmFormListbox.cboRanges_Change"
names/Item 2/Name is " : Name : "MyRange" : String : pmFormListbox.cboRanges_Change"

Am I wrong, or is it a violation of basic computer programming logic that the Name property was not matched against? As far as "the first one it finds" goes, it would appear that index number 1 beat out number 2; but the name resolution ignored the fact that it had an exact match on item 2 - and who knows what on item 1. I can't find a property on item 1 that is the string "MyRange" . Perhaps there's a hidden property called "Nickname range" or some such cuteness in the resolution code....grrr. I'm inclined to believe this is Y.A. example of the M.S. dogma, "We know what the user wants better than the user does - and the user will thank us for thereby making his life more convenient. Microsoft: making the world a better place." Thank you, Microsoft. I love you.

I'll be fair to admit that it sometimes works to my favor. Notice how I was inconsistent on upper-casing MyRange above. In fact, I heavily rely on the alphabetic case forgiveness in VBA. Heavily. But what the heck kind of "forgiveness" is in play here?

It's one thing to used "relaxed resolution" when there is no resolution otherwise. That fits the alphabetic case. But using "relaxed resolution" on the name, when there's an exact match using the property called ... uh, what was it called again? Oh yeah: NAME!

Rant mode off. I just wish I knew WTH they're doing here.