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 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

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

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

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

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  gt  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   amp  Replace ActiveCell Formula  Chr 34   Chr 34   amp  Chr 34    amp  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

[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