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
Reply With Quote
Bookmarks