VBA 中的 MS 脚本控件

MS Script Control in VBA

提问by Troy Mitchel

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.

我通常使用 VB.Net 进行编程，但我已被委派给 VBA 项目，该项目将受益于使用脚本控件运行脚本。这是示例代码，但 .Run 代码行出错。我可以在 VB.Net 中轻松完成此操作，但无法在 vba 中使用它。

ERROR = Wrong number of arguments or invalid property assignent

ERROR = 错误数量的参数或无效的属性分配

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...

使用脚本控制对象中的 .Eval 函数在 vba 中运行正常，但不运行脚本。这是一个例子，如果有人想知道......

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

回答by competent_tech

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

我认为问题在于您的参数sc.Run. 尝试更改此调用

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

回答by Troy Mitchel

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

这是 VBA 的最终工作代码。Competent_tech 有一个用于 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