Drawbacks for checking whether input functions are valid in a textbox

quemuenchatocha

Board Regular
Joined
Aug 4, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Kind regards

I am carrying out a linear regression process, for which I have resorted to the use of a userform, where in each of the four textboxes I have to enter the input data corresponding to four functions fi(x)

Example_4.png


To check if the input functions are valid and do not present some kind of error (if the textboxes are empty, have values equal to 0, or any other text string that does not represent a function of x) I have proposed the following lines:

VBA Code:
If p = 1 Then
    box = MsgBox("No functions were provided for regression analysis.", vbInformation, "Regression Toolbox Info")
    Exit Sub
End If

VBA Code:
For i = 2 To p
    If (fxn(i - 1) = "x") Then
    ElseIf IsError(Evaluate(Replace(fxn(i - 1), "x", 2))) Then
        box = MsgBox("The f" & i - 1 & "(x) input is invalid as it is not a function of x." & _
        "Check your data input and correctness of syntaxis of f(x) functions." & _
        vbNewLine & vbNewLine & "If you want to type in only x you should type in ""1*x""." & _
        vbNewLine & vbNewLine & _
        "Several functions with multiplication of x (e.g. fx1 = 1*x, fx2 = 2*x)" & _
        "are not allowed.", vbCritical, "Error!")
        Exit Sub
    ElseIf InStr(fxn(i - 1), "x") = 0 Then
        box = MsgBox("The f" & i - 1 & "(x) input is invalid as it is not a function of x", vbCritical, "Error!")
        Exit Sub
    End If
Next i

However I am only having problems with the lines identifying that the value entered in any of the textbox does not represent a function of "x"

Example_5.png


In summary, what I am looking for is that when entering a value, in any of the textboxes, either of the function type such as x^2, sqrt(x), 1/x, ln(x), 2*x^4, EXP(x) or LOG10(x), the code will work normally as proposed, but if instead, I enter values to the textboxes as text strings such as "TDBWKO1546", z^2, y*sqrt(z), or another type of value that is not related to the variable "x", I get the dialog box that An error has occurred!

For suggestions, I proposed the lines that way, but I still don't understand why I keep getting errors. I have thought that the problem is at the moment of declaring the variable fxn() As Variant, I have changed it to String, made some modifications to the lines of the code, but I still get errors. I would appreciate any suggestion to help me to continue with the syntax without generating this type of errors.

Thank you very much for your attention.

pdt: In the following link Example, you can find the Workbook with the code I am working on. By omitting the lines related to the validation of the input functions, the whole code works perfectly.

Example_3.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,214,376
Messages
6,119,179
Members
448,871
Latest member
hengshankouniuniu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top