VBA调用javascript

函数接口

Function execJSFunc(filePath, funcName)
    Dim code
    Open filePath For Input As #1
    Do While Not EOF(1)
        Line Input #1, tmpCode
        code = code & tmpCode & Chr(13)
    Loop
    Close #1

    Set JS = CreateObject("ScriptControl")
    JS.Language = "JScript"
    JS.AddCode code
    Dim result
    result = JS.run(funcName, ThisWorkbook)
    execJSFunc = result
End Function

调用封装

Sub run(funcName)
    Dim path, fileName, pos, result
    path = ThisWorkbook.path
    pos = InStr(4, ThisWorkbook.Name, ".", 1)
    pos = Len(ThisWorkbook.Name) - 4
    fileName = Mid(ThisWorkbook.Name, 1, pos - 1)
    path = path + "\" + fileName + ".js"
    result = execJSFunc(path, funcName)
    Debug.Print result
End Sub

调用示例

Sub 按钮1_Click()
    run("hello")
End Sub

test.js源码

function hello(workbook) {
	var sheets = workbook.sheets;
	sheets("Sheet1").range("a3").value = 55555;
	return workbook.sheets.count;
}