javascript之从 VBA 调用 JavaScript 文件

如果我想将业务逻辑写入 javascript 文件并在将它们处理给 Web 作者之前从 Excel VBA 测试它们，则需要能够从 VBA 调用 javascript。从其他 Stack Overflow 问题(比如解析 JSON)中，我发现了 Microsoft Script Control，所以我一直在尝试这个，但我陷入了困境。

我有一个 javascript 文件，我将其保存到“c:\temp\starthere.js”，其中包含一个简单的 javascript 函数

     function DoubleMe(a) { 
         return a * 2; 
     } 

然后我有一些 VBA 代码来创建 Microsoft Script Control 的实例，将文件加载到其中，然后尝试调用它。代码在下面。可以通过启动 Excel VBA 并粘贴代码来重新创建，但您需要引用 Microsoft Script Control(对我来说位于 C:\Windows\SysWOW64\msscript.ocx)。然后向下翻页到“Sub TestRunInincluded()”，您将在其中看到我为使其正常工作而中止的尝试。

目标是让代码调用 JavaScript(虽然它位于文件中)并返回传入参数的两倍。

我真的希望它能够工作，因为我有很多用 Excel VBA 编写的测试脚本(用于比加倍更复杂的东西!)。请帮忙。

    Option Explicit 
 
    'code loosely based on 'http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop 
 
    'Tools->References-> 
    'MSScriptControl; Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx 
 
    'other libraries are late bound 
 
    Private moScriptEngine As ScriptControl 
 
    Public Property Get ScriptEngine() 
        'factory 
        If moScriptEngine Is Nothing Then 
            Set moScriptEngine = New ScriptControl 
            moScriptEngine.Language = "JScript" 
            'moScriptEngine.AllowUI = True 
        End If 
        Set ScriptEngine = moScriptEngine 
    End Property 
 
    Public Sub AddJSFile(ByVal sJSFilename As String) 
        Debug.Assert LCase$(Right$(sJSFilename, 3)) = ".js" 
        Dim fso As Object 'Scripting.FileSystemObject 
        Set fso = VBA.CreateObject("Scripting.FileSystemObject") 
 
        Debug.Assert fso.FileExists(sJSFilename) 
        Dim fil As Object 'Scripting.File 
        Set fil = fso.GetFile(sJSFilename) 
 
        Dim ins As Object 'Scripting.TextStream 
        Set ins = fil.OpenAsTextStream(1)  '1=ForReading 
 
        Dim sCode As String 
        sCode = ins.ReadAll 
 
        ins.Close 
        Set ins = Nothing 
        Set fil = Nothing 
        Set fso = Nothing 
 
        Debug.Assert Len(sCode) > 0 
        ScriptEngine.AddCode sCode 
 
 
    End Sub 
 
    Sub TestAddJSFile() 
        AddJSFile "c:\temp\starthere.js" 
        '* c:\temp\starthere.js contains the following four lines, last line is just a newline (from Notepad) 
        'function DoubleMe(a) { 
        '    return a * 2; 
        '} 
        '<newline> 
 
    End Sub 
 
    '!Fellow StackOverflow users, press F5 on this Sub as this is entry point! 
    Sub TestRunIncluded() 
        TestAddJSFile 
        Dim res 
        Debug.Assert Not IsObject(ScriptEngine.Eval("var b=DoubleMe(3)")) 
        'Debug.Print ScriptEngine.Eval("return DoubleMe(3) ")  'Errors 1018: "'return' statement outside of function" 
        Debug.Print ScriptEngine.Eval("(function { DoubleMe(3) })")  'Errors 1005: "expected '('" 
        'Debug.Assert IsObject(ScriptEngine.Run("(DoubleMe(3))")) 
        'Debug.Assert IsObject(ScriptEngine.Eval("(DoubleMe(3))")) 
        'Set res = ScriptEngine.Eval("DoubleMe(3);") 
    End Sub 

请您参考如下方法:

嗯，看起来答案很简单......doh!

Debug.Assert ScriptEngine.Eval("DoubleMe(3)") = 6

这里有好资源http://support.microsoft.com/kb/184740