Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Referring to named ranges

    Can anybody shed any light on the following? Within VBA, I often need to access the value in a named range. If I use Range("MyRange").Value, it mostly works as expected, but at times I get a run-time error. I've never figured out why - I generally just change it to read ThisWorkbook.Names("MyRange").RefersToRange.Value, which always works OK.

    The error message is "Method 'Range' of obect '_Worksheet' failed", suggesting (?) that I need to precede Range with the worksheet. However, the named range is at the workbook level, so ideally I would like the VBA code to work unchanged if the named range were moved to another worksheet, or if worksheet were renamed. If I'm on the right lines, then maybe success vs. run-time error is determined by what the active sheet is when the code runs...

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to named ranges

    I think your last supposition is the culprit (range not on the active sheet), but there are a couple more implications:

    Range names can be defined at the workbook level (VBA code should run as long as that workbook is active) or at the worksheet level (sheet must be active for the VBA code referencing that range name to run - OR - VBA code must fully qualify the Range name with the worksheet name). BTW, the same name can exist at both the workbook and worksheet level.
    For examples sake, let's say we have two ranges named MyRange: one is referenced on Sheet1 and shows up in the Insert/Names/Define dialog box as MyRange with its reference showing =Sheet1$A$1. On Sheet2 you can define a Name as Sheet2!MyRange with a reference of =Sheet2!$A$1.

    Displaying the Insert/Name/Define dialog box while on Sheet2 will show:
    MyRange Sheet2 with reference =Sheet2!$A$1
    (notice that the sheet name is shown on the right side)

    Doing the same while on Sheet 1 will display:
    MyRange with reference =Sheet1!$A$1
    (notice that there is no sheet name shown)

    Hope this isn't too confusing...

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Referring to named ranges

    Thanks for the response. If I have a workbook-level range name, then, and I want to access the cell's value without knowing which sheet is active, and without specifying the sheet name (for the reasons given before), presumably the best (/only?) way is the expression ThisWorkbook.Names("MyRange").RefersToRange.Value that I've been using?

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to named ranges

    I think that's the safest bet, but I haven't researched it thoroughly.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to named ranges

    I don't know which way is the preferred one, but here is an alternative way to get out the value of a named range:

    Sub test()
    Dim Sh As String
    Dim Raddr As String
    Sh = Range("MyRange").Parent.Name
    Raddr = Range("MyRange").Address
    MsgBox Sheets(Sh).Range(Raddr).Value
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •