How to generate formulas containing quotes with VBA

Started by Yacine, September 09, 2017, 08:23:57 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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:
=SETF(GetRef(EventDrop),"CALLTHIS("&CHAR(34)&Prop.Name&CHAR(34)&","&CHAR(34)&Prop.Module&CHAR(34)&")")

In VBA you would normally write something like:
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.

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
Yacine

wapperdude

Brilliant idea!  Never considered using text editor.

Wapperdude
Visio 2019 Pro