此问题在此处已有答案：

Run Excel Macro from Outside Excel Using VBScript From Command Line（8个答案）
11天前关闭。
这篇文章是4天前编辑并提交审查的。
我正在尝试从VBS脚本调用VBA函数：

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

宏（VBA函数）运行正常。我唯一缺少的是在VBS脚本中。当我从VBS脚本调用Vba函数时，它运行正常，但我无法获得VBS中定义的“returnValue”变量的返回值（它没有显示任何内容）。有人能告诉我哪里做错了吗？谢谢！