Converting formula to string

Question

I have the following formula that I am inputting in a function.

dim minfee, feetier3, feetier4, feetier5, bpspread1, bpsread2, bpspread3 as double

call insformulaincell("=IF(K2 = 100, ""#NA"", IF(K2 <" & minfee & "," & feetier3 & ",IF(K2<" & feetier4 & ",K2+ " & bpspread1 & ",IF(K2<" & feetier5 & ",K2+ " & bpspread2 & ",K2+ " & bpspread3 & "))))")

'all the function does is paste the formula into a cell 
'How would I format the formula so that it can be stored as a single string?
'Ex:

dim sFormula as string 

sformula = ""=IF(K2 = 100, ""#NA"", IF(K2 <" & minfee & "," & feetier3 & ",IF(K2<" & feetier4 & ",K2+ " & bpspread1 & ",IF(K2<" & feetier5 & ",K2+ " & bpspread2 & ",K2+ " & bpspread3 & "))))""

call insformulaincell(sFormula)

The main issue is that the variables such as minfee would not reference its actual values but instead have the actual string variable name appear.

Ex: "... If(K2 <" & minfee & "," ... ) as opposed to "... If(K2 < 1) ..." ' assuming that minfee = 1

I tried that but it doesn't solve the main issue where the variable names no longer reference the variable values anymore — monkey1009, Jan 23 '19 at 17:18

score 2 · Answer 1 · answered Jan 23 '19 at 17:29

In VBA " serves as a delimiter for string literals, like this:

Dim foo As String
foo = "some string"

If your string literal needs to contain " double quotes, you need to escape them, by doubling them up between the string delimiters:

foo = """some string"""

Printing the above would yield "some string", including the double quotes.

So you do need to remove the leading & trailing double quotes.

sformula = "=IF(K2 = 100, ""#NA"", IF(K2 <" & minfee & "," & feetier3 & ",IF(K2<" & feetier4 & ",K2+ " & bpspread1 & ",IF(K2<" & feetier5 & ",K2+ " & bpspread2 & ",K2+ " & bpspread3 & "))))"

Breaking this down, it's a concatenation of the following literals:

"=IF(K2 = 100, ""#NA"", IF(K2 <" (note, "#NA" is a bad idea IMO. Use the NA() function to yield an actual worksheet error instead of a string value that looks like one)
","
",IF(K2<"
",K2+ "
",IF(K2<"
",K2+ "
",K2+ "
"))))"

Which looks right to me.

Arguably, such concatenation is annoyingly confusing. A custom StringFormat function can help mitigate this, by abstracting away the concatenations:

sFormula = StringFormat("=IF(K2=100, ""#NA"", IF(K2<{0},{1},IF(K2<{2},K2+{3},IF(K2<{4},K2+{5},K2+{6}", _
    minfee, feetier3, feetier4, bpspread1, feetier5, bpspread2, bpspread3)

Converting formula to string

1 Answers1