Inserting a formula using Excel VBA

问题:

I am needing to insert a formula in using vba coding.

at the moment the line of code is

=IF(
    AND(
        Compliance="No",
        OR(
            Delivery <> "",
            C31 <> ""
        )
    ),
    "Please Delete Compliance Data",
    IF(
        AND(
            E11="",
            E13="",
            E23="",
            E25="",
            E26="",
            E28="",
            E30="",
            E31=""),
        VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),
        "Please complete form / SC request"
    )
)"

It seems to error out on the text portions inside the code.

Any help would be appreciated.

回答1:

Here are four ways to use quotation marks inside strings in VBA:

• Replace "" with Chr$(34). 34 is the ASCII code for quotation marks
• Use String(2,Chr$(34)) to make double quotes
• Use two double quotation marks for every one (per Tim's comment)
• Replace two double quotation marks with a different method, like ISBLANK for worksheet functions.

None of these is better. I usually use two double quotation marks to escape them. But sometimes I get so many quotation marks together that it's hard to read and maintain, so I'll use a different method. Here's an example:

Dim aFormula(1 To 5) As String

aFormula(1) = "=IF(AND(Compliance=""No"",OR(Delivery<>" & String(2, Chr$(34))
aFormula(2) = ",C31<>" & String(2, Chr$(34)) & ") ),"
aFormula(3) = Chr$(34) & "Please Delete Compliance Data" & Chr$(34)
aFormula(4) = ",IF(AND(ISBLANK(E11),ISBLANK(E13),ISBLANK(E23),ISBLANK(E25),ISBLANK(E26),ISBLANK(E28),ISBLANK(E30),ISBLANK(E31)),"
aFormula(5) = "VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),""Please complete form / SC request""))"

Sheet1.Range("R13").Formula = Join(aFormula, vbNullString)

回答2:

To empower you for the future I strongly recommend you do the following when you want to use functionality in Excel in your VBA code that you are not acquainted with yet.

Go to the developer menu and press the record macro button.

Now fill in a formula in a cell, Clear a cells content by pressing delete and clearing a cells content by going into the cell and deleting all the characters and press enter.

Now stop the macro. And go to the VBA window. See how the code is structured to accomplish these things.

Apply this also whenever you want to do other things to get a good idea of what you can use to get the results you want without having to browse around on the internet for ages!

Also watch the local variables in the locals window or Debug.Print the string you try to build to see hwat it looks so far