如何实现VBA向VBS传递参数并接收VBS返回的变量值

以下是两种可直接落地的实现方案：

方案1：通过标准输出流传递（最简便） #

直接利用进程的标准输出流传递返回值，无需额外生成中间文件，适合返回短文本场景。

修改后x.vbs代码 #

Dim Return_VBS_Val 

'此处编写业务逻辑代码
Msgbox Wscript.Arguments(0)
Return_VBS_Val = "Hello VBA" 

' 将返回值写入标准输出流
WScript.StdOut.WriteLine Return_VBS_Val

修改后VBA代码 #

Sub LaunchScript()
    Dim wsh As Object
    Dim scriptPath As String, MyMsg As String
    Dim Return_VBS_Val As String
    Dim execObj As Object ' 用于捕获执行对象的输出流
    
    scriptPath = "C:\path\x.vbs"
    MyMsg = "I got into VBS"
     
    Set wsh = VBA.CreateObject("WScript.Shell")
    ' 用Exec方法代替Run，可捕获进程输出，指定cscript宿主避免弹窗干扰
    Set execObj = wsh.Exec("cscript """ & scriptPath & """ """ & MyMsg & """")
    
    ' 等待脚本执行完成
    Do While execObj.Status = 0
        DoEvents
    Loop
    
    ' 读取标准输出流的内容即为VBS返回值
    Return_VBS_Val = execObj.StdOut.ReadAll()
    
    ' 此处可正常使用Return_VBS_Val变量
    MsgBox "VBS返回值：" & Return_VBS_Val
End Sub

方案2：通过临时文件传递（兼容原Run写法） #

如果需要保留原有的wsh.Run写法，可以通过中间临时文件中转返回值，适合返回内容较长的场景。

修改后x.vbs代码 #

Dim Return_VBS_Val, fso, f
'此处编写业务逻辑代码
Msgbox Wscript.Arguments(0)
Return_VBS_Val = "Hello VBA" 

' 创建文件系统对象写临时文件
Set fso = CreateObject("Scripting.FileSystemObject")
' 临时文件路径可自定义，也可以调用系统临时目录
Set f = fso.CreateTextFile("C:\path\vbs_return.tmp", True)
f.Write Return_VBS_Val
f.Close
Set f = Nothing
Set fso = Nothing

修改后VBA代码 #

Sub LaunchScript()
    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1 
    Dim scriptPath As String, MyMsg As String
    Dim Return_VBS_Val As String
    Dim fso As Object, f As Object
    Const TMP_FILE As String = "C:\path\vbs_return.tmp"
    
    scriptPath = "C:\path\x.vbs"
    MyMsg = "I got into VBS"
     
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run """" & scriptPath & """ """ & MyMsg & """", windowStyle, waitOnReturn
    
    ' 执行完成后读取临时文件内容
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(TMP_FILE) Then
        Set f = fso.OpenTextFile(TMP_FILE, 1)
        Return_VBS_Val = f.ReadAll()
        f.Close
        ' 读取完成后删除临时文件
        fso.DeleteFile TMP_FILE
    End If
    Set f = Nothing
    Set fso = Nothing
    
    ' 此处可正常使用Return_VBS_Val变量
    MsgBox "VBS返回值：" & Return_VBS_Val
End sub

内容的提问来源于stack exchange，提问作者Goku