Results 1 to 6 of 6

Trying to read hard value named range

Threaded View

Oppressed1 Trying to read hard value... 03-02-2016, 11:05 PM
mikerickson Re: Trying to read hard value... 03-02-2016, 11:20 PM
Oppressed1 Re: Trying to read hard value... 03-02-2016, 11:28 PM
mikerickson Re: Trying to read hard value... 03-03-2016, 11:04 AM
Oppressed1 Re: Trying to read hard value... 03-03-2016, 01:48 PM
Oppressed1 Re: Trying to read hard value... 03-07-2016, 11:19 AM
  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Trying to read hard value named range

    I have a normal worksheet range MyRange from A1:B3, 3 rows, 2 columns
    10 20
    30 40
    50 60

    Code:
    dim vvv as variant 'variant to permit cool range assignment
    vvv=range("MyRange")

    That probably looks unusual to you but check it out. You can assign a range to a variable, though AFAICT it can only be to a variant (or you'll be told you can't assign to an array).

    So now I can go
    ?ubound(vvv,1), ubound(vvv,2), vvv(2,2)
    3 2 40
    So far nice. You can see vvv in the watch window and it looks like a 3 by 2 array.

    Next step, create an "invisible" hard value named range
    ThisWorkbook.Names.Add "rngHoldValues", vvv

    This works. But I can't seem to practically access the new range. Well commented code with useful debug.prints is attached. Walking that code is the best way to see the problems. That's my question. How can I access "rngHoldValues" ? (Summary: range("rngHoldValues") is unrecognized in VBA!)

    My objective is to
    1. save off MyRange into memory (an interim step for 2., works okay)
    2. create an "invisible" hard coded workbook range to hold the values (works okay)
    3. Be able to retrieve the scratch range and put it in MyRange (ummm...)

    FYI, all this is because other (unshown) code will overwrite MyRange, but I want to be able to recover the range contents. So if I crash, or end code, or heaven forbid, save the sheet with the overwritten values still in MyRange, then I'll have a routine to restore the values from the "invisible" created range.
    Attached Files Attached Files
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Read Excel named range to Array
    By dizabled in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-31-2011, 10:09 PM
  2. Replies: 3
    Last Post: 11-08-2010, 04:14 PM
  3. Replies: 1
    Last Post: 02-29-2008, 02:14 PM
  4. Read a named range into an array
    By brucemc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2007, 06:20 PM
  5. Read from Named Range?
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2006, 03:55 PM
  6. Named cells in a worksheetchange macro instead of hard cell refere
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2005, 10:05 AM
  7. Modify "pick from list" to read named range
    By N E Body in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-29-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1