CODEWITHSUNDEEP
vb.netvbavbscript
Troy Mitchel
Troy Mitchel

Reputation: 1810

MS Script Control in VBA

I generally use VB.Net for programming but I have been delegated to a VBA project that would benefit from using script control for running script. Here is sample code but errors out on the .Run line of code. I can do this in VB.Net easy but can't get it to work in vba.

ERROR = Wrong number of arguments or invalid property assignent

Option Explicit
Dim sc As New ScriptControl

Sub RunFunctions()
    MsgBox (Eval("2 * PI"))
End Sub

Function Eval(expr As String) As Object
    Dim code As String
    code = "Dim PI" & vbCrLf & _
           "PI = 3.1416" & vbCrLf & _
           " " & vbCrLf & _
           "Function Result" & vbCrLf & _
           "    Result = " & expr & vbCrLf & _
           "End Function"

    sc.Language = "VBScript"
    sc.AllowUI = True
    sc.AddCode (code)
    Dim myparams() as variant

    Eval = sc.Run("Result", myparams)

End Function

Using the .Eval function from the script control object runs ok in vba but does not run scripts. Here is an example of that if someone cares to know...

Sub SimpleTask()
    Dim expr As String
    sc.Language = "VBScript"
    expr = "2 + 2 * 50"
    MsgBox sc.Eval(expr)
End Sub

Upvotes: 0

Views: 8588

Answers (2)

Troy Mitchel
Troy Mitchel

Reputation: 1810

Here is the final working code for VBA. competent_tech has one for VB.Net.

Option Explicit
Dim sc As New ScriptControl

Sub RunFunctions()
    MsgBox (Eval("2 * PI"))
End Sub

Function Eval(expr As String) As String
    Dim code As String
    code = "Dim PI" & vbCrLf & _
           "PI = 3.1416" & vbCrLf & _
           " " & vbCrLf & _
           "Function Result" & vbCrLf & _
           "    Result = " & expr & vbCrLf & _
           "End Function"

    sc.Language = "VBScript"
    sc.AllowUI = True
    sc.AddCode (code)

    Eval = sc.Run("Result")

End Function

Upvotes: 1

competent_tech
competent_tech

Reputation: 44931

I think the issue is with your parameters to sc.Run. Try changing this call from

Eval = sc.Run("Result", myparams)

to 

Eval = sc.Run("Result")

Update

Here is a complete form using your code that compiles and runs correctly:

Option Explicit On
Option Strict On

Imports MSScriptControl

Public Class Form1

    Dim sc As New ScriptControl

    Sub RunFunctions()
        MsgBox(Eval("2 * PI"))
    End Sub

    Function Eval(expr As String) As Object
        Dim code As String
        code = "Dim PI" & vbCrLf & _
               "PI = 3.1416" & vbCrLf & _
               " " & vbCrLf & _
               "Function Result" & vbCrLf & _
               "    Result = " & expr & vbCrLf & _
               "End Function"

        sc.Language = "VBScript"
        sc.AllowUI = True
        sc.AddCode(code)
        Dim myparams() As Object

        Eval = sc.Run("Result")

    End Function
    Sub SimpleTask()
        Dim expr As String
        sc.Language = "VBScript"
        expr = "2 + 2 * 50"
        MsgBox(sc.Eval(expr))
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        SimpleTask()
        RunFunctions()
    End Sub
End Class

Upvotes: 2

Related Questions