User Form and Dynamic Code

[GGiven]

Hello
I'm still new to VBA for Excel. I've developed a User Form that I'd like to allow Text Box input to change the code logic within the program itself (in another module).

I've looked at CallByName, but don't think that will work.

For Example: TextBox1: "If X CrossesAbove Y"

This returns a boolean value.

Within the module I have a code line that says: BuyCondiiton = TextBox1

Note CrossesAbove is a user defined function.

I'm I dreaming or is this possible with VBA. If not is there another language that might work for this.

thanks
GGiven

 

[TryingToLearn]

I'm confused with the UDF in the text box so not quite sure this is what you are looking for but as a novice myself I would create a public variable on opening the worksheet.

PUBLIC BuyCondition as Boolean

In the userform code:

Private Sub TextBox1_AfterUpdate()
If UCase(TextBox1.Text)= "If X CrossesAbove Y" then BuyCondition = TRUE
End Sub

BuyCondition is then available to other code as TRUE

 

[Norie]

I'm also confused by the UDF.

It doesn't seem to have any input or output.

It looks almost like it's being used as an operator with X and Y the arguments/operands.

Not sure about the dynamic code and changing the logic part either.

VBA has logical structures that you can use to decide what action to take based on some criteria, eg the value in a textbox.

 

[GGiven]

Thanks for the replies

Let me add that in the TextBox1, I would want to change "If X CrossesAbove Y" with many other boolean statements such as "If Z CrossesBelow A", etc.

The User Defined Functions I've created evaluate a vector at t, t-1 etc to see if the condition has been met. To be more specific:

CrossesOver(row, SigLn, ThrLn, ThrVal, IncVal) is an example of one of these.
These functions work in my code and return True or False.

What I'm not sure about is whether I can input combinations of these functions and other boolean operators into a Text Box within a user form and then have this linked to a boolean variable defined within my code module? So that the statement in the text box can be "transferred" to the variable within the code and for each iteration of the model the code evaluates the statement.

(So when the code runs the text within the user box would be "assigned" to the boolean variable within the code. Each iteration of the code (each day) the code would evaluate the variable assigned to the text box.)

Why am I trying to do this? It is to allow the user to only need to edit the User Form rather than having to go directly into the code module itself to make changes. It also allows the user to save the User form text box contents into a database that can be retrieved for later use.

TryingToLearn:

the code you provide (If UCase(TextBox1.Text)= "If X CrossesAbove Y" then BuyCondition = TRUE) would work but there is an infinite number of conditions to test.

Thanks

 

[jkpieterse]

Maybe this helps.
for demonstration purposes, enter this in cells A1:C4 (each character goes in its own column):

1 > 2
2 < 3
3 = 5
4 = 4

Select cells A1:A4 and run the demo sub shown below.

Option Explicit

Public Function EvaluateSomething(Arg1, Operator, Arg2) As Boolean
    EvaluateSomething = Evaluate(Arg1 & Operator & Arg2)
End Function


Public Sub demo()
    Dim oCell As Range
    For Each oCell In Selection
        MsgBox oCell.Value & oCell.Offset(, 1).Value & oCell.Offset(, 2).Value & " yields:" & EvaluateSomething(oCell.Value, oCell.Offset(, 1).Value, oCell.Offset(, 2).Value)
    Next
End Sub

 

[Norie]

You can input whatever you want in a textbox but it's going to be text.

So you are going to need to find some way to parse what the user enters so you have something to work with.

PS Where do all the arguments come from for the function and what are X and Y?

 

[TryingToLearn]

PUBLIC BuyCondition as Boolean
BuyCondition = InputBox ("Enter Condition")



This would allow users to type in their data and your UDF to True or False

 

[GGiven]

Thanks for the replies.

Norie, I was afraid that a text box can only contain text. I'll investigate trying to parse this into individual elements later. (You asked about the function arguments and X and Y. The arguments com from either vectors that are created by other functions or in the case of ThrVal, as a scalar. X and Y would also be column vectors.)

Evaluate is new to me, I'll take a look at this too. Maybe parsing the text into components and using Evaluate on each might work...

GGiven

 

[Norie]

GGiven

What I was wondering was where the X and Y, or whatever variables, are used in the function.

Are you taking X and Y, calculating the rest of the arguments and then plugging those into the formula.

The formula then returns a boolean indicating the result?

If that's what's happening why not pass X and Y to a function along with another argument.

The 3rd argument determines which formula to use, so it would be something like this.

Function GetAnswer(varX, VarY, Optional FcnIdx = 1) As Boolean
      Select Case FcnIdx
           Case 1
               GetAnswer = ' call to CrossesAbove function
           Case 2
               GetAnswer = ' call to CrossesBelow function
             ....
           Case X
 
               GetAnswer = ' call to CrossesX function 
         End Select
 
End Function

Does that make any sense or is it way, way off?

 

[GGiven]

Everyone,

I've been reviewing and experimenting with the Evaluate function to solve my problem. What has worked for me is Evaluating basic expressions contained within a user form text box using the Evaluate function (ie: "a > b"). What I'm having trouble with is reading directly from the user form.

 sub example()

'This works:
SC1 = Evaluate(.Cells(row, F_4) - .Cells(row, F_200) > P1)

'But this doesn't:
SC1 = Evaluate(UserForm1.SC1_box.Value)
'where UserForm1.SC1_box.Value contains: .Cells(row, F_4) - .Cells(row, F_200) > P1


'Note F_4 and F_200 are user defined functions and P1 is a scalar

end sub

In the latter case I get "SC1 = Error 2015" instead of True of False.

Anyone have any idea how to read Textbox values from a User Form?

Thanks