Excel Escaping Single and double quotes

Question

I have a formula in each cell in excel where I need to edit. But I am having a hard time escaping the single quotes and double quotes using VBA code.

This is an example:

=+'F-222Alloc'!N2516+'F-222Alloc'!N2526

I need it to look like this

=+INDIRECT("'"&N14&"'!N2511")+INDIRECT("'"&N14&"'!N2526")

How do I use the REPLACE function properly?

Answer 1

I find the easiest is to define a variable that contains just the double quote - then use it like any other string. Makes the code much more readable. Example:

Dim dq As String, sq as string
dq = Chr(34) ' double quote as a variable
sq = Chr(39) ' apostrophe or single quote as variable
Dim sourceString As String
sourceString = "hello"
msgbox sq + sourceString + "! " + dq + "you" + dq + sq

With these two variables you can create any string you want - after that, replacing what you want with something else (that might contain a crazy sequence of "'"'"'"("!"'") for all I care) becomes trivial.

Some helpful rules can be found in this article