Étiquette : excel_vba

First, in the VBA Project Explorer, select the workbook into which you want to import the module.

Then, from the File menu, choose the Import File option. This action will open the Import File dialog box. Alternatively, you can access this dialog by right-clicking on the target workbook within the Project Explorer and selecting Import File from the context menu.

In the dialog, navigate to the folder containing the module you wish to import. Select the module file to import it into the chosen workbook.

You can identify standard code modules by their .bas file extension, and UserForm modules by their .frm extension.

In the VBA Project Explorer, first select the module you wish to export. This can be any module type—either a standard code module or a UserForm module.

Next, go to the File menu and choose the Export File option. This action will open the Export File dialog box. Alternatively, you can access this dialog by right-clicking on the module within the Project Explorer and selecting Export File from the context menu.

In the dialog, select the desired destination folder where you want to save the exported file. For standard code modules, the file will be saved with the extension .bas. For UserForm modules, the exported file will have the extension .frm.

When exporting a UserForm module, an additional file with the .frx extension is automatically saved in the same directory. This .frx file contains associated binary data such as images and control information necessary for the UserForm.

If you wish to delete a module, use the Remove option in the same File menu. To prevent accidental deletion, VBA will prompt you to ask if you want to export the module first. If you agree, the Export File dialog box will open, allowing you to save a backup before the module is removed from the project.

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.

First, let’s consider an example demonstrating how procedures and functions can be defined and used within the same VBA project file.

The following code is located in the standard code module named Module1:

Sub ModularSameFile()
    ' Calling procedure from another standard module
    Summe1 3, 5
    MsgBox "Sum 2: " & Summe2(3, 5)    
    ' Calling procedure and function from a class module associated with the worksheet object "Sheet1"
    Sheet1.Summe3 3, 5
    MsgBox "Sum 4: " & Sheet1.Summe4(3, 5)
End Sub

Next, in another standard code module named Module2, we have the following procedure and function:

Sub Summe1(a As Integer, b As Integer)
    MsgBox "Summe 1: " & (a + b)
End Sub

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

Finally, within the class module associated with the worksheet object named Tabelle1, the following procedure and function are defined:

Sub Summe3(a As Integer, b As Integer)
    MsgBox "Summe 3: " & (a + b)
End Sub

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

Explanation:

By default, procedures and functions defined in standard code modules have Public visibility, unless explicitly declared as Private Sub or Private Function. This means that the procedure Summe1() and the function Summe2() defined in Module2 can be directly called from anywhere within the VBA project, including from Module1 as shown in ModularGleicheDatei().

In contrast, procedures and functions within class modules (such as the one tied to the worksheet object Sheet1) have Private visibility by default. Consequently, the procedure Summe3() and the function Summe4() defined in the class module cannot be called directly by their name alone. Instead, you must prefix the call with the class module’s object name—in this case, Sheet1.—to access them. For example, you call Sheet1.Summe3 and Sheet1.Summe4 to invoke these members.

This distinction is important because it controls the scope and accessibility of code elements, ensuring encapsulation when working with object-oriented structures like class modules, while allowing more open access in standard modules.