Hi
The problem here is that the quotes and the ampersands have double purposes in the code.
A while ago I had to construct dynamic SQL statements to fire into a SQL Server database and sometimes these statements were spectacularly long. Counting quote characters was impossible.
Code like this simplifies the task and becomes more readable :-
Sub Test()
Dim sLeftPart As String
sLeftPart = "Ripe "
Range("b1").Formula = "=" & InQuotes(sLeftPart) & "& a1 &" & InQuotes(" Cheap")
End Sub
Function InQuotes(sWord As String) As String
Const QUOTE = """"
InQuotes = QUOTE & sWord & QUOTE
End Function
By the way, putting the string into a variable and displaying it in the Debug window using Debug.Print is more productive than using the MsgBox function as you can see the history of your attempts at getting it right and also use the Copy and Paste functions.
Also I did not indent here because I sometimes seem to introduce funny A characters instead of the space characters. Any ideas ?
Derek +++
>________________________________
> From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 28 March 2012, 13:43
>Subject: Re: [ExcelVBA] Using Literals in Formulas
>
>
>
>> ... 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
>
>
>
>
>
[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
----------------------------------
Tidak ada komentar:
Posting Komentar