Error setting value of cells in disjoined named range

[Carl Colijn]

Hi all,

I have named ranges that could contain disjoined cell references (e.g. a named range named "MyRange" set to "=Sheet1!A5;Sheet1!B2"). I want to set the value of all cells in these named ranges to certain values via VBA.

But when I in turn try to access the actual range in such a disjoined named range I receive the common 1004 error;
- accessing contiguous ranges like this does work
- Names("MyRange").RefersTo properly lists "=Sheet1!A5;Sheet1!B2"
- anything starting with Names("MyRange").RefersToRange gives error 1004
- even the Watch window can't show info on RefersToRange (and lists an error instead)

Note that Worksheets(1).Ranges("MyRange").Areas etc. do work, but the named range may be split across several sheets, and I'd rather just access the ranges via the Names collection in stead of vetting out which sheet(s) they're on and doing the address parsing and looking up myself.

Anyone have any experience with this? Is it a known limitation on Name.RefersToRange?

Thanks in advance!
Carl Colijn

 

[pgc01]

Hi Carl

I tried

Sub Test()
 
Names.Add Name:="MyRange", RefersTo:="=Sheet1!B2,Sheet1!C3,Sheet1!A4"
 
Range("MyRange").Value = 6

End Sub

and had no problem. Please try.

... but the named range may be split across several sheets, ....

???

I don't think that a range can span several worksheets. Can you give an example of how you did this?

 

[Carl Colijn]

Hi Carl

I tried

Sub Test()
  Names.Add Name:="MyRange", RefersTo:="=Sheet1!B2,Sheet1!C3,Sheet1!A4"
  Range("MyRange").Value = 6
End Sub

and had no problem. Please try.

Hi pgc01,

Thanks for trying!

But what the above does is create a range in the current workbook and then uses the Range method of Application (and thus again the current workbook) to set it's value. It does work indeed, but my situation is slightly different.

I have another workbook (with a range defined in it) but my own workbook is the active one. So I cannot just use "Range" without qualification (i.e. Application.Range), since that tries to find the range in my own workbook. And since a workbook object itself doesn't have a global ".Range" method (only the sheets have one), I cannot access the names range via ".Range". Unless I use OtherWorkbook.Worksheets(...).Range, but then I'd have to parse out the sheet reference in the name to find out which sheet I need to use (simply just using sheet 1 while the range is e.g. on sheet 2 gives an error).

The only other option left to me thus seems OtherWorkbook.Names (the names can be accessed per workbook), but then I run into the 1004 error when accessing my found Name's .RefersToRange...

So it seems neither way is flexible enough to get me to my destination - maybe I should just resort to parsing the Name's.RefersTo myself and access the cells manually from there. A Split on "," combined with a consecutive split on "!" will give me a list of [Sheet + Cell] references which I can then process further.

This code explains it all:

Sub x()
  ' Make our own Name
  Call ThisWorkbook.Names.Add("MyRange", "=Sheet2!A1,Sheet2!B2", True)
  
  ' Create a new workbook with the same name
  Dim oWorkbook As Workbook
  Set oWorkbook = Application.Workbooks.Add
  Call oWorkbook.Names.Add("MyRange", "=Sheet2!A2,Sheet2!B1", True)
  
  ' This code indeed sets the new workbook's range, since it's the active one
  Application.Range("MyRange").Value = "Set first"
  
  ' But when we are the active one...
  Call ThisWorkbook.Activate
  
  ' ... this code sets OUR range and not the other workbook's
  Application.Range("MyRange").Value = "Set second"
  
  ' The code below just gives an error (wrong sheet for the range)
  On Error Resume Next
  oWorkbook.Worksheets(1).Range("MyRange").Value = "Set via sheet 1"
  Call MsgBox("Error via other sheet: " & Err.Number & " - " & Err.Description)
  Call Err.Clear
  
  ' And this code just gives an error (a Name just can't handle it with disjoined ranges)
  oWorkbook.Names("MyRange").RefersToRange.Value = "Set via Names"
  Call MsgBox("Error via Names: " & Err.Number & " - " & Err.Description)
End Sub

???

I don't think that a range can span several worksheets. Can you give an example of how you did this?

Well, when you go to Name Manager you can type in anything you want it seems. But unfortunately those type of cross-worksheet names will generate an "error 1004 - Method 'Range' of '_Application' failed" when you actually use them. So no luck there

But if I resort to parsing the Name.RefersTo by code this will allow me to use mult-sheet references again, which is a nice added bonus since I can really use that kind of functionality as well.

 

[pgc01]

Hi

I just did a small test using a named range in one workbook being written from anoter one.

This what I did.
- I opened excel and created 2 workbooks
- in workbook(2) I created the workbook named range

Name: Test
Refers to: =Sheet1!$B$4,Sheet1!$B$6,Sheet1!$D$3

As you see the named range consists of some scattered cells as in your case.

- Activated the fisrt workbook

- with the first workbook active ran:

Sub test()
Dim r As Range
 
Set r = Workbooks(2).Names("Test").RefersToRange
r.Value = 5
End Sub

I worked with no problem. Can you try it?

 

[Carl Colijn]

Hi

I just did a small test using a named range in one workbook being written from anoter one.

This what I did.
- I opened excel and created 2 workbooks
- in workbook(2) I created the workbook named range

Name: Test
Refers to: =Sheet1!$B$4,Sheet1!$B$6,Sheet1!$D$3

As you see the named range consists of some scattered cells as in your case.

- Activated the fisrt workbook

- with the first workbook active ran:

Sub test()
Dim r As Range
 
Set r = Workbooks(2).Names("Test").RefersToRange
r.Value = 5
End Sub

I worked with no problem. Can you try it?

Hi PGC,

Thanks again!

I tried it verbatim and the code just bombed out on the line "
Set r = Workbooks(2).Names("Test").RefersToRange" with an error "1004 - Application-defined or Object-defined error".

But then I realized you used a "," when seperating the subranges in the range definition, while on my machine (set to the Dutch locale) Excel forces me to use a ";" as list seperator. So everywhere when entering seperators in Excel I need to enter a ";" and not a ",". In VBA Name.RefersTo of course still uses the 'code-universal' "," in it's representation. So I went to Control Panel, set Regional Settings to English(US) (without even closing this Excel test project) and the code immediately ran just fine thank you very much... Then I customized the English locale by making it use ";" as list seperator and the code again didn't know what to do anymore.

Seems like this is yet another Excel bug then - it forces you to use the machine's locale-set list seperator, but it can't handle it itself when it is not the US-default ","... I guess internally Name.RefersToRange doesn't use Name.RefersTo's result to get the range address, but uses the GUI equivalent instead and then just dies getting the range. Calling Application.Range("Sheet1!A1,Sheet1!B2") works with all locale settings, (i.e. even if the locale says it's a ";"), so that supports the above assumption. Pity though that solution forces you to still know the sheet the range is on, since you can't use Application.Range due to which workbook is active, and thus you need to resort to Worksheet.Range (if only there were a Workbook.Range).

By the way: this is on an English Windows XP machine with an English Office 2007 with the Dutch locale set.

Since my clients won't like changing their locale just for this, I'll have to resort to manual range reference parsing I guess? But then it would be very welcome if the name reference could span multiple sheets anyway, which is not allowed when using Range's, which also suggests I just parse the cell references myself.

 

[Carl Colijn]

Ah well, sometimes it's just not worth it to keep struggling to find the "correct" solution and instead just hack together some workaround. See below; it just works. Hopefully it'll someday alleviate someone else's frustration

Sub Test()
    Call SetNamedRangeToValue(Workbooks(2), "Test", "Now it works!")
End Sub
 
' Sets each cell in the given named range on the given workbook with the given value
' - The workbook doesn't need to be active,
' - The PC's locale doesn't need to be set to EN/US
' - The range may contain an 'illegal' mix of multiple sheet references
Public Sub SetNamedRangeToValue(oWorkbook As Workbook, sRangeName As String, sValue As String)
    ' Get the named range's total reference
    ' (without the leading "=")
    Dim sTotalReference As String
    sTotalReference = Mid(oWorkbook.Names(sRangeName).RefersTo, 2)
    
    ' And process each area in it
    Dim asAreaReferences() As String
    asAreaReferences = Split(sTotalReference, ",")
    Dim sNextAreaReference As Variant
    For Each sNextAreaReference In asAreaReferences
        ' Seperate the sheet name from the cell reference in this area
        Dim asReferenceParts() As String
        asReferenceParts = Split(Trim(sNextAreaReference), "!")
        Dim sSheet As String
        sSheet = Trim(asReferenceParts(0))
        Dim sCells As String
        sCells = Trim(asReferenceParts(1))
        
        ' Get the range representing this area
        Dim oRange As Range
        Set oRange = oWorkbook.Worksheets(sSheet).Range(sCells)
        
        ' And set it's value
        oRange.Value = sValue
    Next
End Sub