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

 

[Norie]

You are reading the value from the textbox ^<SUP><SUPER>correctly</SUP></SUPER> but it's still just text as far as VBA is concerned.

In the Evaluate you post cell references are being used, so the Evaluate is actually kind of redundant.

It could be written like this:

SC1 = .Cells(row, F_4) - .Cells(row, F_200) > P1

By the way, you might want to add parentheses so that the more than is evaluated after the subtraction.

 

[GGiven]

Norie

The key problem here is that I want to be able to enter different functions including UDF within the User Form Text box (or recall saved ones from a database.

What you suggest would work, but it would be coding the SC1 box to only contain the function:

SC1 = .Cells(row, F_4) - .Cells(row, F_200) > P1

I would also like to input to the User Form interface other functions such as:

SC1 = CrossesNBarsOver(row, L1_Line, 0.3, 5) And (.Cells(row, FR4S).Value > 0

'or other functions 

SC1 = CrossesUnder(row, L1_Line, 0.1, 5) And (.Cells(row, FR4S).Value < 0

Where CrossesUnder or CrossesNBarsOver are UDFs.

Is that possible to do in Excel VBA?

Thanks

 

[Norie]

The short answer is no, sort of anyway.

One way would be to, like I said earlier, parse what's in the textbox yourself.

Another possible way would be to write code to write code, but I'm not sure how or if that would work.

It might involve writing code to execute code that you've written in code, or something like that.

 

[Jaafar Tribak]

As Norie pointed out, the CrossesAbove boolean function looks more like an operator in VB which is still causing confusion.

Anyway see if this can put you in the right direction :

1- Add a new Userform to your project and put in it a TextBox and a commandbutton.

2-Place this code in the userform :

Public x As Long
Public y As Long
Private ScriptControl As Object

Private Sub CommandButton1_Click()

    Dim sCode As String
    Dim bRet As Boolean

    x = 1
    y = 1
  
    Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
    ScriptControl.Language = "vbscript"
    ScriptControl.AddObject " ", Me, True
    
    sCode = "Function BlnFunction" & vbNewLine & _
    TextBox1 & vbNewLine & "End Function"
    
    On Error Resume Next
    Call ScriptControl.AddCode(sCode)
    bRet = ScriptControl.Run("BlnFunction")
    ScriptControl.Reset
    
    MsgBox x & "+" & y & "=2" & "  Is " & bRet

End Sub

3-In the TextBox, type in : If x+y=2 Then BlnFunction=True and click the Button to return True.

If you were to type for example x+y=3 then the BlnFunction function wiould return False because x=1 and Y=1.

As you can see, you have added code dynamically to a Function via a TextBox string as requested.You should be able to do the same with Subs.

 

[mikerickson]

It sounds like the question is that you want to be able to type
"X crossesAbove Y" in a text box, press a button and get the answer.
and then type
"A CrossesBelow B" and get that answer.

You are writing your own programming language with the operator(s) crossesAbove and crossesBelow (perhaps more).

The problem is parsing out the string. Could I suggest two textboxes and a combobox instead of one textbox?

Select Case ComboBox1.Text
    Case "crossesAbove"
        MsgBox CrossesAbove(TextBox1.Text, TextBox2.Text)
    Case "crossesBelow"
        MsgBox CrossesAbove(TextBox2.Text, TextBox1.Text)
    Case "smashesTogether"
        MsgBox Not(CrossesAbove(TextBox1.Text, TextBox2.Text)) And Not(CrossesAbove(TextBox2.Text, TextBox1.Text))
End Select

 

[GGiven]

Thanks mikerickson and Jaafar for your replies.

Yes, I am attempting to write a very limited programming language and entering this into the text boxes using a user form and then having my program evaluate them. I thought "Evaluate()" might do this, now I'm not so sure.

Jaafar, your code looks promising, but when I followed your instructions and ran it, I got Error 429 on the

Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")

Reviewing the Help told me:


"If the attempt to instantiate is the result of a CreateObject or GetObject call, the object must find the key. In this case, it may search the system registry or look for a special file that it creates when it is installed, for example, one with the extension .lic. If the key can't be found, the object can't be instantiated. If an end user has improperly set up the object's application, inadvertently deleted a necessary file, or changed the system registry, the object may not be able to find its key. If the key can't be found, the object can't be instantiated..."

I then looked at Google for help and found that MSScriptControl.ScriptControl is not available on 64 bit machines, which I am running...

Does anyone have any suggestions how to fix this or a work around?

thanks

 

[GGiven]

Just as a follow up, I did manage to run Jaafar's program on my 32 bit work machine. However, although it runs, the Msgbox always returns a "False" answer, even when x+y=2...

I've never worked with MSscriptcontrol and would want to use the program I'm developing in a 64 bit environment.

any suggestions would be greatly appreciated.

thanks

 

[Jaafar Tribak]

I did a quick search on google and found that 32 bit controls don't work in 64bit systems.This is unfortunate.

As for the code you tested on a Win32 machine, are you sure the syntax for TextBox1 and CommandButton1 controls is correct ? It worked flawlessly on my system.

Try commenting out the On Error Resume Next line and see what error you get .

 

[GGiven]

Jaafar,

I rechecked everything and it looks okay, but still only get "false" as a result.

I copied your code directly into my machine.

In any case, since I need to use a 64 bit machine, this solution will not work for me.

MSscriptcontrol is getting a bit beyond my comfort zone for vba, but I wonder what replaces this in the new 64 bit environment? Maybe there is another way I can accomplish what I'm trying to do...

Thanks