从 VBA 调用 JavaScript 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23580472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling JavaScript files from VBA
提问by S Meaden
if I want to write business logic into javascript files and test them from Excel VBA before handling them over to a web author then that requires being able to call javascript from VBA. From other Stack Overflow questions (like parsing JSON) I have discovered the Microsoft Script Control and so I have been trying this but I got stuck.
如果我想将业务逻辑写入 javascript 文件并在将它们处理给 Web 作者之前从 Excel VBA 测试它们，那么这需要能够从 VBA 调用 javascript。从其他 Stack Overflow 问题（如解析 JSON）中，我发现了 Microsoft Script Control，因此我一直在尝试这个，但我被卡住了。
I have a javascript file which I save to 'c:\temp\starthere.js' which contains a trivial javascript function
我有一个 javascript 文件，我将它保存到“c:\temp\starthere.js”，其中包含一个简单的 javascript 函数
function DoubleMe(a) {
return a * 2;
}
then I have some VBA code to create an instance of Microsoft Script Control, load the file into it and then attempt to call it. Code is underneath. One can recreate by firing up Excel VBA and pasting in code though you'll need reference to Microsoft Script Control (for me at C:\Windows\SysWOW64\msscript.ocx). Then page down to 'Sub TestRunIncluded()' where you will see my aborted attempts to get this working.
然后我有一些 VBA 代码来创建 Microsoft Script Control 的实例，将文件加载到其中，然后尝试调用它。代码在下面。可以通过启动 Excel VBA 并粘贴代码来重新创建，尽管您需要参考 Microsoft Script Control（对我而言，在C:\Windows\SysWOW64\msscript.ocx）。然后向下翻到“Sub TestRunIncluded()”，在那里您将看到我为使其正常工作而中止的尝试。
The objective is to get the code to call into the javascript (whilst it is housed in a file) and return double the argument passed in.
目标是让代码调用 javascript（虽然它位于文件中）并返回传入的参数的两倍。
I really want this to work as I have lots of test scripts (for stuff more complicated than doubling!) written in Excel VBA. Please help.
我真的希望它能够工作，因为我有很多用 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
采纳答案by S Meaden
Well it looks like the answer was simple....doh!
嗯，看起来答案很简单……哦！
Debug.Assert ScriptEngine.Eval("DoubleMe(3)") = 6
Debug.Assert ScriptEngine.Eval("DoubleMe(3)") = 6
Good resource here http://support.microsoft.com/kb/184740
