Asked by:
Checking if a string contains a valid Excel Formula in enus format
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 enus 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 enus formula.
Is there a way to achive this ?
tia
Hendrik Schmieder
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

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.
Hendrik
