Calling VBA Sheet Function (with arguments) from VBS and getting its return value

Question

I am trying to call a VBA function from VBS script:

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

The macro (VBA function) works fine. The only thing that I am missing is within the VBS script. When I call the vba function from vbs script it runs fine but I cannot get the return value in the "returnValue" variable defined in VBS (it does not show anything). Can anyone tell what I am doing wrong? Thanks!

What return value do you expect? (see comment _empty string if successful execution_ in your code) — Shrotter, Nov 13 '22 at 11:21
When I'm calling the macro from VBS look at the path that I've used in the first argument (...\NON-EXISTENT-DIRECTORY). Maybe I haven't explicitly stated in the post, sorry! — Andoni, Nov 13 '22 at 13:58

score 2 · Accepted Answer · answered Nov 13 '22 at 11:16

2

Based on the name sheet1 (in your VBS script), I'm assuming the f_split_master_file Function is in a Worksheet module. Move it to a standard Module and change sheet1 to (eg) Module1 and then try again.

answered Nov 13 '22 at 11:16

JohnM

2,422
2
8
20

That solved the issue, thanks! (however, why is that so?) – Andoni Nov 13 '22 at 13:58
2

Good question. `Application.Run` will call Functions in standard and 'document' Modules (in Excel, 'document' Modules are Sheet, Chart and ThisWorkbook) but you only get a value returned from those in standard Modules. The [MS docs](https://learn.microsoft.com/en-us/office/vba/api/excel.application.run) do not explain why this is, in fact saying "The Run method returns whatever the called macro returns" ... this being one of the (very) many inaccuracies in VBA-related MS Docs!!! I just new from experience that this is the case. – JohnM Nov 13 '22 at 14:49

Calling VBA Sheet Function (with arguments) from VBS and getting its return value

1 Answers1