Anyone fluent with .Names.Add using values rather than addresses? (advanced)

[TheAntiGates]

Are you fluent with .Names.Add using values rather than addresses? If so can you please follow this very short sequence and answer one question?sub foo()
dim i as integer
i=10
ThisWorkbook.Names.Add "OddRange", i
end subThis creates a "defined name range" named OddRange and it looks like this in Excel (for XL03 go alt-i-n-d; for 2007+ you're on your own):
=10

Now: how can I read that range in VBA? Anything in code saying
range("OddRange")
fails. If you shift-F9 it you get
<Method 'Range' of object '_Global' failed>.
I find no way to access the range or its contents. Try it. Can you solve? What line of code would produce the value 10 from that range?

I'm sorry to be a d____e but lately I've posted hard questions (on other boards, of course) and even real smart members get frustrated because they can't solve it or lack expertise in some aspect of the question, so they either reply with something absolutely irrelevant or lecture about their displeasure with me trying to do what I'm trying to do. This is a hard question, and if you're frustrated that you can't really speak to it, you don't need to diminish the thread with distracting noise such as "you can create the range without VBA" or "Why don't you just use 10 instead of a range set to 10?" I hate having to say this, but lately it seems I needed to say this 100 times. Obviously if you don't have the solution, but want to say something relevant to the actual question or ask for more info or post anything that will indeed help lead to the actual answer, of course that's great. Absolutely. I'm really not a total d____e, but I've been running into a slew of them on my questions lately! No offense to those who deserve none.

[Bob Phillips]

ActiveWorkbook.Names("OddRange").RefersTo

or


[OddRange]

[TheAntiGates]

Man oh man, how you rock. I would never have gotten there. Patting myself on head for choosing the time you were online to ask the question :hi::cloud9:

[TheAntiGates]

Sorry, though marked solved, I'm a little stuck using those range styles, especially with arrays. Expanding previous code:Sub foo()
Dim i As Integer, myArray(1 to 2, 1 to 2) as integer, vvv as variant
i=10
myArray(1,1)=1
myArray(2,1)=3
myArray(1,2)=2
myArray(2,2)=4
ThisWorkbook.Names.Add "OddRange", i
ThisWorkbook.Names.Add "ArrRange", myArray
STOP ' and smell the following
End SubFirst, trying to access OddRange:
?2*[oddrange] 'is fine, gives 20

?activeworkwook.names("oddrange").refersto 'gives
=10
Can I access activeworkwook.names("oddrange") any other way than parsing .refersto as a string?
Is evaluate(activeworkwook.names("oddrange").refersto) (or with other 'refers') the only other way?

Now the real mess is using ArrRange:
?[ArrRange] 'gives error 13 Type mismatch while code running; else Error 2023
[ArrRange].anything: I can't find any properties including offset
[ArrRange](1,2) 'invalid property assignment
[ArrRange(1,2)] 'Error 2023

?activeworkbook.names("ArrRange") 'works giving
={1,2;3,4} (note the semicolon, as you'd expect)

I'm having a hard time using that ={1,2;3,4}
About the only way I could figure to do anything with it is treat as a string
?left(activeworkbook.names("ArrRange"), 3) '(etc. with string operations)
I could parse on the commas (e.g. with Split()), but, yuck, must be a better way

I came upon a desperation measure,
vvv=[ArrRange]
?vvv(2,1) 'works. ubounds work too, nice.
Is there any other way than that to numerically get into [ArrRange] ?
Or any other way to drill into .names("ArrRange") ? Or anyway at all to drill [ArrRange] ?

I huffed and puffed on this but maybe there's a simple answer I'm blindly overlooking. Thanks.

[Paul_Hossler]

Bit cumbersome, but seems to work



Array([ArrRange])(0)(1,2)



15668

[snb]

Sub M_snb()
Dim i As Integer, myArray(1 To 2, 1 To 2) As Integer, vvv As Variant

i = 10
myArray(1, 1) = 10
myArray(2, 1) = 35
myArray(1, 2) = 24
myArray(2, 2) = 42
ThisWorkbook.Names.Add "OddRange", i
ThisWorkbook.Names.Add "ArrRange", myArray

sn = [arrrange]
MsgBox sn(1, 2)

' or

msgbox [index(arrrange,2, 2)]
End Sub

[TheAntiGates]

You're right, Paul_Hossler, Array([ArrRange])(0)(1,2) does seem to work. Nice work. Talk about obscure and esoteric.

snb your undeclared variable sn is the same as the "desperation" vvv. But the use of Index
[index(arrRange,2, 2)]
is brilliant. Esoteric doesn't even cover it. I never heard of the VBA command index.

In fact I still can't find it, and I'm a googlin' fool! I understand worksheetfunction.index, yes (or should I say worksheetfunction.INDEX - or should I say .Index, as the compiler translates it). But even that doesn't work, while your index does:
?WorksheetFunction.Index(Range("arrRange"), 2, 2) ' ERROR.
Your "index" seems to be something entirely different.

( EDIT: Nor is it apparently
Application.Index
which I'm not having any success using with range("ArrRange"}
It appears that Application.Index is the same as Application.WorksheetFunction.Index )

How did you come up with index?! What planet is it from! The compiler even acts like it's foreign, because, speaking of uppercase enforcement, the compiler doesn't even care if you go
MsgBox [INdeX(ArrRange,2, 1)]
( EDIT 2: I understand ArrRange or arrrange being ignored by the compiler, just as you can go range("a1") or range("A1"). However I've always counted on the compiler case-correcting KEYWORDS )

These are some wild answers IMO. But great answers!

What do those square brackets [] exactly do anyway?!

[snb]

You might have a look at: http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0
(http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0)
[ ... ] is identical to evaluate(" ... ")

[TheAntiGates]

Mindus blowus further. So the gold solution is essentially
evaluate("index(ArrRange,2,2)")

I can almost buy that evaluate "makes things that should have a value - but don't as written - have a value."

But I hope Saint Peter never asks me for a verbal explanation of why those double quotes are there.

[TheAntiGates]

By the way, if you're Stephen Bullen, what a tremendous honor.

[Aussiebear]

But I hope Saint Peter never asks me for a verbal explanation of why those double quotes are there.

Not much chance of that I fear, for you would simply bomb him with 40 questions.

[mancubus]

evaluate method as explained in msdn:
https://msdn.microsoft.com/en-us/library/office/ff193019.aspx

visit snb's link for more advanced uses.

[TheAntiGates]

Not much chance of that I fear, for you would simply bomb him with 40 questions.No doubt. This is a little bit far out stuff, at least for me, right now, and I'd expect any coding fanatic to be made thirsty to learn more on these new discoveries.

I gather that index as used in
evaluate("index(ArrRange,2,2)")
or equivalently
[index(ArrRange,2,2)]
is Application.Index Being in quotes, for evaluate, ergo not recognized as a keyword per se by compiler, makes it a little hard to interpret.

Anyway I had not encountered Application.Index before, nor [ and ] in evaluate (which BTW is stated in that MSDN article, thanks), so I've got a lot of work to do to catch up. And the word index appears about 120 times on that massive snb page. Lots of learning ahead :)

TYVM. Solved for real. For now. Until I can come up with 32 more questions.

[Paul_Hossler]

I don't know if it makes a difference, but ...



Evaluate("Index(ArrRange,2,2)")


requires building a string (not a big deal of course), and ....



Array([ArrRange])(0)(1,2)


just deals with row and column numbers

[snb]

Eventually:



MsgBox [arrrange]()(2, 2)

MsgBox [index(arrrange,2, 2)]
MsgBox Evaluate("index(arrrange,2, 2)")

sn = [arrrange]
MsgBox sn(2, 2)

[TheAntiGates]

Thank all of the completely on point answers. Everything shown above works with one exception. The one using index requires hard indices, not variables MsgBox Array([arrrange])(0)(2, 2) 'Paul Hossler
MsgBox [INdeX(arrrange,2, 2)] 'snb 03-16-2016, 08:46 PM
MsgBox [arrrange]()(2, 2) 'snb 03-20-2016, 07:15 AM
i = 2: j = 2
MsgBox Array([arrrange])(0)(i, j)
MsgBox [INdeX(arrrange, i,j)] 'boom, error
MsgBox [arrrange]()(i, j)
A separate thread may be of interest, regarding truncation of the "value ranges" used above, thread "worksheet Names 255 or less byte truncation - bug?"

[snb]

You should have known by now:


i=2
j=2

MsgBox Evaluate("index(arrrange," & i & "," & j & ")")

or


thisworkbook.names.add "first",2
thisworkbook.names.add "second",2
MsgBox [INdeX(arrrange, first,second)]
or

sheets(1).cells(1).resize(,2)=array(2,2)
MsgBox [INdeX(arrrange, A1,B1)]

[TheAntiGates]

Perhaps I should have known the first item. For anyone else who doesn't see it right away, recognize that the 2 within the quotes is not integer value 2; it's "2" (and i is "i" unless you separate it as snb just did)

I haven't dug very deeply into the workings of Evaluate but I'll get on it. It's not immediately intuitive to me that Evaluate would "reach inside" to stick quotes on i and first, and then "dereference" (correct word here?) "first" to be range("first") - and then evaluate its content - while leaving "i" as "i" . I've got work to do :think:

EDIT: For the record, snb's answer was correct in its context. I just changed the context. :devil2:

[Aflatoon]

Evaluate takes a quoted string argument (which you can build up in code at run time). That string cannot exceed 255 characters.

The [] notation cannot be built up at runtime - it is interpreted literally as entered at design time.

[TheAntiGates]

Evaluate takes a quoted string argument (which you can build up in code at run time). That string cannot exceed 255 characters.

The [] notation cannot be built up at runtime - it is interpreted literally as entered at design time.Useful to know, and who could have guessed that. Thanks. If I ever brush that limit, perhaps now knowing about the non-build-up aspect could save me intense head-scratching.