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

[DocAElstein]

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

---

Quotes in VBA

In Excel spreadsheets, within a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax. That is so that we can combine in a cell text and the result of a formula.
Example, type in a cell this ="Result is " & 1+1 then you will still see that in the formula bar, but in the cell you would see Result is 2
The & is just the VBA syntax for "sticking" things together, so this formula, for example, would give the same result in the cell ="Res" & "ult is " & 1+1
It follows, perhaps, that we can put plain text in a cell with a formula, as an alternative to simply putting the text in, for example , typing ="Hello" in a cell will result in that in the formula bar, but in the cell we will see Hello


Quotes in VBA
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see.
Working in VBA, ( or in a spreadsheet cell), to produce a string that we want to show a quote, becomes problematic. It is not clear if anybody really knows how and why we can get over this problem. There is no real documentation, although you might find the simple rule or explanation along the lines of…. Strings in VBA are delimited with quotation marks. If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string….
Well, that may be a correct statement, but I am not sure if that is a good explanation and whether it works by design or accident.
For me the following ideas helps to remember and possibly have an explanation or at least a theory that can explain the results and behaviour: It’s not too distant or different to that statement, it is an idea that helps me to remember, and possibly an
explanation:
The syntax will not allow a single quote within a quote pair, as the complier requires a pair. The syntax of VBA coding would also understandably find these strange.
__________ = "___ "____"___ "
__________ = "xyz"____"xyz"
But consider what might happen when we bring the inner quote pair together
__________ = "xyz""xyz"
I am thinking that we may have some sort of strange dynamic equilibrium situation, whereby simultaneously the quote pair syntax is satisfied and we have two joined pairs of text, ( "intimately joined"), which VBA sees on the compile as something like this
xy"xyz""
xyzxy""xyz"
, but because of this intimacy of the join, the position of the two inner quotes appears in the same place, meaning that the string is joined,- a quote may be defined twice, but in the same place, resulting in the final string as seen by VBA as
xyzxyz"xyz
Another way perhaps for thinking about is that they are actually a bit further than intimately joined, a character is entwined. By definition, the actual final literal string starts or stops on the other side of the quote, either after or before the quote. If you look at some of those coloured sketches you can perhaps imagine that the stop and start of the two literal bits of literal text is in the same place, which further joins them by two quotes merged into one.


Using Debug.Print to aid in string construction
Even with a good understanding of quotes in VBA, it is very easy to make a mistake. This general rule can help:
Printing out the string you have attempted to construct in the immediate window, will show it as VBA sees it after compile, and shows what VBA, after compile, will be trying to put in a cell via a code line like
Let Range("C1") = The string you have constructed
So, that string shown in the immediate window must be syntaxly kosher for an Excel cell, in otherwords it must look like the string as you would manually write it in a cell. So for putting a formula in a cell, something like this would be OK
= "a" & "b"
, resulting in the value
ab
showing in the cell, ( and in the formula bar you will see = "a" & "b" , just as in the immediate window )
On the other hand, something like these showing for a Debug.Print of your constructed string, would likely error when used
= "a" & b"
= "a" & ""b"
= ""a"" & "b"


So finally, in ' 2a we have a string construction that we are happy with to put a formula in a cell which will be the Excel spreadsheet formula, ="a"&Char(10)&Char(13)&"b" , as indeed we see both in the formula Bar, and in the immediate window if using
Debug.Print "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
To briefly explain that final string construction in words
______ "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
The outer most quotes are those always required in VBA to signalise that we are giving literal text.
Within those outer quotes, in VBA coding we may build up the string from further string elements. (The string can also be built up from VBA things returning strings or numbers, as we will do in the next post. Here we are only using other string elements), The syntax of VBA will require further enclosing quotes for every further string elements, and furthermore, VBA syntax requires an ampersand symbol, _ & _ , to join these strings, thus
______ "__" & "¬¬¬¬____" & "asskasasaf"
In green is the literal text, as we would write it in a cell.
Excel spreadsheet syntax requires in the final written cell formula text ampersands, & , which will be here literal text, hence must be enclosed with VBA quote pairs, thus
"&"
Finally, the Excel spreadsheet syntax requires that a literal text such as the character a, must be enclosed in the cell in quotes, but as we discussed in quotes in VBA something like this would error , ""a"" , we require to effectively double quote where we want a final single quote in the final string , """a"""


The final value in the cell would be
a
b
We have there 4 characters, pseudo like, (and it’s a bit subjective/ arbitrary of me, how I show them here in this pseudo form, just for explanation purposes: )
a vbCr
vbLf b

Some final minor notes:

_ If we are in in non English Excel, as I am, we can type either of these in the Immediate window to get the English syntax.
? range("C1").Formula
Debug.Print range("C1").Formula
_ Once we have the final string, we can often simplify, mainly by removing some VBA joining bits, since they are redundant when linking only literal text – the text can simply appear together.
______ "=""a""&Char(13)&Char(10)&""b"""
However, from experience I would only do this, if ever, at a final stage, since the careful explicit constructions are usually easier to build up and debug. Indeed, with hindsight, I might separate all the final cell texts of "a" and "b", as shown within the VBA construction, into the 3 characters of a quote, an a , and a final quote, just so as to make them clearly correspond to the explanations given of the quotes in VBA
______ "=" & """" & "a" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & "b" & """" & ""
In words, just as you would write in the cell ,
=
and
a quote "
and
a
and
a quote "
and
a ampersand &
and
a quote "
and
the two joined text (functions), Char(13)&Char(10)
and
a ampersand &
and
a quote "
and
b
and
a quote "


https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg
= a &CHAR(13)&CHAR(10)& b.JPG

[DocAElstein]

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

---

Problems with vbCr & vbLf in formulas and Evaluate(" ")

Review/ Introduction of what we are doing
In this post we will consider the first parts of the demo coding from the last post, or rather Sub CarriageReturnLineFeedExcelVBAText()
This will take us up to the point of the issue of vbCr & vbLf in formulas and Evaluate(" "), and so serve as an introduction and review of relevant concepts required to be understood before tacking the issue one step further into formulas

Rem 1 Puts stuff in a cell, fairly straight forward,
Range("B3") = "a" & vbCr & vbLf & "b"
( but even that is not so well understood. I understand it better than anyone at the moment. https://www.excelfox.com/forum/showt...-Value-Anomaly ). We can best say that the simplest syntax of the LHS, as we have there, says "put in the cell, what is on the RHS, like wot we might do via writing something in a spreadsheet cell and then hitting Enter". That tends to imply putting text, (and / or numbers) in a cell. But I think if we want to learn quicker and better Excel and VBA, then it is advisable not to think like that too much, and rather to leave it a bit open in the mind at "put in the cell, what is on the RHS, as example, as you might do via writing something in and then hitting Enter". Best not tie the thinking down too much on that one, or further revelations I give may not get so easily in the brain )
As far as the RHS is concerned, it is what we put in the cell : In Excel spreadsheets, we can simply write in text
In a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax.
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see
But already, we perhaps need to think a bit more carefully about exactly what is happening: VBA made some determination of what "a" & vbCr & vbLf & "b" is. It decided it was a 4 character piece of text. In VBA we represent that something like a vbCr vbLf b, but inside the whatever calculated/ evaluated thing, (bucket, section of pipe or bit of text string, etc) is: The character a; the character feed character; the line feed character; and the character b
' 1b Range("C3") = "a" & Evaluate("Char(13)&Char(10)") & "b"
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may be equivalent. The Evaluate(" ") is presumably returning into VBA what VBA also sees in vbCr & vbLf

' 1c(i) Range("D3") = "a" & Evaluate(vbCr & vbLf) & "b"
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may not be equivalent

' 1c(ii) Range("E3") = "a" & Evaluate("""" & vbCr & vbLf & """") & "b"
This is our first dealing with the issue this Post and the next few Posts is about: We seem to have got over the problem of ' 1c(i)
The issue of this Post and the next few Posts is about that I am trying to explain that.

But first we will do some similar experiments, putting a formula in, ( but that also requires some careful consideration of Quotes in VBA , ( or an Excel cell, for that matter )

But before we go let’s have a Results Summary Conclusion attempt
https://i.postimg.cc/pddjNp40/Sub-Ca...el-VBAText.jpg
Sub CarriageReturnLineFeedExcelVBAText().jpg


Rem 1d(i) Some conclusions
Perhaps it’s not so unexpected that Evaluate(vbCr & vbLf) gives us problems. Lets say Evaluate() looks for a valid Excel syntax, or perhaps let say Evaluate() looks for a valid Excel name initially, failing that it does the other sort of evaluation based on adding a = at the start. We can consider vbCr and vbLf as variables containing text characters. As such they are not much different from a x and a y
Now Evaluate(" ") was designed to take a string to allow us to build up a final text within the " " __ I think that is put in the cell , and if that is not recognised, then an = is put in front of it and that is tried. Unless, in this example, xy is recognised, such as for a named range, only there after it puts an = at the start. In a cell =xy wont work. ="xy" will work. The " " is a VBA string. For that to give me like in the returned Debug.Print string a leading quote, for example, I need to add a """"

Rem 1d(ii) Some advanced stab in the dark conclusions (Actually with a bit of hindsight from later)
Maybe….
It is likely that if you have got as far as reading this, then you will likely be familiar with the Range(" ")
There are some similarities with Evaluate(" ") and this more familiar Range(" ") _ So that can be a good start point.
These both return something to VBA when the text inside the " " is some recognised Excel spreadsheet syntax convention thing. I suggest there is some sort of flexible/ dynamic = feature wired in which may be used after some attempt without it. Some extra check is then perhaps added to Range(" ") to ensure that a range object is returned. Assuming this idea is correct, then Evaluate(" ") is the same but without this restriction.
( For Range(" ") this can explain the interesting feature we tripped over here , since a formula is allowed, but only if it returns a range object reference, in other words a name recognised in Excel spreadsheet syntax convention. This last bit sets the limitation of Range(" ")
However, I think the Evaluate(" ") does not share this limitation, and so allows a value, text or numeric to be returned).
Perhaps the Evaluate(" ") can be regarded as doing this pseudo coding
_ If the text is some recognised name or excel reference , Then Return the object, Expose it, run it, or some similar action doing something, Exit
Put a = in front of it.
_ If with the = it is not recognised by Excel spreadsheet syntax convention Then Error
_ If it is a closed workbook reference, then error, since it allows Alan to do things better than most complicated data base stuff, and that’s embarrassing , Error Exit
Return anything that a Variant variable will return.

Evaluate Formula Value)
We are not quite finished.

Evaluate Formula Value(s)
For the case of Evaluate(" ") effectively doing a formula in the " " we end up effective doing an Excel type evaluation, but we are not tied to a worksheet, so we are not necessarily restricted to a single values. But we do end up with value(s) in the range in the LHS. We do the same calculation as if we ended up with a formula in the cell. But it’s a false statement, often given when explaining what Evaluate(" ") is doing, to say it fundamentally does a mathematical calculation, gets a value(s) or fundamentally "evaluates". It can "do formulas"

Ref
https://eileenslounge.com/viewtopic....280997#p280997
https://www.myonlinetraininghub.com/...#comment-84892