Replies

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

I think I was aware of that. Indeed immediately following that post I posted:

“I’ll try that.I already use another_range_object.value = range_object.value which does the equivalent of Paste Special Values on a whole range. It’s just a pity that there is no such shorthsnd for column widths.”

Bob’s one doesn’t work on a set of columns, only on a single column and so doesn’t work.

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.

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

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.

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.

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.

No because it pastes from the clipboard which violates the “without going via the clipboard” in the OP.

I’ll try that.I already use another_range_object.value = range_object.value which does the equivalent of Paste Special Values on a whole range. It’s just a pity that there is no such shorthsnd for column widths.

Well, one can do a copy via an object (i.e. not via the clipboard) using:

range_object.Copy Destination:=another_range_object

As well as copying the data, I also wish to copy the column widths to the destination range.

Wow Hans. Not only does it work, it is better than my original formula (which did appear to work OK when there was only one form present).

Thank you, Thank you, Thank you, Thank you.

In all of the subrate ones. It is showing FALSE on all of them

Doesn’t seem to in this extended version.

I use it as a base topping for pizza