Excel VBA Formula Inside a String

Question

Trying to put a formula inside a string and can't get it working. Can someone help me turn this into a string? I know I need to do ""&"" in places, I just don't know where....

Dim form As String
form = =IFERROR(GETPIVOTDATA("[Measures].[Sum of Amount]",Pivot!$A$3,"[qAllData].[Year]","[qAllData].[Year].&[2.016E3]","[qAllData].[Month Name]","[qAllData].[Month Name].&["&$B9&"]","[qAllData].[PRODUCT_CODE]","[qAllData].[PRODUCT_CODE].&["&C$3&"]","[qAllData].[Product Name]","[qAllData].[Product Name].&["&C$4&"]")-C8-C7-C6-C5,0)

Do you want it as a string with the references in there, so it'd look like "=IFERROR(GETPIVOTDATA(...,Pivot!$A$3,..." or do you want that Pivot!$A$3 to actually be the text in that cell? — BruceWayne
– BruceWayne, Commented Oct 6, 2016 at 14:12
What do you want the formula to look like in the spreadsheet? — John Coleman
– John Coleman, Commented Oct 6, 2016 at 14:12
Write the desired formula on the sheet itself to make sure it works and post the working formula here with an explanation of which values you want swapped out with variables. The whole formula must be in quotes with the variables outside those quotes concatenated with &. — Scott Craner
– Scott Craner, Commented Oct 6, 2016 at 14:12
@JohnColeman that's what I meant, that it should be = "=... — Shai Rado
– Shai Rado, Commented Oct 6, 2016 at 14:15

John Coleman · Accepted Answer · 2016-10-06 14:26:18Z

2

If your formual is verbatim what you have in the spreadsheet but you want to insert it with VBA then just:

Dim form As String
form = "=IFERROR(GETPIVOTDATA(""[Measures].[Sum of Amount]"",Pivot!$A$3,""[qAllData].[Year]"",""[qAllData].[Year].&[2.016E3]"",""[qAllData].[Month Name]"",""[qAllData].[Month Name].&[""&$B9&""]"",""[qAllData].[PRODUCT_CODE]"",""[qAllData].[PRODUCT_CODE].&[""&C$3&""]"",""[qAllData].[Product Name]"",""[qAllData].[Product Name].&[""&C$4&""]"")-C8-C7-C6-C5,0)"

This simply replaced each " by "" and wrapped the whole thing in quotes. VBA accepts it as a valid formula, though I am not sure that this is what you really want.

answered Oct 6, 2016 at 14:26

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Victor Moraes · Accepted Answer · 2016-10-06 14:27:32Z

1

If it is a working formula and you just want it in a variable as a String, all you need to do is start and end it with double quotes (") and then double all inner quotes (""), like so:

form = "=IFERROR(GETPIVOTDATA(""[Measures].[Sum of Amount]"",Pivot!$A$3,""[qAllData].[Year]"",""[qAllData].[Year].&[2.016E3]"",""[qAllData].[Month Name]"",""[qAllData].[Month Name].&[""&$B9&""]"",""[qAllData].[PRODUCT_CODE]"",""[qAllData].[PRODUCT_CODE].&[""&C$3&""]"",""[qAllData].[Product Name]"",""[qAllData].[Product Name].&[""&C$4&""]"")-C8-C7-C6-C5,0)"

answered Oct 6, 2016 at 14:27

Victor Moraes

9721 gold badge11 silver badges28 bronze badges

Collectives™ on Stack Overflow

Excel VBA Formula Inside a String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related