How to access a named range in Excel-VBA with multiple areas?

Question

All works fine with a one-area range: Select cells A1 and B1 and give it the name "foo".

?ThisWorkbook.Names.Item("foo").RefersTo
 =Tabelle1!$A$1:$B$1
?ThisWorkbook.Names.Item("foo").RefersToRange.Address
 $A$1:$B$1

However, two-area ranges will fail (Excel 2010): Click cells A2 and B2 with ctrl pressed and give it the name "bar".

 ?ThisWorkbook.Names.Item("bar").RefersTo
  =Tabelle1!$A$2,Tabelle1!$B$2
 ?ThisWorkbook.Names.Item("bar").RefersToRange.Address
  -> Run-time error '1004'

Why can't we get the range the "bar"-name refers to?

Is there a workaround not using sheet.Range("bar") as I don't know which sheet holds the name?

Note .RefersToRange.Address appears to work as desired when I tested in Excel 2003. — A. Webb
– A. Webb, Commented Jan 11, 2013 at 15:01
@A.Webb I've tested on 2003 as well and it fails. Are you sure you created a multiarea range? — GSerg
– GSerg, Commented Jan 11, 2013 at 15:14
@GSerg Yes, I get, for example ?ThisWorkbook.Names.Item("bar").RefersToRange.Address $A$1,$C$1 — A. Webb
– A. Webb, Commented Jan 11, 2013 at 21:00
@A.Webb Ah. It would appear this works indeed if the Excel language is English. Doesn't work on [at least some] localized versions. Apparently this internally retrieves range address in local form and then treats it in en-us way (or the other way round). Another PITA Excel bug for my collection. — GSerg
– GSerg, Commented Jan 11, 2013 at 21:08

GSerg · Accepted Answer · 2013-01-11 15:07:48Z

1

You could try the global Range, not a sheet.Range:

? Range(ThisWorkbook.Names("bar").RefersTo).Address

~~In order to avoid possible cross-workbook gotchas you can use ConvertFormula to add workbook name to the range:~~

? ThisWorkbook.Names("bar").RefersTo =Sheet1!$A$2,Sheet1!$B$2 ? Application.ConvertFormula(ThisWorkbook.Names("bar").RefersTo, xlA1, xlA1) =[Book1]Sheet1!$A$2,[Book1]Sheet1!$B$2 ? Application.Range(Application.ConvertFormula(ThisWorkbook.Names("bar").RefersTo, xlA1, xlA1)).Address =$A$2,$B$2

edited Jan 11, 2013 at 15:07

answered Jan 11, 2013 at 14:13

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wolfgang Kuehn Over a year ago

Global range refers to the active workbook. There may be multiple workbooks cross-accessing named ranges, though that is not my use case, currently.

GSerg Over a year ago

I know that. There is no Workbook.Range unfortunately. I'm actually quite surprised to learn RefersToRange falis on multiarea addresses. I suppose you're not going to parse the RefersTo yourself?

Wolfgang Kuehn Over a year ago

No way, no brittle string operations.

GSerg Over a year ago

@amadeus No, actually doesn't look good. It always appends the active workbook name, not the name of the workbook to which the range belongs. Which is understandable if you think about it. Sadly, even relative R1C1 addressing does not help if I pass the correct anchor cell as the last parameter to ConvertFormula, it would still use the active workbook name.

Max von Hippel · Accepted Answer · 2018-06-02 02:48:45Z

0

A wrinkle here is that RefersToRange fails for a multi-area named range defined by:

=Sheet1!$B$2 + Sheet1!$D$4

But it will work for:

=Sheet1!$B$2 , Sheet1!$D$4

The difference being , vs +.

edited Jun 2, 2018 at 2:48

Max von Hippel

2,9683 gold badges31 silver badges47 bronze badges

answered Jun 1, 2018 at 21:08

Five Nine

113 bronze badges

Collectives™ on Stack Overflow

How to access a named range in Excel-VBA with multiple areas?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related