Checking if a string contains a valid Excel Formula in en-us format RRS feed

  • Question

  • Hello,

    we use C# and the Excel Interop to set formulas to ranges.

    We use for this the Formula Attribut of the range Attribut for this.

    Normally when the string isn't a valid en-us Formula then an exception is thrown.

    But if the string is something like

    string str = "=SUM({1;1;#VALUE})";

    then it could be that Excel crashes if we set the Formula Attribut of a Range to this string.

    Keep in mind that the Excel could be a nlv Excel like a german Excel.

    We want therefore first check if the string is a valid en-us formula.

    Is there a way to achive this ?


       Hendrik Schmieder

    Thursday, August 15, 2019 1:30 PM

All replies

  • In VBA:

    Sub TestMacro()
        Dim str As String
        Dim v As Variant
        str = "=SUM({1;1;1})"
        v = Application.Evaluate(str)
        MsgBox """" & str & """ is " & IIf(IsError(v), "bad", "ok")
        str = "=SUM({1;1;#VALUE})"
        v = Application.Evaluate(str)
        MsgBox """" & str & """ is " & IIf(IsError(v), "bad", "ok")
    End Sub

    Thursday, August 15, 2019 2:25 PM
  • I also thought of evaluete.

    But the drawback of this approach is that the formula is calculated two or three times.

    The evaluate function calculate the formula two times and when the formula is then set for the attribut Formula.

    The last calculation is the only one wanted.

    For this example this is not a big deal, but for our real life formula is a big deal,

    since the calculation implies calls to a Server wchich may be not located on the same machine.

    So I should have asked About possibilties without trying to calculate the formula.

    Nevertheless thanks for the answer.


    Friday, August 16, 2019 8:01 AM
  • There's also a second issue with this solution.

    If evaluate doesn't returns an error, then the formula is definitly valid.

    But evaluate could return an error even if the formula is valid.

    For example "=1/A1" in case that the value of A1 is 0.

    Friday, August 16, 2019 1:01 PM