excelvbaexcel-2007

How to check if VBA formula is correct

I'm creating a VB macro that converts string values like "1+1" to formulas.

   Cells(1, 1).Formula = "=" & Cells(1, 1).Value

But if Value string can't be calculated i have Run-time Error '1004'.

How can i be sure that string would convert to formula successfully?

Solution

  • First construct the formula and then use the Evaluate method on it. If it doesn't return an error then you can add it to the cell.

    Sub tester()
    Dim f As String

    f = "1+1"
    Debug.Print f, IIf(FormulaOK(f), "OK", "not valid")

    f = "1blah1"
    Debug.Print f, IIf(FormulaOK(f), "OK", "not valid")

End Sub

Function FormulaOK(f As String) As Boolean
    FormulaOK = Not IsError(Application.Evaluate(f))
End Function