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

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
– Shrotter, Commented Nov 13, 2022 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
– Andoni, Commented Nov 13, 2022 at 13:58

JohnM · Accepted Answer · 2022-11-13 11:16:13Z

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, 2022 at 11:16

JohnM

3,3904 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andoni Over a year ago

That solved the issue, thanks! (however, why is that so?)

JohnM Over a year ago

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 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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related