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?

Answer 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