none
VBA and Range Names

    Question

  • With an add-in (XLAM), I've been encountering 1004 errors when referencing certain Range Names in VBA.

    Some of the Range Names just point to a single cell, others are complex, dynamic references to a cell or set of cells.

    It appears the Application.Range("rangename") is not reliable. However, when I point to a range using a sheetname,
    that seems to work as long as the reference is on that sheet. The scope of the range name does not appear to make a difference...most of mine are workbook in scope.

    This appears to be a bug in Excel 2007 that I also witnessed in 2003.
    I created a procedure to use a workbook as a parameter and a string as the name, and all it does is return a range defined as follows:
    poWorkbook.Names("parmname").ReferstoRange

    Even that fails !! Has anyone experienced this problem ?

    Tuesday, March 15, 2011 1:07 AM

Answers

  • Could you give me an example of your 'complex' name definitions that work in Excel 2007 but not in 2003?

     

    If you like the Excel 2007 Name Manager you really should try the original Name Manager developed by JKP and myself. Most people think its significantly better than the built-in Name Manager (certainly has more function ...)

    http://www.decisionmodels.com/downloads.htm


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Bruce Song Monday, March 28, 2011 9:52 AM
    Tuesday, March 15, 2011 4:34 PM

All replies

  • You did not post the code that creates the Name.  Within the Application, this seems to work:

     

    Sub Macro1()
    Dim r As Range
    Set r = Range("F3:I11")
    Dim s As String
    s = "=Sheet1!" & r.Address
    ActiveWorkbook.Names.Add Name:="rangename", RefersTo:=s
    '
    '
    MsgBox ActiveWorkbook.Names("rangename").RefersTo
    End Sub


    gsnu201103
    Tuesday, March 15, 2011 12:32 PM
    Moderator
  • Public Function GetRangeFromName(ByRef poWB As Workbook, ByVal pszName As String) As Range
    '
    ' Return the range for the RangeName
    ' The workbook should usually be specified
    '
    Const cPROCNAME As String = "GetRangeFromName"
    On Error GoTo Err
    
    If poWB Is Nothing Then
     Set GetRangeFromName = Application.Names(pszName).RefersToRange
    Else
     Set GetRangeFromName = poWB.Names(pszName).RefersToRange
    End If
    
    Finish:
     On Error Resume Next
     Exit Function
    
    Err:
     MsgBox "Error: " & Err.Number & " (" & Err.Description & ") in procedure '" & cPROCNAME & "' in modUtil"
     Resume Finish
    
    End Function
    

    There are literally dozens of posts thru-out the excel forums on this topic. That says it all: bug-maximus. There are hidden "rules" in Excel as to when you can use Application.Range vs. workbook.names("xxxx").referstorange, etc. It depends on the type of range name reference being made as far as I can see.

    However, it does appear that worksheet level range names work fine. But that STINKS, because now all range names must be encoded somehow with the sheet reference to avoid referencing the wrong sheet.

    Tuesday, March 15, 2011 1:54 PM
  • Excel Defined Names are basically stored formulas. The only kind of stored formula that can be handled by Range(Name).RefersToRange is a straightforward Range reference.

    To handle more complex stored formulae you need to use Application.Evaluate or Worksheet.evaluate.

    There are some quirks you should be aware of if you are going to use Evaluate:

    see http://www.decisionmodels.com/calcsecretsh.htm

    Your function works correctly for me when called from VBA with a simple Defined Name and the other workbook is open.

    There is a long-standing bug when you have the same name defined with both Global scope and Local scope: I would advise avoiding this.<br/>There is also a nasty bug if you use defined names with a reference like =!$A$1 : I would advise avoiding these.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    Tuesday, March 15, 2011 2:27 PM
  •  because now all range names must be encoded somehow with the sheet reference to avoid referencing the wrong sheet.

    An interesting comment.  For something like:

    Range("A1:B9")  can refer to any sheet, but Named Ranges will have a Parent:

     

    MsgBox Range("rangename").Parent.Name  will give the Sheet associated with the Name.  So the "encoding"  is  already there.


    gsnu201103
    Tuesday, March 15, 2011 2:38 PM
    Moderator
  • To handle more complex stored formulae you need to use Application.Evaluate or Worksheet.evaluate.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/


    Charles - thanks for the confirmation. An FYI: In Excel 2007, a reference like Activesheet.Range("rangename") works beautifully even with complex name definitions. No good for Application.Range("rangename") or even ActiveWorkbook.Names("rangename").Referstorange. To me, since the scope is Workbook level, they should work. This screams "bug".

    I've never liked Evaluate....always tricky to use. I'll look at that reference. Perhaps I just need to trap the 1004 error in my function and switch to Evaluate to handle the exception. This will eliminate the need to change all of my range names.

    The name manager in Excel 2007 is great. Alas, the VBA required to support complex, dynamic ranges is not.

    Tuesday, March 15, 2011 3:52 PM
  • Could you give me an example of your 'complex' name definitions that work in Excel 2007 but not in 2003?

     

    If you like the Excel 2007 Name Manager you really should try the original Name Manager developed by JKP and myself. Most people think its significantly better than the built-in Name Manager (certainly has more function ...)

    http://www.decisionmodels.com/downloads.htm


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Bruce Song Monday, March 28, 2011 9:52 AM
    Tuesday, March 15, 2011 4:34 PM
  • MsgBox Range("rangename").Parent.Name  will give the Sheet associated with the Name.  So the "encoding"  is  already there.

    Unless it is a global name, then the Workbook is the parent!

    Wednesday, March 07, 2012 10:53 AM