Results 1 to 8 of 8
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Handling Invalid Range Names in VBA (2000 +)

    I have code that uses a range name made of discontiguous cells that errors if a user has deleted rows or columns containing part of the range, because the name reference becomes invalid, like this:

    =Summary!$B$55:$B120,Summary!$B$59:$B$60,Summary!# REF!

    The cells are always in the same location so I guess I can rebuild the name at the point when that code is run, but since the Range Names are preset in the template I'm wondering if there is another solution ...
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    John,

    The name definition =INDIRECT("Summary!$B$55:$B120"),INDIRECT("Summary !$B$59:$B$60"),INDIRECT("Summary!$A$1") will not suffer the same fate.
    Note the last non-contiguous range needs to be updated to suit.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    Have you actually tried that? I can't get INDIRECT to work with any range of more than one cell in XL2K.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    Legare,

    I did test it briefly, throughtthe use of Jan Karel Pieterse's Name Manager utility (version 3.1 build 478) and highlighting the range areas.

    It correcly highlighted the discrete range areas, so I assumed the range was valid.

    Maybe XL2002 perfoms differently.
    Debug.print worksheets("Summary").range("Test").areas.count returns 3 as expected for me.
    What is the result for 2000 +

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    Keiran, I can create the range name in Excel XP and 2003 as you suggest using =INDIRECT() in the range name, it appears to work in my situation. Thanks!
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    Note that the most current build is 483, available from here
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    If I do that on my XL2K, I get this:
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Handling Invalid Range Names in VBA (2000 +)

    However, changing just the the range name from:

    =Summary!$B$55:$B120,Summary!$B$59:$B$60,Summary!$ B$65:$B$66

    to

    =INDIRECT("Summary!$B$55:$B120"),INDIRECT("Summary !$B$59:$B$60"),INDIRECT("Summary!$B$65:$B$66")

    added about 10k to the workbook size. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    Perhaps I'll take the name out and code it instead.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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