Hi YasserKhalil,
Happy New Year.
The Evaluate , especially the “full” version Evaluate(“ “) is a thing that often has caught my interest.
Along the lines of what Hans said, .. The major difference in the two forms , ( the full version Evaluate(“ “) and the “shorthand” version, [ ] ) , is that the “full” version Evaluate(“ “) takes a string as argument which allows you to build up that string with a combination of spreadsheet and VBA things, as was the case in your first post.
You can do some nice one liner codes with Evaluate whereby you evaluate a range . Here you can do some nice “tricks” to make the Evaluate work like an ..
Excel array formula in disguise.. You can trick the Evaluate into doing Array calculations, returning you an array of values which you would normally only get with a looping process. Hence a single line can replace a looping process. This can have often result in something working much quicker than in normal VBA looping techniques.
Another way to think about the difference in the two versions:
For a [ ] you ( almost ) put in it exactly what you put in a cell. (The only difference is that you can leave off the = . I expect VBA puts them in but just does not show you. You can always add a = and that is from the syntax accepted ). ( Often people use the look of the square brackets as looking like a cell to remind them of this point. )
You cannot put VBA things in a cell, so you cannot put them in a [ ] either.
Note, for some of the things that you can put into [ ] , you would need to do the CSE Array type entry to get them to “work” if you put them in a cell. You do not need to do that CSE entry when using VBA Evaluate. However you may need to do the clever “tricks” to get the evaluate to do Array calculations. In some ways you can consider that as similar to when you sometimes select a number of cells in a spreadsheet before you do the CSE Entry. So you see from that way of thinking you come to the idea of an .. ..
Excel array formula in disguise…
For a Evaluate(“ “) you have to arrange that the evaluated string ends up looking like what you have in a cell … that can be a nightmare then with double - doubled quotes and the such.. the tricky way of building up that string, especially where the quotes are concerned***, along with the experience needed to master the “tricks” is probably one reason why this technique has not been used to its full potential. Some of the most experienced people in Excel and VBA will tell you ( possibly not publically ) that they do not understand many of these techniques…
I do not believe that there is any concise documentation from Microsoft on many of the advanced Evaluate techniques. I do not believe any Microsoft person understands the subject very well. The people### who understand these things are few and far between. Catch post from Rick Rothstein, a lot at MrExcel, as one of the few people who seem to be able to master this subject effectively
Alan
Ref:
***
http://www.eileenslounge.com/viewtopic. ... 98#p196259" onclick="window.open(this.href);return false;
### .
Excel array formula in disguise… Rick Rothstein, Willy Vanhaelen
http://www.excelfox.com/forum/showthrea ... #post10201" onclick="window.open(this.href);return false;
https://excelribbon.tips.net/T012042_Re ... _Name.html" onclick="window.open(this.href);return false;
https://excelribbon.tips.net/T010768_Sh ... Codes.html" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
