Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

this is post #599
https://www.excelfox.com/forum/showt...ll=1#post24132
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24132&viewfull=1#post24132
https://www.excelfox.com/forum/showt...ge60#post24132
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24132

---

Put Formulas in cells, with Evaluate(" ") , or use them in Evaluate Range type solutions if they are involved with new line characters

The original issues that sparked off this page, can be dealt with, or rather can be almost forgotten and / or don’t occur, as we now have a much better in depth understanding of the required string constructions, so can go directly to them, armed with our new knowledge of what fundamentally the Evaluate(" ") is /does

' 2a Char(13)&Char(10)
Char(13)&Char(10) is recognised Excel syntax. The characters of a and b are not
VBA wants to see this string in order to put the formula into the cell
"=""a""&Char(13)&Char(10)&""b"""
So producing all those bits from Evaluate(" ") requires carefully producing each of those characters

 """" & "=" & """" & """" & "a" & """" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & """" & "b" & """" & """" & """"

With the previous knowledge, it’s fairly straight forward. Here again in words what we have done: We consider the string that VBA wants to have , in its simplest form. ( It must be in the simplest form without any of the VBA " & " bits, since Evaluate(" ") in this use if it is only going to return to us a simple string, not a code line. By the same reasoning the Evaluate(" ") won’t give any other VBA things like variables and functions, so the " & " is not needed anyway.
We then make the string for evaluate, strEval . We do this by consigöring each character in turn of the string we need in VBA,

    "      =      "      "      a      "      "    &     Char(13)&Char(10)   &      "      "      b      "      "      "                                              ' For VBA

, for each of the 16 things there, we know the equivalent to have inside Evaluate(" ")
Each of those is then joined by a " & "
Here is that working result again

   """" & "=" & """" & """" & "a" & """" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & """" & "b" & """" & """" & """"