saafsfaf
saafsfaf
Last edited by DocAElstein; 05-09-2024 at 05:18 PM.
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 VBAIn 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
Last edited by DocAElstein; 05-16-2024 at 06:36 PM.
Later
Code:Option Explicit Sub CarriageReturnLineFeedExcelVBAText() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24054&viewfull=1#post24054 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 Evaluate("""" & vbCr & vbLf & """") ' This seems to take the cursor down 2 places, but as Debug.Print takes routinely the cursor down once, our bit must have took it down once 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 Let Range("E4") = "#WERT! #VALUE is Excel's way of saying, ""There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."" The error is very general, and it can be hard to find the exact cause of it" 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 """" & vbCr & vbLf & """" ' " ' " 'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("""" & vbCr & vbLf & """") ' 4 characters quote https://i.postimg.cc/QMPndxSn/4-characters-quote-vb-Cr-vb-Lf-quote.jpg https://postimg.cc/gwVKMpCv Debug.Print Debug.Print Exit Sub Bed: Debug.Print Err.Number Debug.Print Err.Description Resume Next End Sub Sub CarriageReturnLineFeedExcelVBAFormula()
Last edited by DocAElstein; 05-11-2024 at 11:37 PM.
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
Last edited by DocAElstein; 05-17-2024 at 05:30 PM.
Quotes in VBARem 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(" "), well almost
Last edited by DocAElstein; 05-12-2024 at 01:31 AM.
this is post #597
https://www.excelfox.com/forum/showt...ll=1#post24087
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24087&viewfull=1#post24087
https://www.excelfox.com/forum/showt...ge60#post24087
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24087
We are not quite finished
A Final Evaluate full. Text. But it does a formula,
You should be confused at this point. If you aren’t, then you are not normal. We are putting text in a cell. The final step is to complete it with Evaluate, for what reason I have long since forgotten myself.
A recap is perhaps useful:
We figured out a tricky syntax, and in doing so came close finally to conclusions leading to an understanding of Evaluate (" ") that probably no one has…... Office is an old program, in software age, and has had an incredible number of fixes. I doubt anyone at Microsoft has any idea of the entire suite's programming. I doubt anyone at Microsoft has a handle on the coding….
We stated this by looking at putting text in cells. We were also messing with Evaluate (" ")
We spoke about a flexible = that allows a formula to be done, and in a cell, if the result is to be out in a cell, then just the value will be put in.
Our final code line so far considered was this ..__.. and it errored , (as did all our Evaluate attempts)Code:Range("E4") = Evaluate("a" & Evaluate("""" & vbCr & vbLf & """") & "b") ' In Cell we see #WERT! - IT APPEARS TO BE RETURNING ERROR 2015
We had concentrated on the Evaluate("""" & vbCr & vbLf & """") stuff. But I think we can see clearly from all our discussions and in particular to the initial stab at some conclusions, that we need to have in the Put in the cell, Debug.Print seen string a quote pair around the a and the b
Let's say that again a bit differently: Currently we have what we could call the Evaluate(" or VBA code line required quotes around it. This would be like Excel trying to recognise a Name or meaning to
a
, and failing that, Evaluate(" ") tries to see if doing
=a
, gives soimethi8ng sensible. Unless you specifically have given that character, a , some meaning in Excel , that will error.
The oint that needs to be remembered over and over again is that in this situation, we can consider Evaluate(" ") to be trying to get what we finally want, via a formula in a cell, but we won’t fonally get a formula if its successful: We will get the actual final result. In other words, in this example a cell would want to see
="a"
So… what to do
And this is the crux actually, of what started this page in order to get vbCr & vbLf to do what we wanted: In that final Evaluate string we need to do something to make the a and the b be seen to be enclosed in quotes. That requires a double quote per quote you finally want to see.
Let’s do that in a separate coding, Sub CarriageReturnLineFeedExcelVBAEvaluateTextFormula( )
Code:Sub CarriageReturnLineFeedExcelVBAEvaluateTextFormula() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24087&viewfull=1#post24087 On Error GoTo Bed ' 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 ' 1f Finally Debug.Print "Rem 1f": Let Range("F1") = "Rem 1f" & vbLf & _ "Evaluate(" & """""""" & "a" & """""""" & ")" & " & " & " Evaluate" & "(" & """" & """" & """" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & """" & """" & """" & ")" & " " & " & " & "Evaluate(" & """""""" & "b" & """""""" & ")" Let Range("F1").Characters(Start:=1, Length:=6).Font.Color = -11489280: Let Range("F1").Characters(Start:=7, Length:=78).Font.ColorIndex = xlAutomatic Debug.Print Evaluate("""" & "a" & """") & Evaluate("""" & vbCr & vbLf & """") & Evaluate("""" & "b" & """") Let Range("F4") = Evaluate("""" & "a" & """") & Evaluate("""" & vbCr & vbLf & """") & Evaluate("""" & "b" & """") Debug.Print Debug.Print Exit Sub Bed: Debug.Print Err.Number Debug.Print Err.Description Resume Next End Sub
What have we done.
Let’s put into words what we have done, as it will help in the next post sections, when we do putting formulas into cells with Evaluate("")
We created a full string for VBA equivalent to
"a" & vbCr & vbLf & "b"
"a" was got from this Evaluate("""" & "a" & """")
vbCr & vbLf was got from this Evaluate("""" & vbCr & vbLf & """")
"b" was got from this Evaluate("""" & "b" & """")
But, and this is important,
The three Evaluates effectively did these three things:
It got the result as if we typed in a cell ="a" which resulted in a
It got the result as if we typed in a cell ="the character for a carriage return" & "the character for a carriage return" which resulted in a visible line break, or second line, or however you personally describe that
It got the result as if we typed in a cell ="b" which resulted in b
Evaluate got us the 3 results from a formula, as if I typed the formula in a cell in a cell
https://i.postimg.cc/vmSF6JBH/Column-F-results.jpg
Column F results.JPG
Last edited by DocAElstein; 05-13-2024 at 01:57 PM.
this is post #598
https://www.excelfox.com/forum/showt...ll=1#post24089
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089
https://www.excelfox.com/forum/showt...ge60#post24089
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24089
Rem 2 Put formula in cell (Sub CarriageReturnLineFeedExcelVBAFormula() )
Having forced myself into going back to the basics, and getting a more deeper understanding of Evaluate(" ") , the problems in some syntax in putting formulas into a cell , or finding text things via an excel formula in Evaluate(" ") , which caused the excursion of this page , is easy to do, and sure to get it, as long as we are patient and cautious.
So armed with some knowledge of constructing the necessary strings, and some initial attempt at a a Results Summary Conclusion attempt we move on to get the same value in a cell as previously, but from a formula, that is to say, taking Evaluate(" ") string constructs that one step further to actually put a formula in a cell …. pseudo
Put in the cell = a formula looking like , ="Hello"
, using
Put in the cell = Evaluate( string construct to actually give a formula, not the result from it )
Note , of course, you will still see the value result in the cell, but an indication that we actually put on a formula, rather than a value, should be seen in the formula bar, just as if we manually typed the formula in, ( assuming default Excel settings )
https://i.postimg.cc/9Mf54ycw/Formul...splay-text.jpg
Formula in cell, (to display text).JPG
An extra quote, or two, and stuff
Having battled out enough with the basics in the last few posts, it’s actually not so difficult from now on
Points to note
_1) literal text in a cell, needs to be put in initially, "in the first place" as it were, in quotes in the string construct for evaluate just to prevent Evaluate(" ") trying to interpret the text as some particular Excel thing. So far we did this explicitly like this
Evaluate("""" & "Mytext" & """")
, which you would have to do if your text was in a variable , - example if your text was in a string variable, strText, then you would need to do this
Evaluate("""" & strText & """")
, or we could consder the new line pair things we have been considering as variables, and as such also need to do like
Evaluate("""" & vbCr & vbLf & """")
For actual text, this would be the same
Evaluate("""Mytext""")
, but I would recommend always being explicit until maybe you have finished.
Based on the results and attempted explanations of Evaluate(" ") so far generally, and with a bit of hindsight again, I might have a go at a slightly modified explanation of Evaluate(" ")
If you or I type an = at the start of putting something in a cell, then that is a significant thing that calls into action deep Office innards coding which I expect is either the same, or in some way strongly related to, or very similar to, the coding that gets set off by Evaluate(" "). There will be similarities therefore in the result, where I am using the word result in its broadest sense here: Just one example of this similarity is that it could be that a text or number appearing in the cell would be that which would appear in a variable, Ex if you did something like this
Ex = Evaluate("What you put in a cell after typing in a = at the start")
But that is just part of the story, perhaps what is often given as the simplifies version of what Evaluate(" ") is about.
More fully, the string what VBA "sees" will need to be a syntax, or named thing, that Excel recognises, and its always very good practice when constructing the string in Evaluate, ( strEval, where strEval is like here, Evaluate(strEval) ) , to check what VBA sees via Debug.Print strEval
I expect there may be a priority to the order that things are done.
Calling something out
First, a reference to an object or something will have a varied effect. Maybe we could refer this to calling it out, like shouting at someone to call them to attention. It may make them do something or get them there, ready. We sometimes hear of "exposing an interface" having a similar effect to running it. I like the sound of calling it out
Do a formula to do something (even a formula!)
This is easy to get mislead and confused. If a string construct, what VBA finally "sees", can be recognised as a syntax that Excel would be happy with in a cell, A formula, then the result that would be obtained of that string with an = in front of it, in a cell, is what Evaluate(" ") will try to return. At a stretch of the imagination, this could be regarded as calling out an imaginary cell, by its name, its cell reference, where the imaginary cell has that formula in it. This should perhaps spark off someone smarter to get excited and tell me about kicking off an Excel4 macro somehow. Just now it’s a leap too far for my brain. But I think the idea is potentially sound
Having got to this level of understanding, maybe we can do close to anything , within reason, even … put a formula in a cell
So let’s have a go. Introduction to formula in a cell
Backwards working understand, to easier perhaps is this. (This is perhaps easier to understand, working backwards.)
For example , I want this formula to effectively be put in a cell ,
="Hello"
, which will mean that I see Hello in the cell, and in the formula bar I will see ="Hello"
If I was using VBA to put that formula in a cell, I would need to construct the VBA string context for that, which would be like
Range("A10") = "=""Hello"""
To achieve that from a code line like ,
Range("A10") = Evaluate("")
, then Evaluate("") will need to return "=""Hello"""
So far we have learnt that for returning "Hello" in VBA , the RHS == Evaluate("") , would need to look like this
Evaluate("""" & "Hello" & """")
In simplified terms, we can say that in that construction , the 4 quotes, """" is getting us a single quote in that "Hello" in VBA
So a simplified logic perhaps tells us we need a few more of the 4 quotes, """" bits, ( as well as an = somewhere )
Just looking carefully , and applying the simple logic tells us we want : a quote; a =; 2 quotes; the text Hello ; and finally 3 quotes.
So perhaps we want this VBA context string
Evaluate(VBA context string)
"""" & "=" & """" & """" & "Hello" & """" & """" & """"
This next simple coding shows the construction clearly breaking down each required string bit
Having got this far, at the final stage, we can simplify quite easily, if we wish to. The thing here to remember is that the & is only required toCode:Sub CallOutTextInCellFormula() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089 ' Effectively put ="Hello" in a cell , which will mean that I see in the cell Hello and in the formula bar ="Hello" Let Range("A10") = "=""Hello""" ' we need from Evaluate(" ") a quote, a =, 2 quotes, Hello , and finally 3 quotes ' " = " " Hello " " " Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """") End Sub
_ link or glue variables or other VBA things to each other
or _ to link or glue variables or other VBA things to text
It is not required to link or glue text to text.
This means that bits like " & " can be removed
Do that with our last example, and you end up with this rather impressive looking beautiful thing
"""=""""Hello"""""""
Just to confuse or impress a little further, we have noted previously the somewhat "flexible" = that Evaluate(" ") seems to posses. There is no exception here, so we could add that last bit of apparent mystical magicness to that last string construction
"=""=""""Hello"""""""
_._________________Code:Sub CallOutTextInCellFormula() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089 ' Effectively put ="Hello" in a cell , which will mean that I see in the cell Hello and in the formula bar ="Hello" Let Range("A10") = "=""Hello""" ' we need from Evaluate(" ") a quote, a =, 2 quotes, Hello , and finally 3 quotes ' " = " " Hello " " " Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """") ' ' simplified Let Range("A10") = Evaluate("""=""""Hello""""""") Let Range("A10") = Evaluate("=""=""""Hello""""""") End Sub
&
A small reminder about the & in string context both In VBA and Excel spreadsheet syntax.
As we seen already in a coupe of places ( https://www.excelfox.com/forum/showt...ll=1#post24083
https://www.excelfox.com/forum/showt...ll=1#post24050 ) we have this same "gluing together" thing in both Excel and VBA.
If the formula we are trying finally to put in a cell has in Excel a & , then that & will effectively be another bit of text to add.
Note also in the following example to get like a formula ="Hello"&" You" we cannot rely on Evaluate(" ") to somehow return us VBA connecting / gluing & bits , so we must do some simplification in such example cases.
Code:Sub CallOutTextInCellFormula() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089 ' Effectively put ="Hello" in a cell , which will mean that I see in the cell Hello and in the formula bar ="Hello" Let Range("A10") = "=""Hello""" ' we need from Evaluate(" ") : a quote; a =; 2 quotes; Hello ; and finally 3 quotes ' " = " " Hello " " " Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """") ' ' simplified Let Range("A10") = Evaluate("""=""""Hello""""""") Let Range("A10") = Evaluate("=""=""""Hello""""""") ' Put ="Hello"& "You" in a cell Let Range("A11") = "=""Hello""" & "&" & """ You""" ' Evaluate will not be ablk to return the two connecting/ gluing VBA things " & " so we will need to be at the simplified level Let Range("A11") = "=""Hello""&"" You""" ' we need from Evaluate(" ") : a quote; a =; 2 quotes; Hello ; 2 quotes ; the & ; 2 quotes ; You ; and finally 3 quotes Let Range("A11") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & "&" & """" & """" & " You" & """" & """" & """") ' simplified Let Range("A11") = Evaluate("""=""""Hello""""&"""" You""""""") End Sub
Last edited by DocAElstein; 05-16-2024 at 08:06 PM.
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"""
In other words, if I wanted to use VBA to put this formula in cell B10,
= "a" & CHAR(13) & CHAR(10) & "b"
, ( as an alternative to me physically typing this in ), then I would need a code line like
Range("B10") = "=""a""&CHAR(13)&CHAR(10)&""b"""
So producing all those bits on the RHS using Evaluate(" ") on the RHS requires carefully producing each of those 18 things from what we have learned is how they need to look like within the Evaluate(" ") string:-
With the previous knowledge, it’s fairly straight forward.Code:"""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR (13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"
Here saying it again. In words what we have done is: 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. – ( It would be needed to link string text to other things like VBA variables and functions and formulas, in which case it would have this sort of form , missing one of the quotes, __ "words " & VBA stuff & " other words" )
We then make the string for evaluate, strEval . We do this by consigöring each Character in turn of this string we need in VBA, "=""a""&CHAR(13)&CHAR(10)&""b""" – Let’s open that out just to make it easier to see all those 18 bits
For each of the 18 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….
Edit. Actually I have found a few remarkable things come out when including some of the "invisible" characters, as well as other VBA things, in the string construction. But based on my previous experience of revealing some of my lesser controversial discoveries I have serious decided to not openly discus them until/ if I become very rich and famous so that I can afford a small army of the best body guards. I am happy to say that here as I am sure most people won’t believe a word of it yet, so I am fairly safe.
(** 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 . )
Last edited by DocAElstein; 05-19-2024 at 03:17 PM.
this is post #600
https://www.excelfox.com/forum/showt...ll=1#post24133
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24133&viewfull=1#post24133
https://www.excelfox.com/forum/showt...ge60#post24133
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24133
Evaluate(" ")
This is often seen to be allowing Excel spreadsheet things to be done in VBA, and therefore a simplified explanation and some Blogs on Evaluate(" ") , the Evaluate method, will concentrate on this feature of it. That is not necessarily misleading, and is not a bad simplified start point.
After a lot of head breaking , I think I have a statement(s)/ ideas from which I am happy to expand and add flesh to in order to explain away both the accepted conventional things done with it, and to some extent the more advanced interesting and not always accepted unconventional things I have done, and also some more recent little discoveries, (which because of previous reactions I am never going to make public until when/if I become very rich and famous, so that I can afford a small army of the best body guards)
Here we go , it's like this:
This would be my entry in a simple dictionary, or the very first bit in an explanation:
___Evaluate _ (Callout an Excel object by referring thereto)
Call out is in the sense of bringing it to attention via some sort of reference string or name , and this is sometimes referred to as exposing an interface. A Run or a Call of something are a similar thing. We often experience that using a recognised text will make something happen . An Excel in built function and a User Defined Function, (UDF), can reasonably be considered to be similar things, even an Excel object, if Object is used in its very broadest sense, as is reasonably acceptable and certainly not conflicting with the somewhat bodged together form of apparent object orientated programming that VBA is. Don’t be confused that I went over to VBA there - what I am suggesting there is that the wrapper for the function coding has the name for referral to be Called of the routine name, which is recognised by Excel – in other words the name of a UDF is recognised by Excel, - as most of us will know, that is true of a Public Function in a normal code module, which is what a UDF is.
Last edited by DocAElstein; 05-20-2024 at 02:24 PM.
Posting Permissions
Reply With Quote
Bookmarks