Excel Vba: Double quotes in formula?

Question

I don't know the syntax for handling double quotation marks within a formula.

Here is my code:

ActiveCell.Formula = "=IF($W9="A",1,IF($W9="B",2, IF($W9="C",3,0))))"

Any help is greatly appreciated.

Answer 1

What I typically do is use the chr function in vba. You give the function the ASCII code for the desired character, this case 34, and vba fills it in for you.

ActiveCell.Formula = "=IF($W9=" & chr(34) & "A" & chr(34) & ",1,IF($W9=" & chr(34) & "B" & chr(34) & ",2, IF($W9=" & chr(34) & "C" & chr(34) & ", 3,0)))"

Cybernetic.nomad's answer works well, but in my experience (and mainly with Access) being super explicit gets the point across for the application, even though readability is practically non-existent.

I would posit that the best solution would to make the if statements in vba (instead of using excel functions) and return the value to the active cell. Then double quotes shouldn't be an issue at all in this case.

Answer 2

You need to double quotes for them to appear correctly in the formula

So: ActiveCell.Formula = "=IF($W9=""A"",1,IF($W9=""B"",2, IF($W9=""C"",3,0))))"

should give you the following formula in the ActiveCell:

=IF($W9="A",1,IF($W9="B",2, IF($W9="C",3,0))))

Answer 3

You need to change the formula to

ActiveCell.Formula = "=IF($W9=""A"",1,IF($W9=""B"",2, IF($W9=""C"",3,0)))"

So, first you need to double quote and in your case you also need to count the number of "(" and ")"