Named ranges - how to refer?

[Jaymond Flurrie]

I have code like this

Sub testname()
    Dim newname As Name
    Set newname = Names.Add("Sheet1!Test2", "$Q$1")
    newname.Comment = "I'm just testing"
    Debug.Print Range("Sheet1!Q1").Name.Name
End Sub

which fails (Error 1004, application-defined or object-defined error) on that debug.print row. I have tried it also without the "Sheet1!" prefix, same result. I want the name to be a worksheet level name. I need the comment there. How do I make it work?
So either a different way to assign the given name with a given comment to a given range or just different way to refer are both acceptable answers.

Oh and my Excel is version 16.0.

 

[Bosquedeguate]

Try this ... (this was a test with one of my Sheets)

Sub Macro1()
 Names.Add "WorkersList", "$A$3:$A$6"
 Debug.Print Names(Names.Count).Name, Names(Names.Count)
End Sub

For your example try:

Sub Macro1()
 Names.Add ""Sheet1!Test2", "$Q$1"
 Debug.Print Names(Names.Count).Name, Names(Names.Count)
End Sub

 

[Jaymond Flurrie]

Try this ... (this was a test with one of my Sheets)

Sub Macro1()
 Names.Add "WorkersList", "$A$3:$A$6"
 Debug.Print Names(Names.Count).Name, Names(Names.Count)
End Sub

For your example try:

Sub Macro1()
 Names.Add ""Sheet1!Test2", "$Q$1"
 Debug.Print Names(Names.Count).Name, Names(Names.Count)
End Sub

So assuming I have (I will have) more than one name, do I need to loop thru all of them to find the correct one from the names collection? Also, this doesn't add the comment to the name at all.

But yes, it gives me this as answer: Sheet1!Test2 ="Sheet1!$Q$1"
and the comment can be added like I added. So otherwise good, but I'd need to find a way to do this without looping thru all of the names each time I want to refer to one of them.

 

[Bosquedeguate]

You can get the stored range reference for a named range using
This was in the Immediate window while debugging code
?Names("Test2")
="$Q$1"
?Names("Sheet1!Test2")
="$Q$1"
?Names("Test2").Comment
I'm just testing

You can use either a numerical index or the list name as your index into your Named Ranges (i.e. Names collection)

Does this help?

 

[Jaymond Flurrie]

You can get the stored range reference for a named range using
This was in the Immediate window while debugging code
?Names("Test2")
="$Q$1"
?Names("Sheet1!Test2")
="$Q$1"
?Names("Test2").Comment
I'm just testing

You can use either a numerical index or the list name as your index into your Named Ranges (i.e. Names collection)

Does this help?

All those three works, but the first two are backwards. That is, it gives me the range of the name, while what I need is to get the name of the range. That is, to pass in somehow the Q1 and receive "Test2" as the result.

Adding the comment works.

 

[Bosquedeguate]

Right there are only 2 indexes you can use in the Names collection

1. an numerical index, or
2. the Name to assigned to the range

The problem you could have is more than one named range with the identical address range (e.g. $Q$1).
Which one would you want returned to you.

Just curious ... why would you be trying to determine the Named Range name, by using an address?

If you used absolute address(es) in your Named Range you would have to make sure you use the absolute range address in your reverse lookup.

 

[Jaymond Flurrie]

Right there are only 2 indexes you can use in the Names collection

1. an numerical index, or
2. the Name to assigned to the range

The problem you could have is more than one named range with the identical address range (e.g. $Q$1).
Which one would you want returned to you.

Just curious ... why would you be trying to determine the Named Range name, by using an address?

If you used absolute address(es) in your Named Range you would have to make sure you use the absolute range address in your reverse lookup.

For some reason doing this directly from Excel interface works:

Names Manager shows this:

This code works:

Sub TestNameFromExcelTest()
    Debug.Print Range("Q1").Name.Name 'Prints TestNameFromExcel
End Sub

And this doesn't:

Sub testname()
    Dim newname As Name
    Set newname = Names.Add("Test3", "Sheet1$Q$2")
    newname.Comment = "I'm just testing"
    Debug.Print Range("Q2").Name.Name
End Sub

I don't necessarily need that worksheet level name, if that's the problem here (I'll just append the sheet name to the beginning of the name).

The Name Manager has two interesting displays:
1) Those quotation marks around the RefersTo
2) That Value is not empty (the cell doesn't have that Sheet1$Q$2 value)

 

[Jaymond Flurrie]

This is the closest I've been able to get, I think this will have to be good enough:

Sub test4()
    Sheet1.Range("Q3").Name = "Sheet1!Moretesting"
    Sheet1.Range("Q3").Name.Comment = "I'm commenting here"
    Debug.Print Range("Q3").Name.Name
End Sub