Code in Another File with Excel VBA

Below is an example illustrating how to work with procedures and functions located in a different Excel file (workbook). For this to work, the other workbook must be open in Excel.

First, the following code is located in Module1 of the current workbook:

Sub ModularOtherFile()
    ' Open the other workbook located in the same folder as the current workbook
    Workbooks.Open ThisWorkbook.Path & "\Mappe5Mod.xlsm"   
    ' Call a procedure in the other workbook, specifying module and parameters
    Application.Run "Mappe5Mod.xlsm!Modul1.Summe5", 3, 5   
    ' Call a function in the other workbook and display the returned result
    MsgBox "Summe 6: " & Application.Run("Mappe5Mod.xlsm!Modul1.Summe6", 3, 5)   
    ' Call a procedure in the class module of the other workbook
    Application.Run "Mappe5Mod.xlsm!Tabelle1.Summe7", 3, 5   
    ' Call a function in the class module of the other workbook and display the result
    MsgBox "Summe 8: " & Application.Run("Mappe5Mod.xlsm!Tabelle1.Summe8", 3, 5)  
    ' Close the other workbook after execution
    Workbooks("Mappe5Mod.xlsm").Close
End Sub

In the other workbook named Mappe5Mod.xlsm, within Module1, the following procedure and function are defined:

Sub Summe5(a As Integer, b As Integer)
    MsgBox "Summe 5: " & (a + b)
End Sub

Function Summe6(a As Integer, b As Integer) As Integer
    Summe6 = a + b
End Function

Additionally, in the class module associated with the worksheet object Tabelle1 of Mappe5Mod.xlsm, the following procedure and function are defined:

Sub Summe7(a As Integer, b As Integer)
    MsgBox "Summe 7: " & (a + b)
End Sub

Function Summe8(a As Integer, b As Integer) As Integer
    Summe8 = a + b
End Function

Explanation:

Procedures and functions located in code modules of other workbooks can only be called if that workbook is open in Excel. Therefore, the example code first opens the workbook, executes the calls, and finally closes it.

If the workbook is already open, this will not cause any issues, assuming the code in the other workbook has not been modified during execution.

In this example, it is assumed that the other workbook is located in the same directory as the current one. To facilitate this, the path of the current workbook (ThisWorkbook.Path) is used to build the full file path.

To invoke procedures or functions in another workbook, the Application.Run() method is used. It requires a string as its first argument, which must follow this format:

« <WorkbookName>!<ModuleName>.<ProcedureOrFunctionName> »

Additional parameters (if any) follow after the string.

For example, calling a procedure looks like this:

Application.Run « <WorkbookName>!<ModuleName>.<ProcedureName> », Parameter1, Parameter2, …

Calling a function looks like this:

Result = Application.Run(« <WorkbookName>!<ModuleName>.<FunctionName> », Parameter1, Parameter2, …)

One important limitation to note: Functions defined inside class modules of another workbook, such as Summe8(), do not return values when called via Application.Run(). This is a restriction of the method and how VBA manages class module members across workbooks.