Trying to read hard value named range

[Oppressed1]

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.

[mikerickson]

Why not just use the name MyRange.

If you want to convert it to an explicit array, you'll have to do something like

Please Login or Register  to view this content.

(If any of the elements are strings, you'd have to make accommodations for the needed quotation marks)


Its much quicker to deal with the existing name MyRange.

[Oppressed1]

Thank you but, no, I have no problems using MyRange, or code arrays. I'd like to know how to read the hard value named range. [EDIT: I understand that I can make a "visible" range copy that survives workbook closing. I'd like to do it asd I laid it out here though. I spent hours making a concise but very informative code example, attached to O.P.; I tried to make it easy for someone who understands "hard ranges" to solve it with the attachment]

[mikerickson]

This is one way

Please Login or Register  to view this content.

this is another

Please Login or Register  to view this content.

BTW, according to the Object Browser, "hard" is not a property of a range object.

[Oppressed1]

No. Thanks, Mike; I've seen how very knowledgeable you are on many Excel topics, but neither of your posts is relevant to my question.

Is anyone actually familiar with working with ranges of the form
={10,20;30,40;50:60}

If so, I'd really appreciate if you'd spend just one minute walking through the attached code. There are only 14 executable lines and half of them are debug.print. I put in lots of comments. I really worked hard to strip a complex project down to a small file and short code, and tweaked and tweaked and tweaked it so one of you could easily address the errors.

In fact, if you're an enthusiastic coder like me, you'll find that it's actually very interesting, what the code does (and what it fails to do). The assignment
vvv=range("MyRange")
is really surprising to me. I've also read that it is the superefficient approach to processing large amounts of worksheet data.

Maybe that form of a range (shown above with curly braces) is too obscure, so no one (that sees this) has worked with them. Oh well.

[Oppressed1]

Bump please.