How do I put double quotes in a string in vba?

user793468 picture user793468 · Jan 26, 2012 · Viewed 465.2k times · Source

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?

Answer

Brain2000 picture Brain2000 · Jan 26, 2012

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," & CHR(34) & CHR(34) & ",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.