Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disappearing Ranges

    The attached workbook has 2 ranges defined with scope Sheet3.

    The range bbb refers to =Sheet3!$E$4
    whilst the range aaa refers to =Sheet1!$E$4

    There is a macro in there which attempts to print the values of each of these:
    Debug.Print Worksheets("Sheet3").Range("bbb").Value
    Debug.Print Worksheets("Sheet3").Range("aaa").Value

    The former works fine, the latter gives a 1004 runtime error.

    So how does one get the value of the aa range into a macro?
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Paul,

    I'm a little confused as to what you are trying to accomplish here.
    Your workbook shows both range names as having a scope of Sheet3 which is causing the problem in my opinion.
    Are you trying to use the same Range Name on multiple sheets?
    I deleted "aaa" and redefined it and now things seem to work just fine.
    The macro prints the appropriate results and cell E6 on Sheet3 shows the value from cell E4 on Sheet1.
    See graphics and attached workbook.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You now have aaa defined at workbook level.

    In my original workbook I had 2 ranges defined with a scope of Sheet3.

    If I use a formula on Sheet3 of =aaa it correctly displayed the value of cell Sheet1!$E$4.

    If I use a formula on Sheet2 of =bbb it will correctly display the value of cell Sheet3!$E$4

    However in VBA, I can access the value referenced by Worksheets("Sheet3").Range("bbb").Value but not the value referenced by Worksheets("Sheet3").Range("aaa").Value

    I am trying to accomplish getting the value of the referenced by Worksheets("Sheet3").Range("aaa").Value into the macro. You have redefined the range aaa so that it has WorkBook scope. I am trying to get the value when it is defined as having WorkSheets("Sheet3") scope.

  4. #4
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your second debug print statement says "Sheet3" as the sheet where range aaa is located. However, it is located on sheet1. Excel looks for a local range name "aaa" on sheet 3, can't find it and gives an error. Try: Debug.Print Worksheets("Sheet1").Range("aaa").Value

    Bob Flanagan
    Last edited by Medico; 2012-10-09 at 16:07.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did try that. Did you try it before posting?

    The referred to range may be located on Sheet1, but the scope of the range name is Sheet3. I was very careful to put this information in the OP.

    Worksheets("Sheet1").Range("aaa") will not find the range name on Sheet1 since the scope of the range name is Sheet3.

  6. #6
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul, yes I did test.

    Debug.Print Worksheets("Sheet1").Range("aaa")

    will print the value of aaa found on sheet1. Since aaa is not located on sheet 3,

    Debug.Print Worksheets("Sheet3").Range("aaa")

    returns runtime error 1004, application-defined or object-defined error. Which means that excel/vba can not find a range named aaa on sheet3.

    I suspect the issue is what you mean by: "the scope of the range name is sheet3". I'm afraid I don't understand that statement. Could you expand on what you mean by "scope"?

    Bob

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you tried that in my workbook (which I attached to the OP) then I do not believe that you got an output.

    You can learn about the sope of range names by reading the help here:
    http://office.microsoft.com/client/h...ersion=14&tl=2

    If you go into the name manager on my sheet you will see that the scope of aaa is Sheet3. If you go to sheet 1 and type
    =aaa
    into a cell you will get #NAME
    This is why Debug.Print Worksheets("Sheet1").Range("aaa") can not work. Try going to the Name Box and click the drop down arrow on Sheet3. You will see aaa and bbb
    Try that on Sheet1 and you will see an empty box.

    I have no idea what you used to get the value of aaa on Sheet1 printed but you did not use Debug.Print Worksheets("Sheet1").Range("aaa") in my workbook.

    If you did it on RetiredGeek's Copy of disappearing range.xlsm then you will have got Sheet1-E4. On mine you will get an error.

    I suspect that this may be an Excel bug.

  8. #8
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul, I think I am getting closer to understanding your problem.

    I did not download your workbook, as I tend to not do so to avoid potential problems with viruses. I took your description and duplicate it. However, your description did not mention that the ranges you created were LOCAL range names, only on sheet3. I created default global ranges, with aaa on sheet1, and bbb on sheet3. Thus my use of sheet1 when I tested did work.

    You are getting the problem because you have created two local range names on sheet3. One of the range names, bbb, refers to a cell on that sheet, e4. The other local name, aaa, refers to a cell on sheet1. Excel can not interpret aaa to a range. I suspect there is a formula trick one can use; I don't know this.

    To further illustrate what I am saying, try this: Type "ccc" in a cell. Select it. Press CTL-F3. Excel will ask if you wish to create a range name by the name ccc. Click OK. Now press CTL-F3 again. You will see a list of all three range names on that worksheet. The local ones have "sheet3" by them. Since ccc is not local, it does not have a sheet name by it. If you go to any sheet, you will see ccc in the names box.

    So, that means the task is how to find out what range a local name on a sheet refers to. The Names collection is fairly limited. One must search it for a match. And then once found, return the refersTo property. This will begin with an equal sign. One then converts it to a range. Something like this will work:

    Sub ReturnValue()
    Dim anyN As Name
    Dim anyR As String
    Dim cell As Range
    For Each anyN In ActiveSheet.Names
    If Right(anyN.Name, 4) = "!aaa" Then
    anyR = anyN.RefersTo
    anyR = Mid(anyR, 2)
    Set cell = Range(anyR)
    MsgBox cell.Value
    Exit Sub
    End If
    Next
    MsgBox "No match found."
    End Sub

    You do have to be on the right sheet to use. The reason for testing with ! as part of the name is to insure that a match to a name like aaaa is not found. And using activesheet.names does just the names on the activesheet vs activeworkbook.names.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Paul,

    You may want to read this article to help clarify the usage of the local scope for range names.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Bob:
    You said "However, your description did not mention that the ranges you created were LOCAL range names, only on sheet3."
    The very first line of the original post said "The attached workbook has 2 ranges defined with scope Sheet3."

    You said "Excel can not interpret aaa to a range"
    I say that it can and does. If you look in my sheet you will see that I have =aaa in a cell on sheet3 and Excel is interpreting it and showing the value of cell Sheet1!E4

    Your comment "You do have to be on the right sheet to use" is not true since in VBA you can (and normally should) qualify your locations using the correct level objects, possibly within a With block.

    Not being funny but your explanation about how to use the name manager was a bit of a waste of time since I had already referred you to that myself.

    In fact I have worked out that the answer to my question is to use:
    Worksheets("Sheet3").Names("aaa").RefersToRange.Va lue

  11. #11
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek,
    that article talks about using them in sheet formulas and if you looked at my workbook you'd see it was already working there. In fact the help link I supplied earlier gives more information on range scope.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts
    You need to access the sheet's Name object:
    Code:
       Debug.Print Worksheets("Sheet3").Range("bbb").Value
       Debug.Print Worksheets("Sheet3").Names("aaa").RefersToRange.Value
    Regards,
    Rory
    Microsoft MVP - Excel.

  13. #13
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Which I posted above 2 days ago once I worked it out.

    Quote Originally Posted by rory View Post
    You need to access the sheet's Name object:
    Code:
       Debug.Print Worksheets("Sheet3").Range("bbb").Value
       Debug.Print Worksheets("Sheet3").Names("aaa").RefersToRange.Value

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts
    Guess I should read more closely. Won't bother you again.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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