时间戳

首页 > 解决方案 > 将公式转换为字符串

vba - 将公式转换为字符串

问题描述

我在函数中输入了以下公式。 

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)

主要问题是诸如 minfee 之类的变量不会引用其实际值，而是显示实际的字符串变量名称。

例如： "... If(K2 <" & minfee & "," ... ) 而不是 "... If(K2 < 1) ..." ' 假设 minfee = 1

标签： vba

解决方案

在 VBA"中用作字符串文字的分隔符，如下所示：

Dim foo As String
foo = "some string"

如果您的字符串文字需要包含"双引号，则需要通过在字符串分隔符之间将它们加倍来对它们进行转义：

foo = """some string"""

打印上面的内容会产生"some string"，包括双引号。

因此，您确实需要删除前导和尾随双引号。

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

分解它，它是以下文字的串联：

  • "=IF(K2 = 100, ""#NA"", IF(K2 <"（注意，"#NA"IMO 是个坏主意。使用该NA()函数产生一个实际的工作表错误，而不是一个看起来像一个的字符串值）
  • ","
  • ",IF(K2<"
  • ",K2+ "
  • ",IF(K2<"
  • ",K2+ "
  • ",K2+ "
  • "))))"

这对我来说是正确的。

可以说，这种串联令人讨厌。自定义StringFormat函数可以通过抽象连接来帮助缓解这种情况：

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)

推荐阅读