How do I put double quotes in a string in vba

Question

I want to insert an if statement in a cell through vba which includes double quotes   Here is my code   Worksheets  Sheet1   Range  A1   Value     IF Sheet1 B1 0    Sheet1 B1     Due to double quotes I am having issues with inserting the string  How do I handle double quotes

User · Answer

I have written a small routine which copies formula from a cell to clipboard which one can easily paste in Visual Basic Editor.

    Public Sub CopyExcelFormulaInVBAFormat()
        Dim strFormula As String
        Dim objDataObj As Object

        '\Check that single cell is selected!
       If Selection.Cells.Count > 1 Then
            MsgBox "Select single cell only!", vbCritical
            Exit Sub
        End If

        'Check if we are not on a blank cell!
       If Len(ActiveCell.Formula) = 0 Then
            MsgBox "No Formula To Copy!", vbCritical
            Exit Sub
        End If

        'Add quotes as required in VBE
       strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34)

        'This is ClsID of MSFORMS Data Object
       Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        objDataObj.SetText strFormula, 1
        objDataObj.PutInClipboard
        MsgBox "VBA Format formula copied to Clipboard!", vbInformation

        Set objDataObj = Nothing

    End Sub

It is originally posted on Chandoo.org forums' Vault Section.

User · Answer

I prefer the answer of tabSF   implementing the same to your answer  here below is my approach  Worksheets  Sheet1   Range  A1   Value     IF Sheet1 A1 0    amp  CHR 34   amp  CHR 34   amp    Sheet1 A1

User · Answer

Another work-around is to construct a string with a temporary substitute character   Then you can use REPLACE to change each temp character to the double quote   I use tilde as the temporary substitute character     Here is an example from a project I have been working on   This is a little utility routine to repair a very complicated formula if when the cell gets stepped on accidentally   It is a difficult formula to enter into a cell  but this little utility fixes it instantly   Sub RepairFormula   Dim FormulaString As String  FormulaString     MID CELL  filename   A 1  FIND     CELL  filename   A 1   1 FIND      CELL  filename   A 1  -FIND     CELL  filename   A 1  -1   FormulaString   Replace FormulaString  Chr 126   Chr 34    this replaces every instance of the tilde with a double quote  Range  WorkbookFileName   Formula   FormulaString   This is really just a simple programming trick  but it makes entering the formula in your VBA code pretty easy

User · Answer

I find the easiest way is to double up on the quotes to handle a quote   Worksheets  Sheet1   Range  A1   Formula    IF Sheet1 A1 0      Sheet1 A1      Some people like to use CHR 34     Worksheets  Sheet1   Range  A1   Formula    IF Sheet1 A1 0    amp  CHR 34   amp  CHR 34   amp    Sheet1 A1       Note  CHAR   is used as an Excel cell formula  e g  writing   CHAR 34   in a cell  but for VBA code you use the CHR   function

User · Answer

All double quotes inside double quotes which suround the string must be changed doubled  As example I had one of json file strings    delivery    Standard    In Vba Editor I changed it into     delivery      Standard     and everythig works correctly  If you have to insert a lot of similar strings  my proposal first  insert them all between      then with VBA editor replace   inside into     If you will do mistake  VBA editor shows this line in red and you will correct this error

[excel] How do I put double quotes in a string in vba?

Examples related to excel

Examples related to vba

Examples related to double-quotes