CODEWITHSUNDEEP
vba
monkey1009
monkey1009

Reputation: 11

Converting formula to string

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

Upvotes: 0

Views: 50

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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)

Upvotes: 2

Related Questions