How to execute a string using VB?

Posted on 2003-03-26
Medium Priority
Last Modified: 2012-06-27
I'm making a program to build "ON-LINE" VB commands... all of these are saved in a DB.
When the program reads a record, the command is stored in a variable. for example:

dim comd as string

comd=rs("COMMAND")  ' reading the DB   COMMAND="FORMAT(CDATE(Text1.text),"MM/DD/YYYY")"
at last comd should have 02/12/1998

How to execute the command stored in comd and get the right date. I really appreciate your comments.
Thanks a lot

Question by:PatrickO
  • 4
  • 2
  • 2
  • +6
LVL 14

Expert Comment

ID: 8214131
Impossible. In VB you have to assign the database results to a variable which VB reads as a variable and not as part of the code.

VB cannot use functions or code stored in a database.
Including external code or function(s) isn't available either !


Expert Comment

ID: 8214149

  VB is a compiled language so I don't see how you could do this.

  Have you thought about reading your variable information then creating a vbscript object on the fly and executing that?

LVL 11

Expert Comment

ID: 8214202
I think you have to write a parser to do that....but I think it is too complicated.  Not sure how you could do that at this point.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.


Expert Comment

ID: 8214226
From the Project menu, select "References" and add "Microsoft Script Control 1.0". Add Command Button "Command1" to your form and paste this into your code:

Private Sub Command1_Click()
Dim S As String
Dim oSC As ScriptControl
Dim iVal
Dim toexe As String
    Set oSC = New ScriptControl
    oSC.Language = "VBScript"
    toexe = "FormatDateTime(" & S & ", vbLongDate)"
    iVal = oSC.Eval(toexe)
    MsgBox iVal, vbOKOnly, "Value of iVal"
End Sub

Nothing will execute VB Code but this will execute VBA code.


Expert Comment

ID: 8214237
Sorry.. I forgot a line
Here is the correct code:

Private Sub Command1_Click()
Dim S As String
Dim oSC As ScriptControl
Dim iVal
Dim toexe As String
    S = "02-12-1998"
    Set oSC = New ScriptControl
    oSC.Language = "VBScript"
    toexe = "FormatDateTime(" & S & ", vbLongDate)"
    iVal = oSC.Eval(toexe)
    MsgBox iVal, vbOKOnly, "Value of iVal"
End Sub

Accepted Solution

emadat earned 1500 total points
ID: 8214258
Here it is in a generic function:

Private Function ExecuteCMD(sCMD As String) As Variant
Dim oSC As ScriptControl
    Set oSC = New ScriptControl
    oSC.Language = "VBScript"
    ExecuteCMD = oSC.Eval(sCMD)
    Set oSC = Nothing
End Function

Private Sub Command1_Click()
Dim S As String, sEvaluated As String
    S = "02-12-1998"
    sEvaluated = ExecuteCMD("FormatDateTime(" & S & ", vbLongDate)")
End Sub
LVL 11

Expert Comment

ID: 8214287

Private Function InterpreteCmd(cmd As String) As Variant
    Dim i As Long
    Dim j As Long
    Dim str As String
    Dim valVar As Variant

    If (InStr(1, cmd, "Format(")) Then
        i = InStr(1, cmd, "Format(") + 7
        j = InStr(i + 1, cmd, ",")
        valVar = InterpreteCmd(Mid(cmd, i + 1, j - i - 1))
        i = InStr(j, cmd, """")
        j = InStr(i + 1, cmd, """")
        str = Mid(cmd, i + 1, j - i - 1)
        InterpreteCmd = Format(valVar, str)
        InterpreteCmd = cmd
    End If
End Function

InterpreteCmd("Format(22-10-76, ""mm/dd/yyyy"")")
' return value "22/10/1976"


Expert Comment

ID: 8214343
Nice answer emadat, you beat me to it. (all that typing for nothing ;) )  May I also add, that the Script Control is a very powerful tool and suggest that anyone who doesn't know about it go out and learn.

Expert Comment

ID: 8214851
use script, but not simply use Eval()

    Dim objScript As Object
    Dim sCode As String
    Dim comd As String
    strCode = "Function comd()" & vbNewLine & _
            "comd = " & rs("COMMAND") & vbNewLine & _
            "End Function"
    Set objScript = CreateObject("MSScriptControl.ScriptControl")
    objScript.Language = "VBScript"
    objScript.addObject "Text1", Text1 'Add any object will used by COMMAND
    objScript.AddCode strCode
    comd = objScript.Run("comd")

Expert Comment

ID: 8218355
Weel, if you have VB6, you might use the indirect function call CallByName().

Expert Comment

ID: 8219147
This function would not help in this matter.

According to MSDN:

The CallByName is a function theat executes a method of an object, or sets or returns a property of anobject.

And here are its remarks:

The CallByName function is used to get or set a property, or invoke a method at run time using a string name.

In the following example, the first line uses CallByName to set the MousePointer property of a text box, the second line gets the value of the MousePointer property, and the third line invokes the Move method to move the text box:

CallByName Text1, "MousePointer", vbLet, vbCrosshair
Result = CallByName (Text1, "MousePointer", vbGet)
CallByName Text1, "Move", vbMethod, 100, 100


Author Comment

ID: 8220733
10q guys for all your comments..
I`m going to check one by one..

the best one will receive the points..It`s a deal..


Expert Comment

ID: 8223676
What you are contemplating is a nightmare, and I go along fully with the comments above. However, it IS possible to make VB output vbp and frm files (which are ascii) and then open a new instance of VB and run the newly created project. I'm sure you can even get VB to compile the project and run the exe. I insist that script is the way to go, but for curiosities sake, here's an example of using VB to create VB:

Take a form and add a textbox (Text1) and a commandbutton (Command1). Then add the code below. The idea (kinda obviously)is that you type something into the textbox and hit the commandbutton. When you do this, VB writes a vbp project file and a frm form file to a given directory, and then opens it in VB. The newly created project contains a commandbutton, which, when you click it, will display the message you typed in to the textbox in the creator project.

Kindest regards,

'API to open the newly created VB project
Private Declare Function ShellExecute Lib "shell32" _
   Alias "ShellExecuteA" _
  (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWDEFAULT As Long = 10
Private Const SE_ERR_NOASSOC As Long = 31

Dim myPath As String
Dim myMessage As String

Private Sub Command1_Click()

'Change to where you want to store the project
myPath = "c:\WHEREVER\"
Open myPath & "Project1.vbp" For Output As #1
Print #1, "Type=Exe"
Print #1, "Form=Form1.frm"
Print #1, "Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#..\..\SYSTEM\stdole2.tlb#OLE Automation"
Print #1, "Startup=""Form1"""
Print #1, "Command32="""""
Print #1, "Name=""Project1"""
Print #1, "HelpContextID=""0"""
Print #1, "CompatibleMode=""0"""
Print #1, "MajorVer=1"
Print #1, "MinorVer=0"
Print #1, "RevisionVer=0"
Print #1, "AutoIncrementVer=0"
Print #1, "ServerSupportFiles=0"
Print #1, "CompilationType=0"
Print #1, "OptimizationType=0"
Print #1, "FavorPentiumPro(tm)=0"
Print #1, "CodeViewDebugInfo=0"
Print #1, "NoAliasing=0"
Print #1, "BoundsCheck=0"
Print #1, "OverflowCheck=0"
Print #1, "FlPointCheck=0"
Print #1, "FDIVCheck=0"
Print #1, "UnroundedFP=0"
Print #1, "StartMode=0"
Print #1, "Unattended=0"
Print #1, "Retained=0"
Print #1, "ThreadPerObject=0"
Print #1, "MaxNumberOfThreads=1"
Close #1

Open myPath & "Form1.frm" For Output As #2
Print #2, "VERSION 5.00"
Print #2, "Begin VB.Form Form1"
Print #2, "   Caption=""Form1"""
Print #2, "   ClientHeight=3195"
Print #2, "   ClientLeft=60"
Print #2, "   ClientTop=345"
Print #2, "   ClientWidth=4680"
Print #2, "   LinkTopic=""Form1"""
Print #2, "   ScaleHeight=3195"
Print #2, "   ScaleWidth=4680"
Print #2, "   StartUpPosition=3    'Windows Default"
Print #2, "   Begin VB.CommandButton Command1"
Print #2, "      Caption=""Click Me!"""
Print #2, "      Height=855"
Print #2, "      Left=600"
Print #2, "      TabIndex=0"
Print #2, "      Top=720"
Print #2, "      Width=1935"
Print #2, "   End"
Print #2, "End"
Print #2, "Attribute VB_Name=""Form1"""
Print #2, "Attribute VB_GlobalNameSpace=False"
Print #2, "Attribute VB_Creatable=False"
Print #2, "Attribute VB_PredeclaredId=True"
Print #2, "Attribute VB_Exposed=False"
Print #2, "Private Sub Command1_Click()"
'Add text to msgbox in output program
Print #2, "MsgBox """ & Text1.Text & """"
Print #2, "End Sub"
Close #2

'The new Project is now created
'To open it in VB use:
allPath = myPath & "Project1.vbp"
Call ShellExecute(0&, "open", allPath, 0&, 0&, SW_SHOWNORMAL)
'Then wait for it to be fully loaded and send F5
'Better still, compile it and shell execute it.

End Sub

Author Comment

ID: 8307632
I gonna give the points to emadat for the idea to use VBScript Control...

Now, It is  not working very well , but the idea is good.
Thanks a Lot..

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question