Check Textbox text is excel valid formula and use that function to calculate in VBA without storing any value in excel sheet

Question

I am trying to learn EXCEL VBA. One of the tasks I am stuck with is how to verify textbox1.text is valid excel formula. I tried Worksheetfunction.isfunction(userform1. text1.text) but it is giving error. My objective is to validate the userform1.text1.text = valid EXCEL function (say x^2 or sin(x)). If valid then use Evaluate function/method to calculate the value. Tried text as follows :

if worksheetfunction.isformula(userform1.text1.text) = true then A(i).value = worksheetfuntion.evaluate(userform1.text1.text,"x", B(i))

A and B are value arrays as varient I am not able to understand what the error is, any help?

Answer 1

a) worksheetfunction.isformula expects a Range as parameter - with other words, you can check if a cell contains a formula, but not if a string is a formula. You get a type mismatch error if you try (because a string and a range are not compatible types).

b) WorksheetFunction has no evaluate-method, you need to use Application.Evaluate (you can omit the Application)

c) evaluate result in an error (Error 2015) if the string you pass cannot be evaluated (because it contains an invalid formula). You can check if the result with the function IsError. However, be aware that there might be cases where the formula itself is valid, but the result is an error (eg your formula is "A1/A2" and cell A2 contains 0, you get a Division by 0 error).

d) evaluate will evaluate the string with and without leading = while a formula in Excel always starts with the =.

Dim formula as String, checkValue As Variant 
formula = userform1.text1.text
checkValue = Application.Evaluate(formula)
If not IsError(checkValue) Then 
    A(i).value = checkValue
Else
   MsgBox formula & " is not a valid formula"
End If