Use of Evaluate to fill an array

[brucemc777]

I am fascinated by Evaluate but so far have had such extremely limited use it might as well be non-existent and most examples I have read easily confuse me, though some I have attempted to tuck away in my memory somewhere.

From the posts I have read that keep flying over my head I believe that Evaluate must be able to take a table column and create an array out of it. In my case I want to take the column "Name" which is in the table ListObjects("tblDirectory") which, if need be known, resides on the worksheet Worksheet("Directory") and create an array out of the names in it using Evaluate.

Yes, it can be done all day long using any dang loop, but I would really like to start to gain a useful knowledge of this Evaluate thing...

Thank-you!

[Bernie Deitrick]

You don't need to use an array, or a loop - you can address the ith element of a specific column directly:

Please Login or Register  to view this content.

See here for more on how to address listobjects (in worksheet functions and in VBA) using structured references

http://peltiertech.com/structured-re...-excel-tables/

[brucemc777]

I am learning a lot of new things!

I value that which you have shown me and I still wish to learn more on using Evaluate. Any ideas of a good starting point (in the meantime I will be reading the page you referenced)?

[JackSheet_1]

I think the very basic Idea of Evaluate in its simple form is that it allows you to do what you can do in a spreadsheet in VBA
Typically this is shown as :
_____[here what you would type in a Spreadsheet cell]
Such as in this You Tube Video:
https://www.youtube.com/watch?v=CdQJqT_9uf4

_.......................................


But that is a bit of an oversimplification. In a more Full explicit Form it Evaluates a String
____Evaluate(String to be evaluated)
____Evaluate(“______”)
This more explicit form allows you to build up a String to be evaluated from both Spreadsheet things and VBA things

You can probably do a Google search yourself, ( like site:ExcelForum.com Evaluate ) but here a few useful Links i found:
http://www.ozgrid.com/forum/showthread.php?t=52372
http://www.mrexcel.com/forum/excel-q...powerfull.html


Often a “neat” usage is suggested to do away with a loop with a simple Evaluate “One Liner code”...
https://usefulgyaan.wordpress.com/20...ions-evaluate/

but although things like that “look pretty” it is not always found to be the better, or the quickest way...
http://www.excelforum.com/showthread...t=#post4331102

_....................................

To get to use this more explicit version you need to be able to handle the use of the Quotes in VBA and Evaluate which can be very mind blowing!!

http://www.mrexcel.com/forum/excel-q...lications.html
http://www.mrexcel.com/forum/general...ml#post4468324
http://www.excelfox.com/forum/f2/spe....html#post9517



Jack

[Bernie Deitrick]

In addition to the other posts, here is some example code - the important thing is that you don't need to use the array as an array, but if you do, it is a 2D array rather than a 1D array:

Please Login or Register  to view this content.

[brucemc777]

Thanks guys!

I guess there is no "Idiot's Guide to Evaluate"...

As is cited in some of those articles, some of which I have had the pleasure of scratching my head at, is the phrase stating that Evaluate has so many uses. That starts my head spinning right there. Then, as Jack states, the combination of sheet formula, vba and quotation marks just kind of causes a brain overload.

Best course at this point is to sit down and start running examples, as I suspect that's how you folks worked it.

I appreciate the help.

[JackSheet_1]

.....as Jack states, the combination of sheet formula, vba and quotation marks just kind of causes a brain overload.....

This has done my head in frequently and i seem to need to re -learn every time i do things with Evaluate, especially the quotes. The simplified "short hand" [ ] simplifies the expression, especially regarding the quotes... but you loose the flexibility..... for example:
https://usefulgyaan.wordpress.com/20...x/#comment-575


_.....................

...... examples, as I suspect that's how you folks worked it.
I appreciate the help.

correct, practice practice and more practice...
Thanks for the feedback, glad we could help..

P.s. some other subtle differences I just remebered when using the "shorthand" [ ] to do Named Range and Range referrencing stuff
http://www.mrexcel.com/forum/excel-q...l?#post4332606

[brucemc777]

The portion by Mr. Sullivan as follows is finally sinking through my skull. I hope it is not in violation of a policy to "re-post" the following; I do so as it might help others as I found this one of, if not the, most complicated aspects of decoding what the heck was going on; it was a vital link that I had missed (and THANK-YOU!)-

2 Red Quotes are needed to literal quote in the expression
="Year " & "2013" -> =""Year "" & ""2013""

1 Blue quote is needed around each string part that isn't a variable
"string1" & variable2 & "string3"
where string1=""Year "" & ""
variable2= 2013
string3= ""

Substituting values of string1, variable2 and string3 yields
"""Year "" & """ & sYear & """"

[Doc.AElstein]

The portion by Mr. Sullivan as follows is finally sinking through my skull. I hope it is not in violation of a policy to "re-post" the following; I do so as it might help others as I found this one of, if not the, most complicated aspects of decoding what the heck was going on; it was a vital link that I had missed (and THANK-YOU!)-

Yes - Some Nicely summarised results in that Thread... But you will go crazy if you try to prove those results!!

[brucemc777]

...and I was thinking that I was finally seeing a glimmer of light-
Silly Rabbit... (Recalling the scene from Kill Bill)

[JackSheet_1]

......(and THANK-YOU!)-
...
...and I was thinking that I was finally seeing a glimmer of light-
Silly Rabbit... (Recalling the scene from Kill Bill)

Want to go insane, just remembered this one.... why not do it in German just to get really confused
http://www.excelforum.com/non-englis...ml#post4221013
http://www.excelforum.com/non-englis...mensetzen.html

Hi pin lui.......

_ .. Und weiter , noch eine Version, damit man wirklisch verwirrt wurde, .......