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

**DocAElstein** · 03-05-2024, 01:59 AM

saafsfaf

**DocAElstein** · 03-06-2024, 02:18 AM

Later

**DocAElstein** · 03-06-2024, 02:18 AM

Later

**DocAElstein** · 03-09-2024, 10:10 PM

Code:

Option Explicit   '   https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24054&viewfull=1#post24054
Sub CarriageReturnLineFeedExcelVBA() '
On Error GoTo Bed
Rem 0                     A few things to make things in the demo look closer to what they are
 Call TidyCellView   '    When experimenting it may be a good idea to occaisionally do this again, as Excel occaisionally may change things if it geusses incorrrectly what we want to see
Debug.Print
Rem 1  Simple Text to put in a cell
                                                            Debug.Print "Rem 1a": Let Range("B1") = "Rem 1" & vbLf & """" & "a" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & "b" & """"   ' I get this from the Immdiszr eindow after I wrote it in   https://i.postimg.cc/3rSpmQ94/Use-Wtcha-Got-in-Immediate-window-to-get-tricky-cell-text-in-VBA-syntax-form.jpg
                                                                                  Let Range("B1").Characters(Start:=1, Length:=6).Font.Color = -11489280: Let Range("B1").Characters(Start:=7, Length:=23).Font.ColorIndex = xlAutomatic
       Debug.Print "a" & vbCr & vbLf & "b" '           shows           a
'                                                                      b
       Debug.Print "a" & Chr(13) & Chr(10) & "b" '     shows           a
'                                                                      b
 Let Range("B3") = "a" & vbCr & vbLf & "b" '           shows in cell   a
'                                                                      b
 Let Range("B4") = Evaluate("a" & vbCr & vbLf & "b") ' In cell  #WERT!  -  it appears to be returning  Error 2015
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A1").Value) '        "a" & vbCr & vbLf & "b"
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("a" & vbCr & vbLf & "b") ' "a" & vbCr & vbLf & "b"



Stop: Call TidyCellView: Debug.Print
' LHS    https://www.excelfox.com/forum/showthread.php/2956-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
 
' 1b
                                                                    Debug.Print "Rem 1b": Let Range("C1") = "Rem 1b" & vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & """" & "Char" & "(" & "13" & ")" & "&" & "Char" & "(" & "10" & ")" & """" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                    Let Range("C1").Characters(Start:=1, Length:=7).Font.Color = -11489280: Let Range("C1").Characters(Start:=8, Length:=41).Font.ColorIndex = xlAutomatic
       Debug.Print "a" & Evaluate("Char(13)&Char(10)") & "b" ' shows           a
'                                                                              b
 Let Range("C3") = "a" & Evaluate("Char(13)&Char(10)") & "b" ' shows in cell   a
'                                                                              b
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B1").Value)      '           "a" & vbCr & vbLf & "b"
 Let Range("C4") = Evaluate("a" & Evaluate("Char(13)&Char(10)") & "b")  ' In cell  #WERT!  -  it appears to be returning  Error 2015
Stop: Call TidyCellView: Debug.Print

' 1c(i)                             Note    Evaluate(vbCr & vbLf)   in watch gives     gives    Watch :   : Evaluate(vbCr & vbLf) : Fehler 2015 : Variant/Error
                                                                    Debug.Print "Rem 1c(i)": Let Range("D1") = "Rem 1c" & _
                                                                    vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & "vbCr " & "&" & " vbLf" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                                             Let Range("D1").Characters(Start:=1, Length:=7).Font.Color = -11489280: Let Range("D1").Characters(Start:=8, Length:=33).Font.ColorIndex = xlAutomatic
       Debug.Print "a" & Evaluate(vbCr & vbLf) & "b" ' Fatal                        Type Mismatch Error
 Let Range("D3") = "a" & Evaluate(vbCr & vbLf) & "b" ' Fatal                        Type Mismatch Error
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("a" & Evaluate(vbCr & vbLf) & "b") 'Fatal Type Mismatch Error
 Let Range("D4") = Evaluate("a" & Evaluate(vbCr & vbLf) & "b") ' Fatal      Type Mismatch Error
Stop: Call TidyCellView: Debug.Print

' 1c(ii)                            Note    Evaluate("""" & vbCr & vbLf & """")  Watch :   : Evaluate("""" & vbCr & vbLf & """") : "
                            '                                                                                                       " : Variant/String      if copied here, but looks like     "  "     in the watch window       https://i.postimg.cc/L6C24trX/Warch-Window-dont-display-a-line-break.jpg
                                                                    Debug.Print "Rem 1c(ii)": Let Range("E1") = "Rem 1c(ii)" & _
                                                                    vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & """" & """" & """" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & """" & """" & """" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                                              Let Range("E1").Characters(Start:=1, Length:=11).Font.Color = -11489280: Let Range("E1").Characters(Start:=12, Length:=47).Font.ColorIndex = xlAutomatic
       Debug.Print "a" & Evaluate("""" & vbCr & vbLf & """") & "b" ' shows          a
'                                                                                   b
 Let Range("E3") = "a" & Evaluate("""" & vbCr & vbLf & """") & "b" ' shows in cell  a
'                                                                                   b
 Let Range("E4") = Evaluate("a" & Evaluate("""" & vbCr & vbLf & """") & "b") ' In cell  #WERT!  -  it appears to be returning  Error 2015

Debug.Print Len("""" & vbCr & vbLf & """")             '                            4
Debug.Print Len(Evaluate("""" & vbCr & vbLf & """"))   '                            2
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B1").Value)      '                "a" & vbCr & vbLf & "b"
Stop: Call TidyCellView: Debug.Print
Debug.Print
Stop: Stop: Stop

' 1d Some conclusions
                                                                    Debug.Print "Rem 1d"
Debug.Print
Debug.Print
Debug.Print

Rem 2 Put formula in Cell

**DocAElstein** · 03-09-2024, 10:11 PM

this is post
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. 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 Tackling the issue

Rem 1 Puts stuff in a cell, fairly straight forward, ( 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: 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.
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

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

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

' 1c(ii)
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/4dNrgdff/Rem-1-...vided-by-b.jpg
Rem 1 Put in cell text like a divided by b.JPG

Rem 1d Some conclusions
Maybe….
There are some similarities with Evaluate(" ") and the more familiar Range(" ")
These both return an object where the text inside the " " is recognised Excel spreadsheet syntax convention.
For Range(" ") it does not go much further than that.
There continues to be some shared features. For example, I suggest there is some wired in feature that allows some attempt to:
include, if not there;
, or to remove, if there
,an =
I suggest this is done to see if that makes the text something acceptable in Excel spreadsheet syntax convention.
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
_____ 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
_____ Else Return the object, run it, or some similar action doing something, Exit
_ Else
__ put a = in front of it and see if that is somehow recognised by Excel spreadsheet syntax convention, and allow it to return anything that a Variant variable will return. Exit

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

**DocAElstein** · 03-13-2024, 04:13 PM

Quotes in VBA

Rem 2 Puts formula in cell(Quotes in VBA )
' 2a
At this point we see for the first time the confusing world of quotes in VBA, and Excel in general.
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.

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 rile 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

So we have done the background revision and can move on in the next post to the problem / issues with vbCr & vbLf in formulas and Evaluate(" ")