从 vbscript 调用 vba 函数（带参数）并显示结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13232615/
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 vba function(with parameters) from vbscript and show the result
提问by Zuzu JH
I have the following vba function ( in a module in Excel file)
我有以下 vba 函数（在 Excel 文件的模块中）
Public Function validate_fncname(strFncname As String) As Boolean
.
.
.
validate_fncname = True
End Function
and I wrote the following vbscript to call it :
我编写了以下 vbscript 来调用它：
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm")
objExcel.Application.Visible = True
Dim str
str ="hello"
Dim validate_fncname
validate_fncname = objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname", str)
Wscript.Echo validate_fncname
however when I run the script, it gives me type mismatch error for the line:
但是，当我运行脚本时，它给了我该行的类型不匹配错误：
objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname", str)
Despite the fact that the type is correct (String)
尽管类型是正确的（字符串）
furthermore if I change it to :
此外，如果我将其更改为：
objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname(5)")
It doesn't give me an error, although 5 is integer!
它不会给我一个错误，虽然 5 是整数！
Where is my mistake please?
请问我的错误在哪里？
采纳答案by Kevin Pope
You already have your validate_fncnamemethod, which looks fine (except setting the return value to Trueshould probably be done in an if thenstatement to avoid setting everything to True).
你已经有了你的validate_fncname方法，看起来不错（除了将返回值设置为True可能应该在if then语句中完成以避免将所有内容设置为True）。
When calling this function, you should just be able to do the following:
调用此函数时，您应该能够执行以下操作：
Dim validateResult As Boolean
Dim str as String
str = "hello"
validateResult = validate_fncname(str)
Debug.Print validateResult
回答by Rick Sharp
I played with this for a while and came up with the same error.
In my case I was just trying to pass a string back and forth.
我玩了一段时间并提出了同样的错误。
就我而言，我只是想来回传递一个字符串。
Then I looked up the dim statement for VBScript and it says "All VBScript variables are variants" so I changed the function on the spreadsheet to as Variant and that worked for me. Hope this helps others that come across this thread.
然后我查找了 VBScript 的 dim 语句，它说“所有 VBScript 变量都是变体”，所以我将电子表格上的函数更改为 Variant，这对我有用。希望这可以帮助遇到此线程的其他人。
