Run another macro in another Excel file

Question

Hi so I am building an Excel system to run macro for running several different Macros on other Excel files.

Let's say I have two Excel files: "Parent.xlsx" and "child.xlsx" opened already.

In my Parent.xlsx I am running an VBA script, how can I run a macro called "method1" in my "PERSONAL.XLSB" for my "child.xlsx"

Right now, in my Parent.xlsx, I try to run this macro VBA script:

Workbooks("child.xlsx").Application.Run "PERSONAL.XLSB!method1"

In my PERSONAL.XLSB in Modelue6, I have:

Sub method1()
    Dim rTable As Range
    Selection.AutoFilter
End sub

Error: Run-time error'1004': AutoFilter method of Range class failed

Thank you very much!

Answer 1

You need to qualify and reference the workbook that the code should act on.

Look into the ActiveWorkbook property here: https://docs.microsoft.com/en-us/office/vba/api/excel.application.activeworkbook

Answer 2

Application.Run

In Parent.xlsx (to keep the code, save the file as e.g. Parent.xlsm)

Sub callMethod1()
    Dim wb As Workbook
    Set wb = Workbooks("child.xlsx")
    Application.Run "PERSONAL.XLSB!method1", wb
End Sub

In PERSONAL.XLSB

Sub method1(wb As Workbook)
    wb.Activate
    If Not Selection Is Nothing Then
        If TypeName(Selection) = "Range" Then
            If ActiveSheet.AutoFilterMode Then
                ActiveSheet.AutoFilterMode = False
            End If
            Selection.AutoFilter
        End If
    End If
End Sub

Answer 3

First off, you can't expect macros in a workbook of xlsx format. Make sure that you have eikther xlsm or xlsb as a source. This gets us to the second necessity. The workbook must have been saved to have such a format. You can't call a macro from an open workbook that hasn't been saved (because it doesn't have any format yet).

This is the correct syntax for calling an existing macro in another workbook.

Sub Test_TestCall()
    Application.Run "'Personal.xlsb'!Method1"
End Sub

Add arguments to the call in brackets in the sequence required by the called procedure.

You may find it easier, however, to simply set a reference to the other workbook. Here is a step-by-step guide how to set a reference. Once a reference is set you can call all macros in the other project as if they were within the calling workbook. The reference gets saved with the workbook and will still be there when you next open the workbook. The drawback of this (and any other method) is that you can't send a working copy of the calling workbook to third parties unless you send the referenced workbook as well.

The error you get has yet another reason. The Selection is made by the user in the ActiveWorkbook, and since you don't tell Method1 which workbook is active it wouldn't be able to find it, right? However, this problem is best solved by following the most basic of all programming rules: "Avoid the Selection object!" Use the Range object instead. If you absolutely must use the Selection` object then pass it to your procedure as an argument.

Application.Run "'Personal.xlsb'!Method1(Selection)"
' and
Sub method1(MyRange As Range)
    Dim rTable As Range
    MyRange.AutoFilter
End sub