Entering a function into vba using UserForm

[cfolsom]

i am using a userform to try and enter a function like (x^2-1) into a text box. I am trying to get my vba program to recognize this as a function so that i can evaluate the function at a specific value that is also entered in a text box in the UserForm. I am kind of new to VBA so any help would be appreciated.

 

[Tom Urtis]

Well, you'd need to identify "x" in some way, probably as a numeric variable.

Let's say you have your posted expression of
x^2-1
in a textbox named TextBox1 on your userform.

You'll of course need to assign some association between the "x" and a numeric value, so for demonstration purposes, let's define "x" as 8 by using a Double type variable, with this example that evaluates the expression by clicking a command button named CommandButton1.

Private Sub CommandButton1_Click()
Dim xVal#, xString$
xVal = 8
xString = WorksheetFunction.Substitute(TextBox1.Text, "x", xVal)
MsgBox Evaluate(xString), , "Answer:"
End Sub

The answer in the message box is 63 because we assigned 8 to x, therefore
x^2-1
equals
8^2-1
equals
64-1
equals
63

 

[Mala]

There would be a problem construing a function like:
x²+8x-4

 

[Tom Urtis]

If you are directing that comment at me, I'd contend there is still no problem because the workbook author would know to accept or not accept that expression, and if accepted, allow for the superscript to be represented in a recognizable way in the textbox entry. This is actually a simple problem solved as I exampled, made simpler by the workbook author defining what is OK and not OK and pre-coding the solution to fit how they allow the expression to be entered.

 

[Mala]

My apologies, Tom!!
The solution is perfect - I misunderstood grossly.

 

[Mala]

Based on Tom's excellent solution here is a small 'bells and whistles' suggestion:

Let's say the userform has two textboxes TBFunc and TBValue for the function and value respectively and a label LblResult to display the result.

Use something like (code to be pasted in the code module for the user form):

Private Sub TBFunc_AfterUpdate()
  EvaluateFunction
End Sub
 
Private Sub TBValue_AfterUpdate()
  EvaluateFunction
End Sub
 
Private Sub EvaluateFunction()
  LblResult = ""
  xString = WorksheetFunction.Substitute(TBFunc.Text, "x", TBValue)
  On Error Resume Next
  LblResult.Caption = Evaluate(xString)
  On Error GoTo 0
End Sub

 

[cfolsom]

That worked great, thank you so much for the quick reply and the help, from both of you.