Results 1 to 4 of 4

Thread: Range Names

  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Names

    How can I create a Worksheet Level Range Name in Excel in VBA? In Excel itself you need to add '!' before the range name, such as Name: Data RefersTo: =!A14. How can I do this in programming? Cheers, Andy.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Names

    Try something like :
    <pre> ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Sheet1!A14"</pre>

    which should name the Range A14 as Test.

    Hope that is of assistance

    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Names

    Thank you for your prompt reply.
    However, I wish to create a range name that doesn't refer to a specific sheet. In Excel you can use RefersTo: =!$A$1:$D$4 (without a sheet reference). Typing the range name in the name box and pressing Enter selects A14 for whichever sheet you happen to be in. However, trying to do the same in VBA generates an error.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Names

    Sorry about that, but try the following :<pre> ActiveWorkbook.Names.Add Name:="TEST", RefersToR1C1:="=!R1C1:R4C4"</pre>


    I think that might give you what you want

    Andrew

Posting Permissions

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