Author Topic: How to generate formulas containing quotes with VBA  (Read 4258 times)

0 Members and 1 Guest are viewing this topic.

Yacine

  • Hero Member
  • *****
  • Posts: 2923
How to generate formulas containing quotes with VBA
« on: September 09, 2017, 03:23:57 AM »
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
« Last Edit: September 09, 2017, 09:07:51 AM by Yacine »
Yacine

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4331
  • Ideas Visio-lized into solutions
Re: How to generate formulas containing quotes with VBA
« Reply #1 on: September 09, 2017, 08:51:24 AM »
Brilliant idea!  Never considered using text editor.

Wapperdude
Visio 2019 Pro