> ... but why is MyString1 surrounded by 3 " ...?
MyString1 isn't surrounded by quotes at all. MyString1 is surrounded by
ampersands.
> I see that the first ", before the =, matches the very last " after Cheap
Actually it doesn't. To break it up:
"= """
is a short quoted string containing an equals sign, then a space, then a
double quote. To get the double quote into the string, you double it up.
So the two quotes together become one quote, and the very first quote
matches the last quote to make it all a quoted string.
&
is string concatenation to link the above with the next bit.
MyString1
is my string variable. You called it Msg. By being away from any quotes,
it will be the value contained in the variable that will be concatenated
with the first quoted string.
&
is string concatenation to link all the above with the next bit.
""" & A1 & "" Cheap"""
is a quoted string (with the first and last quotes being what contains it.
This string contains: a double quote (which is doubled up to make it a
literal), space, ampersand, space, A, 1, space, ampersand, space, quote
(doubled), space, C, h, e, a, p, quote (doubled).
Clear as mud? :-)
"= """ & MyString1 & """ & A1 & "" Cheap"""
For a start, the first two ampersands are outside the quoted strings and are
therefore operators in the VBA statement. These cause the three separate
parts of all this to be concatenated together into a single string value
that can be put into the formula.
The last two ampersands are inside the second quoted string and therefore
become literal parts of the formula.
Perhaps I should "compile" it.
"= """ & MyString1 & """ & A1 & "" Cheap"""
is three parts being concatenated together into a string to go into a
formula. This concatenation is done by the first two ampersands. Let's say
MyString1 contains "Sour", then
"= """ & MyString1 & """ & A1 & "" Cheap"""
becomes
"= """ & "Sour" & """ & A1 & "" Cheap"""
and we can combine the three parts
"= ""Sour"" & A1 & "" Cheap"""
Now, if we get rid of the enclosing quotes, and un-double the doubled-up
quotes, we get the formula
= "Sour" & A1 & " Cheap"
It turns out that this is actually missing a space after Sour, so my
original should have been
"= """ & MyString1 & " "" & A1 & "" Cheap"""
===========================================
As a matter of programming style, you should avoid statements such as
Range("B1").Formula = "= """ & MyString1 & " "" & A1 & "" Cheap"""
Instead, you should do something like
Dim FormulaString as String
FormulaString = "= """ & MyString1 & " "" & A1 & "" Cheap"""
Call MsgBox (FormulaString)
Range("B1").Formula = FormulaString
When you run the code, you will get the message box telling you exactly what
your formula is going to be. This means that you can see whether it is a
legal formula or not, before you try to stuff it into a cell.
Then when it's all working correctly, just comment out the MsgBox call. Or
delete it, if you prefer.
Regards, Dave S
----- Original Message -----
From: "Michael Trombetta" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, March 28, 2012 1:03 PM
Subject: Re: [ExcelVBA] Using Literals in Formulas
David,
Thank you once again.
First, in simplifying my post I used S1 as the name of my string, but in my
code I actually used Msg, and as far as I can tell that was flagged as a
#NAME error. (I went through so many iterations of quotation marks to
eliminate syntax errors that I can't recall the statement I used that
generated that #NAME error.)
I don't understand your code:
Range("B1").Formula = "= """ & MyString1 & """ & A1 & "" Cheap"""
I see that the first ", before the =, matches the very last " after Cheap,
but why is MyString1 surrounded by 3 ", when Cheap needs only 2? And why is
MyString1 surrounded by &'s? In the literal version
Range("B1").Formula = "=""Ripe "" & A1 & "" Cheap"""
Ripe gets only 2 ", and no &'s.
As for why I want to use a string variable. Suppose I have a very long
string, like "Perfect Fruit Picked at the Peak of Freshness ". I thought it
would be neater to assign that string to a variable instead of coding it in
the statement.
Thanks for you help.
Mickey
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1424 / Virus Database: 2113/4898 - Release Date: 03/27/12
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar