从VBS调用带参数的VBA工作表函数并获取返回值异常问题

我尝试从VBS脚本中调用VBA函数，VBA宏函数运行正常，但VBS调用时虽能正常执行函数逻辑，却无法在returnValue变量中获取函数返回值，请问问题出在哪里？

VBA代码 #

Function f_split_master_file(output_folder_path As String, master_excel_file_path As String) As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo ErrorHandler
    Dim wb As Workbook
    Dim output As String
    ' Variables related with the master excel file
    Dim wb_master   As Workbook
    Dim ws_master   As Worksheet
    Dim master_range As Range
    Dim responsible_names_range As Range
    Dim responsible_name As Range
    Dim last_row_master As Integer
    ' Variables related with the responsible name excel
    Dim savepath    As String
    Dim wb_name     As Workbook
    Dim ws_name     As Worksheet
    Dim name        As Variant
    ' Check whether master file exists
    If Len(Dir(master_excel_file_path)) = 0 Then ' Master file does not exist
        Err.Raise vbObjectError + 513, "Sheet1::f_split_master_file()", "Incorrect Master file path, file does not exist!"
    End If
    ' Check whether output folder exists
    If Dir(output_folder_path, vbDirectory) = "" Then ' Output folder path does not exist
        Err.Raise vbObjectError + 513, "Sheet1::f_split_master_file()", "Incorrect output folder path, directory does not exist!"
    End If
    Set wb_master = Workbooks.Open(master_excel_file_path)
    Set ws_master = wb_master.Sheets(1)
    last_row_master = ws_master.Cells(Rows.Count, "AC").End(xlUp).row
    Set master_range = ws_master.Range("A1:AD" & last_row_master)
    Set responsible_names_range = ws_master.Range("AC2:AC" & last_row_master)        ' Get all names
    data = get_unique_responsibles(responsible_names_range)        'Call function to get an array containing distict names (column AC)
    For Each name In data
        'Create wb with name
        savepath = output_folder_path & "\" & name & ".xlsx"
        Workbooks.Add
        ActiveWorkbook.SaveAs savepath
        Set wb_name = ActiveWorkbook
        Set ws_name = wb_name.Sheets(1)
        master_range.AutoFilter 29, Criteria1:=name, Operator:=xlFilterValues
        master_range.SpecialCells(xlCellTypeVisible).Copy
        ws_name.Range("A1").PasteSpecial Paste:=xlPasteAll
        wb_name.Close SaveChanges:=True
        ' Remove filters and save workbook
        Application.CutCopyMode = False
        ws_master.AutoFilterMode = False
    Next name
CleanUp:
    ' Close all wb and enable screen updates and alerts
    For Each wb In Workbooks
        If wb.name <> ThisWorkbook.name Then
            wb.Close
        End If
    Next wb
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    f_split_master_file = output ' empty string if successful execution
    Exit Function
ErrorHandler:
    ' TODO: Log to file
    ' Err object is reset when it exits from here IMPORTANT!
    output = Err.Description
Resume CleanUp
End Function

VBS代码 #

Set excelOBJ = CreateObject("Excel.Application")
Set workbookOBJ = excelOBJ.Workbooks.Open("C:\Users\aagir\Desktop\BUDGET_AND_FORECAST\Macro_DoNotDeleteMe_ANDONI.xlsm")
returnValue = excelOBJ.Run("sheet1.f_split_master_file","C:\Users\aagir\Desktop\NON-EXISTENT-DIRECTORY","C:\Users\aagir\Desktop\MasterReport_29092022.xlsx")
workbookOBJ.Close
excelOBJ.Quit
msgbox returnValue

问题原因与解决方案 #

核心问题：调用方式错误 #

你在VBS中使用excelOBJ.Run调用VBA函数，这种方式无法保证返回值正确传递到VBS变量中。正确的做法是使用包含该VBA函数的工作簿对象（workbookOBJ）来执行Run方法，这样能确保返回值的正确传递。

修正后的VBS代码 #

Set excelOBJ = CreateObject("Excel.Application")
Set workbookOBJ = excelOBJ.Workbooks.Open("C:\Users\aagir\Desktop\BUDGET_AND_FORECAST\Macro_DoNotDeleteMe_ANDONI.xlsm")
' 改用工作簿对象调用Run方法
returnValue = workbookOBJ.Run("sheet1.f_split_master_file","C:\Users\aagir\Desktop\NON-EXISTENT-DIRECTORY","C:\Users\aagir\Desktop\MasterReport_29092022.xlsx")
workbookOBJ.Close
excelOBJ.Quit
msgbox returnValue

额外优化建议（提升代码稳定性） #

1. 限定Rows.Count的上下文：VBA中Rows.Count默认引用当前激活的工作簿，容易引发错误，建议修改为：

   last_row_master = ws_master.Cells(ws_master.Rows.Count, "AC").End(xlUp).Row
   

2. 显式声明变量：VBA中data变量未声明类型，建议添加Dim data As Variant，增强代码可读性和稳定性。

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