How to generate formulas containing quotes with VBA

[Yacine]

Generating with VBA a shapesheet formula containing a lot of quotes is difficult.
Say you need to get in a user cell the formula:

Code

=SETF(GetRef(EventDrop),"CALLTHIS("&CHAR(34)&Prop.Name&CHAR(34)&","&CHAR(34)&Prop.Module&CHAR(34)&")")

In VBA you would normally write something like:

Code

shp.cells("user.myRow").formulaU = "SETF(GetRef(EventDrop)," & chr(34) & "CALLTHIS(" & chr(34) & "&CHAR(34)&Prop.Name&CHAR(34)&" & chr(34) & "," & chr(34) & "&CHAR(34)&Prop.Module&CHAR(34)&" & chr(34) & ")" & chr(34) & ")"

Not only is the string almost impossible to write, but it is also impossible to read.

Here's how it can be done in an easier way:

1) write the formula in the shapesheet as you need it.
2) copy and paste in a text editor
3) replace all the quotes by apostrophes
4) copy and paste the so transformed string to the VBA IDE
5) use the replace function to avoid concatenating string parts within quotes and doing the replacement by yourselves.

Code

t= "=SETF(GetRef(EventDrop),'CALLTHIS('&CHAR(34)&Prop.Name&CHAR(34)&','&CHAR(34)&Prop.Module&CHAR(34)&')')"
t= replace(t,"'",chr(34))
shp.cells("user.myRow").formulaU = t

[wapperdude]

Brilliant idea!  Never considered using text editor.

Wapperdude