Dynamically Changing Code

[riskassure]

Hi, I have the following question:

I have a form which contains a drop down box and a button. The values of the drop down box are texts of partial VBA code.

Underneath the button is a VBA code, which looks roughly like

private sub blahblahblah()
blah blah blah
blah blah blah
x
blah blah blah
end sub

which will trigger when I click the button. In addition, x will be replaced by the value of the drop down that I selected, so that the execution can continue.

Is this possible? If not, is there another approach? Any help will be greatly appreciated!

Chi

~~CW~~

 

[MajP]

I do not understand the concept of why you would do something like this. Could you explain more? However, take a look at the eval function.

http://office.microsoft.com/en-us/access/HA012289421033.aspx

I would think it would be a lot cleaner to return a value (hidden column in the combo box) that represents that piece of code. Then use a select statement to execute the code based on the value returned.

 

[riskassure]

Here's my dilemma: the values are neither numbers nor simple strings, but texts that are really pieces of code, like "if A < B".

So the drop down has several values, and all values represent very different pieces of code.

However, when they are inserted properly, and somehow "interpreted" as code, then they should work perfectly.

For example, let's say the selected drop down is "if A < B", and the VBA looks like

private sub blahblahblah()
"me.dropdown.value" then D = 1
end sub

My question is: is there a way to "interpret" the value of the drop down as part of a VBA script, so the above code, after being interpreted, becomes

private sub blahblahblah()
if A < B then D = 1
end sub

~~CW~~

 

[MajP]

the values are neither numbers nor simple strings, but texts that are really pieces of code

No, the values are simply strings. However, like you said they represent a piece of code

You could probably use the eval function like I stated, but this still seems pretty clunky. It appears to me all you want to do is control the flow of the code execution. This is how I would do it. I would build my code snippet table with an ID key. For example

tblCodeSnippet
intCodeID (primary Key)
strCodeSnippet (string code snippet)

intCodeID strCodeSnippet
1 if A > B then
2 docmd.opendatabase("tblX",dbopendynaset)
3 call subroutine2

then in a event procedure for the combo I would control code flow by using a select case structure.

private sub myCombo_eventName()
some code common to all choices
select case myCombo.value
case 1
call subA>B
case 2
call subOpenDatabase
case3
call subRoutine2
end select
some more code common to all choices
end sub

bind the first column of your combo, but hide it. So for the first value you will see
"if A>B then", but the value returned is 1. This will make updating the table and the code easier. Basically I compartmentalize the code using a select case and sub routines that have unique code based on a selection.

 

[MajP]

To clarify, In my example I suggested using three sub routines:
"subA>B"
"subOpenDatabase"
"subRoutine2"
to compartmentalize the code. I mistakenly suggested that you could call a procedure "subA>B", this name would cause a compile error because the greater than symbol is a reserved character and can not be used in a procedure name.

 

[MikeC14081972]

Change the Procedure Name to something elsse and that in the Select Case.

 

[MakeItSo]

Hi there!

If you truly want to run code that you piece together dynamically, you may want to check out the "ScriptControl" control.

Here's an example of how a simple procedure is pieced together from strings and then run:

strcode = "Sub Updateme()" & vbNewLine
    strcode = strcode & "Dim obfFile" & vbNewLine
    strcode = strcode & "Set objFile = CreateObject(" & Chr(34) & "DSOFile.OleDocumentProperties" & Chr(34) & ")" & vbNewLine
    strcode = strcode & "objFile.Open " & Chr(34) & pfad & Form2.filez & Chr(34) & vbNewLine
    strcode = strcode & "objFile.SummaryProperties." & Label1.Caption & "=" & Chr(34) & Text1.Text & Chr(34) & vbNewLine
    strcode = strcode & "objFile.Save" & vbNewLine & "objFile.Close" & vbNewLine & "Set objFile = Nothing" & vbNewLine & "Set objProperty = Nothing" & vbNewLine
    strcode = strcode & "End Sub" & vbNewLine
    Debug.Print strcode
[b]    ScriptControl1.AddCode strcode
    ScriptControl1.Run "Updateme"[/b]

With it, I create some code to update certain document properties. Since I do not know, which properties will be updated beforehand, I decided to use a script control to generate this code.
Here's a typical debug.print of the strcode variable:

Sub Updateme()
Dim obfFile
Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open "D:\PastePicture.xls"
objFile.SummaryProperties.Author="MakeItSo"
objFile.Save
objFile.Close
Set objFile = Nothing
Set objProperty = Nothing
End Sub

Okie dokie?
;-)

Cheers,
miS


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[riskassure]

Very interesting! Thanks a bunch folks!

~~CW~~