Disappearing Ranges

Topic

New Reply

The attached workbook has 2 ranges defined with scope Sheet3.

The range bbb refers to =Sheet3!$E$4
whilst the range aaa refers to =Sheet1!$E$4

There is a macro in there which attempts to print the values of each of these:
Debug.Print Worksheets(“Sheet3”).Range(“bbb”).Value
Debug.Print Worksheets(“Sheet3”).Range(“aaa”).Value

The former works fine, the latter gives a 1004 runtime error.

So how does one get the value of the aa range into a macro?

Reply | Quote

Replies

Paul,

I’m a little confused as to what you are trying to accomplish here.
Your workbook shows both range names as having a scope of Sheet3 which is causing the problem in my opinion.
Are you trying to use the same Range Name on multiple sheets?
I deleted “aaa” and redefined it and now things seem to work just fine.
The macro prints the appropriate results and cell E6 on Sheet3 shows the value from cell E4 on Sheet1.
See graphics and attached workbook. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You now have aaa defined at workbook level.

In my original workbook I had 2 ranges defined with a scope of Sheet3.

If I use a formula on Sheet3 of =aaa it correctly displayed the value of cell Sheet1!$E$4.

If I use a formula on Sheet2 of =bbb it will correctly display the value of cell Sheet3!$E$4

However in VBA, I can access the value referenced by Worksheets(“Sheet3”).Range(“bbb”).Value but not the value referenced by Worksheets(“Sheet3”).Range(“aaa”).Value

I am trying to accomplish getting the value of the referenced by Worksheets(“Sheet3”).Range(“aaa”).Value into the macro. You have redefined the range aaa so that it has WorkBook scope. I am trying to get the value when it is defined as having WorkSheets(“Sheet3”) scope.

Reply | Quote

Your second debug print statement says “Sheet3” as the sheet where range aaa is located. However, it is located on sheet1. Excel looks for a local range name “aaa” on sheet 3, can’t find it and gives an error. Try: Debug.Print Worksheets(“Sheet1”).Range(“aaa”).Value

Bob Flanagan

Reply | Quote

I did try that. Did you try it before posting?

The referred to range may be located on Sheet1, but the scope of the range name is Sheet3. I was very careful to put this information in the OP.

Worksheets(“Sheet1”).Range(“aaa”) will not find the range name on Sheet1 since the scope of the range name is Sheet3.

Reply | Quote

Paul, yes I did test.

Debug.Print Worksheets(“Sheet1”).Range(“aaa”)

will print the value of aaa found on sheet1. Since aaa is not located on sheet 3,

Debug.Print Worksheets(“Sheet3”).Range(“aaa”)

returns runtime error 1004, application-defined or object-defined error. Which means that excel/vba can not find a range named aaa on sheet3.

I suspect the issue is what you mean by: “the scope of the range name is sheet3”. I’m afraid I don’t understand that statement. Could you expand on what you mean by “scope”?

Bob

Reply | Quote

If you tried that in my workbook (which I attached to the OP) then I do not believe that you got an output.

You can learn about the sope of range names by reading the help here:
http://office.microsoft.com/client/helpcategory14.aspx?CategoryID=CH010369092&lcid=2057&NS=EXCEL&Version=14&tl=2

If you go into the name manager on my sheet you will see that the scope of aaa is Sheet3. If you go to sheet 1 and type
=aaa
into a cell you will get #NAME
This is why Debug.Print Worksheets(“Sheet1”).Range(“aaa”) can not work. Try going to the Name Box and click the drop down arrow on Sheet3. You will see aaa and bbb
Try that on Sheet1 and you will see an empty box.

I have no idea what you used to get the value of aaa on Sheet1 printed but you did not use Debug.Print Worksheets(“Sheet1”).Range(“aaa”) in my workbook.

If you did it on RetiredGeek’s Copy of disappearing range.xlsm then you will have got Sheet1-E4. On mine you will get an error.

I suspect that this may be an Excel bug.

Reply | Quote

Paul, I think I am getting closer to understanding your problem.

I did not download your workbook, as I tend to not do so to avoid potential problems with viruses. I took your description and duplicate it. However, your description did not mention that the ranges you created were LOCAL range names, only on sheet3. I created default global ranges, with aaa on sheet1, and bbb on sheet3. Thus my use of sheet1 when I tested did work.

You are getting the problem because you have created two local range names on sheet3. One of the range names, bbb, refers to a cell on that sheet, e4. The other local name, aaa, refers to a cell on sheet1. Excel can not interpret aaa to a range. I suspect there is a formula trick one can use; I don’t know this.

To further illustrate what I am saying, try this: Type “ccc” in a cell. Select it. Press CTL-F3. Excel will ask if you wish to create a range name by the name ccc. Click OK. Now press CTL-F3 again. You will see a list of all three range names on that worksheet. The local ones have “sheet3” by them. Since ccc is not local, it does not have a sheet name by it. If you go to any sheet, you will see ccc in the names box.

So, that means the task is how to find out what range a local name on a sheet refers to. The Names collection is fairly limited. One must search it for a match. And then once found, return the refersTo property. This will begin with an equal sign. One then converts it to a range. Something like this will work:

Sub ReturnValue()
Dim anyN As Name
Dim anyR As String
Dim cell As Range
For Each anyN In ActiveSheet.Names
If Right(anyN.Name, 4) = “!aaa” Then
anyR = anyN.RefersTo
anyR = Mid(anyR, 2)
Set cell = Range(anyR)
MsgBox cell.Value
Exit Sub
End If
Next
MsgBox “No match found.”
End Sub

You do have to be on the right sheet to use. The reason for testing with ! as part of the name is to insure that a match to a name like aaaa is not found. And using activesheet.names does just the names on the activesheet vs activeworkbook.names.

Reply | Quote

Paul,

You may want to read this article to help clarify the usage of the local scope for range names. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek,
that article talks about using them in sheet formulas and if you looked at my workbook you’d see it was already working there. In fact the help link I supplied earlier gives more information on range scope.

Reply | Quote

Bob:
You said “However, your description did not mention that the ranges you created were LOCAL range names, only on sheet3.”
The very first line of the original post said “The attached workbook has 2 ranges defined with scope Sheet3.”

You said “Excel can not interpret aaa to a range”
I say that it can and does. If you look in my sheet you will see that I have =aaa in a cell on sheet3 and Excel is interpreting it and showing the value of cell Sheet1!E4

Your comment “You do have to be on the right sheet to use” is not true since in VBA you can (and normally should) qualify your locations using the correct level objects, possibly within a With block.

Not being funny but your explanation about how to use the name manager was a bit of a waste of time since I had already referred you to that myself.

In fact I have worked out that the answer to my question is to use:
Worksheets(“Sheet3”).Names(“aaa”).RefersToRange.Value

Reply | Quote

You need to access the sheet’s Name object:

Code:

   Debug.Print Worksheets("Sheet3").Range("bbb").Value
   Debug.Print Worksheets("Sheet3").Names("aaa").RefersToRange.Value

Reply | Quote

Which I posted above 2 days ago once I worked it out.

You need to access the sheet’s Name object:

Code:

   Debug.Print Worksheets("Sheet3").Range("bbb").Value
   Debug.Print Worksheets("Sheet3").Names("aaa").RefersToRange.Value

Reply | Quote

Guess I should read more closely. Won’t bother you again.

Reply | Quote