How to use a textbox data in a formula

[Hussein]

Hello,

Please i need help on how to use a user provided data through a textbox in a formula. For example: I developed a userform which request an equation from user:


The result of the equation is then used in a formula in my code to compute result

yn1 = Y + (X + Y) * D

My prob is how refer the result of the equation (X+y) which is now used as a value in the above formula.

Thanks.

 

[iferror]

Uhmm not sure i got this.... what is it that you want to do here?

Pls share something more

 

[sgmpatnaik]

@Hussein

Welcome To Chandoo.org Forum, Glad you are here

before post a question please read the Forum Rules from here and always try to post a sample workbook with desire request

http://chandoo.org/forum/forums/new-users-please-start-here.14/

now come to your point you want use the formula in TextBox then please try the below method
say you have 3 textbox 1,2,3 and you want to sum or what ever of TextBox1,2 in Textbox3 then we can use

Private Sub TextBox2_AfterUpdate()
TextBox3.Value = Format(CDbl(TextBox1.Value) + CDbl(TextBox2.Value), "#,##0.00")
End Sub

Hope it give some idea, other wise please post a sample workbook

Thanks

SP

 

[Hussein]

Thanks. How do i now use the value of TextBox_3 in the following:

For i = 1 To N
  yn1 = Y + (x + Y) * D
  x = x + D
  Y = yn1
  ActiveSheet.Cells(i + 10, 4) = x
  ActiveSheet.Cells(i + 10, 5) = Y
Next i

Thanks.

 

[NARAYANK991]

Hi Hussein ,

For your code to execute and return the proper result , you need initial values for the following variables :

Y , x and D

Where are the initial values for these variables ?

You say your textbox is accepting the equation , which means what is being entered by the user is a text string ; for example if the user enters x + y , this will be a text string.

Since you say that the user can only enter one of two possibilities ( either x + y , or x - y ) , what you can do is assign the textbox value to a string variable , say s. Within your code you can test for s and perform the correct action based on whether s contains the + character or the - character.

The point still remains , that for the macro to do anything useful , you need to initialize the 3 variables. This has nothing to do with what the user enters in the textbox.

Narayan

 

[Hussein]

Thanks a lot,

The initial values are also supplied by the user on the same userform page. For example, default values are X=0, Y=0 and D=0.001.
I have tried assigning the textbox value to a string variable but dint work. I have been getting a Run time error '13'.

Dim E as string, Y as Double, X as Double, D as Double

    E = equation.value
    Y = Yn.Text
    X = Xn.Text
    D = StepSize.Text
    N = NSteps.Text
For i = 1 To N
  yn1 = Y + (E) * D
  X = X + D
  Y = yn1
  ActiveSheet.Cells(i + 10, 4) = X
  ActiveSheet.Cells(i + 10, 5) = Y
Next i

Also, the equation the user inputs is not only X+/-Y but just a format. That is, it must consist of x and y plus and operator. e.g. 2x + 45y.

Thanks.

 

[sgmpatnaik]

@Hussein

already we told you to upload a sample file so Please upload with your exact requirement

Thanks

SP

 

[NARAYANK991]

Hi Hussein ,

Try this , after changing it to suit your actual variables :

Public Sub Calculate_from_Equation()
           Dim E As String, Val_Y As Double, Val_X As Double, D As Double

           E = LCase(InputBox("Enter the equation in the format Ax + By : "))        ' equation.Value
           x_coeff = Val(Left(E, 1))
           If x_coeff > 0 Then
              E = Replace(E, "x", "*x")
           End If
           
           y_coeff = Val(Mid(E, Application.WorksheetFunction.Find("y", E) - 1, 1))
           If y_coeff > 0 Then
              E = Replace(E, "y", "*y")
           End If
           
           Val_Y = 2                    ' Yn.Text
           Val_X = 1                    ' Xn.Text
           D = 5                        ' StepSize.Text
           N = 7                        ' NSteps.Text

           For i = 1 To N
               Val_E = Application.Evaluate(Replace(Replace(E, "x", Val_X, , , vbTextCompare), "y", Val_Y, , , vbTextCompare))
               
               yn_1 = Val_Y + Val_E * D
               Val_X = Val_X + D
               Val_Y = yn_1
               ActiveSheet.Cells(i + 10, 4) = Val_X
               ActiveSheet.Cells(i + 10, 5) = Val_Y
           Next i
End Sub

Copied from : http://www.mrexcel.com/forum/excel-...lications-function-evaluating-expression.html

Narayan

 

[Hussein]

I'm so grateful Narayan!. It worked when i modified it.
Thanks a lot.

 

[Hussein]

Hi Hussein ,

Try this , after changing it to suit your actual variables :

Public Sub Calculate_from_Equation()
           Dim E As String, Val_Y As Double, Val_X As Double, D As Double

           E = LCase(InputBox("Enter the equation in the format Ax + By : "))        ' equation.Value
           x_coeff = Val(Left(E, 1))
           If x_coeff > 0 Then
              E = Replace(E, "x", "*x")
           End If
          
           y_coeff = Val(Mid(E, Application.WorksheetFunction.Find("y", E) - 1, 1))
           If y_coeff > 0 Then
              E = Replace(E, "y", "*y")
           End If
          
           Val_Y = 2                    ' Yn.Text
           Val_X = 1                    ' Xn.Text
           D = 5                        ' StepSize.Text
           N = 7                        ' NSteps.Text

           For i = 1 To N
               Val_E = Application.Evaluate(Replace(Replace(E, "x", Val_X, , , vbTextCompare), "y", Val_Y, , , vbTextCompare))
              
               yn_1 = Val_Y + Val_E * D
               Val_X = Val_X + D
               Val_Y = yn_1
               ActiveSheet.Cells(i + 10, 4) = Val_X
               ActiveSheet.Cells(i + 10, 5) = Val_Y
           Next i
End Sub

Copied from : http://www.mrexcel.com/forum/excel-...lications-function-evaluating-expression.html

Narayan

But, how can i make it dynamic such that the equation is not necessarily of the format Ax + By. What if i want to make a user input any formula that is acceptable in Excel such that it may contain x or y variables or both.
Below is the modified code:

Dim Y As Double, yn1 As Double, X As Double, D As Double
  Dim N As Integer, E As String, Sol As String

E = Equation.Text  'LCase(InputBox("Enter the equation in the format Ax + By : "))
  x_coeff = Val(Left(E, 1))
If x_coeff > 0 Then
[INDENT]E = Replace(E, "x", "*x")[/INDENT]
End If
   
y_coeff = Val(Mid(E, Application.WorksheetFunction.Find("y", E) - 1, 1))
If y_coeff > 0 Then
[INDENT]E = Replace(E, "y", "*y")[/INDENT]
End If
Sol = Yexact.Text  'LCase(InputBox("Enter the equation in the format Ax + By : "))
x_coeff = Val(Left(Sol, 1))
If x_coeff > 0 Then
[INDENT]Sol = Replace(Sol, "x", "*x")[/INDENT]
End If
   
  y_coeff = Val(Mid(Sol, Application.WorksheetFunction.Find("y", Sol) - 1, 1))
  If y_coeff > 0 Then
  Sol = Replace(Sol, "y", "*y")
  End If
   
Val_Y = Yn.Value      ' Yn.Text
Val_X = Xn.Value      ' Xn.Text
D = StepSize.Value  ' StepSize.Text
N = NSteps.Value     ' NSteps.Text

For i = 1 To N
[INDENT]Val_E = Application.Evaluate(Replace(Replace(E, "x", Val_X, , , vbTextCompare), "y", Val_Y, , , vbTextCompare))
Val_Sol = Application.Evaluate(Replace(Replace(Sol, "x", Val_X, , , vbTextCompare), "y", Val_Y, , , vbTextCompare))

yn1 = Val_Y + Val_E * D
Error = Val_Sol - yn1
Val_X = Val_X + D
Val_Y = yn1
ActiveSheet.Cells(i + 10, 4) = Val_X
ActiveSheet.Cells(i + 10, 5) = Val_Y
ActiveSheet.Cells(i + 10, 6) = Sol
ActiveSheet.Cells(i + 10, 7) = Error[/INDENT]

Next i
UserForm1.Hide
End Sub

Thanks.

 

[NARAYANK991]

Hi Hussein ,

There are a few possibilities :

Ax + By + C , Ax + By - C , Ax - By + C , Ax - By - C , Ax + By , Ax - By , Ax + C , Ax - C , By + C , By - C

assuming that we are talking of only first-order equations ; if we go on to higher-order equations , what we need is a full-fledged equation parser !

You need to restrict the possibilities ; my coding skills do not extend to writing an equation parser.

Narayan

 

[Hussein]

Thanks!