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 the following string in order to put the formula = "a" & CHAR(13) & CHAR(10) & "b" into the cell
"=""a""&CHAR(13)&CHAR(10)&""b"""
So producing all those bits from Evaluate(" ") requires carefully producing each of those Characters
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.Code:"""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR (13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"
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,
, for each of the 16 things there, we know the equivalent to have inside Evaluate(" ")Code:" = " " a " " & CHAR(13)&CHAR(10) & " " b " " " ' For VBA
Each of those is then joined by a " & "
Here is that working result again
Code:"""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR(13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"
What finally is returned from the RHS and seen by VBA and then put in the cell is, as we would physically write it
="a"&CHAR(13)&CHAR(10)&"b"
A final simplification can be made to the evaluate string by removing all the " & " bits
"""=""""a""""&CHAR(13)&CHAR(10)&""""b"""""""
or
"=""=""""a""""&CHAR(13)&CHAR(10)&""""b"""""""
One last things that will be useful to compare with the over next section. ' 2b(ii) , will be the Debug.Print of the string to be used in Evaluate(" ") strEval , and the formula finally put in the cell and seen in the formula bar
__ "=""a""&CHAR(13)&CHAR(10)&""b""" ______ ="a"&CHAR(13)&CHAR(10)&"b"
' 2b vbCr & vbLf
' 2b(i)
We might want to go back a bit to some of the conclusion attempts here https://www.excelfox.com/forum/showt...ll=1#post24055
We said along the lines of that vbCr & vbLf was actually a text of two Characters, not much different to like xy. Even if we say that we know that vbCr & vbLf is a VBA text construct thing, used when building up a string in VBA, then that still does not help us to get anything useful out of Evaluate(vbCr & vbLf), (and this does nothing at all for us either Evaluate("" & vbCr & vbLf & "") )
This is a consequence of our better understanding of Evaluate(" ") in that it calls out an exposition of the underlying functionality, of the fundamental Excel object to which is named. This strange looking constant, vbCr & vbLf makes no sense to Excel.
Doing this Evaluate("""" & "a" & """") gives us the same results as putting ="""" & "a" & """" in a cell and the result from that world be ,
"a"
, ( regarding the extra = see ** ) That "a" is the required string to present in VBA on the RHS to put an a in a cell
To this consideration of the character a is a parallel to the Characters vbCr & vbLf - Doing this Evaluate("""" & vbCr & vbLf & """") gives us the same results as pseudo putting ="""" & "NewLineCharacters" & """" in a cell, although that pseudo syntax, NewLineCharacters , is not recognised, but the underlying shared innards working does the same, and the result from that world be, … well a bit difficult to show, but something pseudo like ,
"
_"
That is effectively the required string to present in VBA on the RHS to put those two Characters in a cell.
' 2b(ii) vbCr & vbLf
What perhaps the last section, ' 2b(i) , has told us, or as interpreted to be saying , is that the two VBA things, vbCr & vbLf return in a string construct in VBA "NewLineCharacters" , so that will be fine to apply to , (put in ) , a cell to on the RHS of a code line like pseudo ,
Range("xxx") = "NewLineCharacters"
, so the same as
Range("xxx") = vbCr & vbLf
, which will effectively put in two "invisible" characters, (although if you fiddle with the cell or formula bar format and size, select the cell or formula bar and move around with the arrow keys, then you may see the cursor moving giving the indication of something there )
So here we go, very similar to ' 2a except we consider that rather than having available a recognised Excel syntax/Name CHAR(13)&CHAR(10) we have something which we can consider pseudo like an xy or a or b literal character(s) , and that is pseudo like NewLineCharacters
The Characters of a and our pseudo NewLineCharacters and b are not recognised Excel syntax/names
VBA wants to see the following string in order to put the formula = "a" & "NewLineCharacters" & "b" into the cell
"=""a""&""NewLineCharacters""&""b"""
So producing all those bits from Evaluate(" ") requires carefully producing each of those Characters
Code:"""" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" & "NewLineCharacters" & """" & """" & "&" & """" & """" & "b" & """" & """" & """"Code:"""" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" & vbCr & vbLf & """" & """" & "&" & """" & """" & "b" & """" & """" & """"
What finally is returned from the RHS and seen by VBA and then put in the cell is, as we would physically write it, ( if we could, - remember we have a pseudo characters, NewLineCharacters
="a"&"NewLineCharacters"&"b"
A final simplification can be made to the evaluate string by removing all the " & " bits
"""=""""a""""&""""" & vbCr & vbLf & """""&""""b"""""""
or
"=""=""""a""""&""""" & vbCr & vbLf & """""&""""b"""""""
One last thing interesting to compare with the first formula section. ' 2a , will be the Debug.Print of the string to be used in Evaluate(" ") strEval , and the formula finally put in the cell and seen in the formula bar
"=""a""&""
""&""b"""
="a"&"
"&"b"
A subtle difference perhaps, is that we are not passing a Excel known name / syntax that will do the new line, but rather perhaps the actual characters. Not sure of the significance if that, if any, but there may be some hidden revaluations in it at some later date….
(** Toleranceual redundancy , akin to adding the obstruction in a path of an open door, that door being the one closed on a cell and opened by the inclusion of an = at the first textual position thereof . )
Reply With Quote
Bookmarks