How to Import, Export and Copy Module in VBA?

In the previous article, we learned the usage of Modules in VBA. Today, we will learn another important functionality to import and export the VBA module. We must learn to import modules from external sources to learn and experiment with new VBA code.

So let us start learning.

Importing and Exporting VBA Module

Importing the VBA module helps us to use the VBA code from external sources. There are several methods to do this and we have four ways to operate.

Exporting a VBA module enables us to share the module without needing to share the entire workbook project.

Method 1 – Copy and Paste the VBA Code

This simple approach is copying the code and then pasting it into VBA Editor. Let us say we have the following Sub Procedure to zoom in on a workbook’s worksheets to the same extent.

Sub zoom()
'zoom to fixed value for all worksheets of workbook
Dim x As Integer
x = InputBox("Enter zoom value")
Dim ws As Worksheet, Act_Sheet As Worksheet
Set Act_Sheet = ActiveSheet
For Each ws In Sheets
ws.Activate
ActiveWindow.zoom = x
Next ws
Act_Sheet.Activate
End Sub

To use this code, copy the code from above and then follow these steps.

• Take a new excel workbook and Press Alt and F11 keys to open the Visual Basic Editor.
• Select the Project corresponding to the new workbook from the Project Explorer and then go to the Insert tab on the ribbon. Insert a new VBA module from there.
• Open the code window for Module1 and paste the above code into it as follows.

Method 2 – Drag and Drop Module

Let us say we have a workbook containing a module that we wish to import in our own destination Workbook. For practice, we are providing a workbook containing a module named colorShadeGenerator as follows.

• Take a new excel workbook or open an existing workbook that becomes the destination workbook to import the VBA code.
• Download the above workbook and then this workbook.
• Open the VBE and you will see projects for the downloaded workbook and the destined workbook.
• Open the Modules section of the Downloaded workbook ColorShadeGenerator. Drag the module named colorShadeGenerator and drop it into the Destined Workbook project.

Now you will be able to use the code in Book1 also as the module is imported from colorShadeGenerator to Book1.

Method 3 – Import and Export Module

This is an easy way to share modules with other people. Modules in Excel have .bcf extension. Using this method, we can save the module as a .bas file on the PC where .bas stand for Basic Source File.

We will start to learn to export a VBA module from the Project.

• Open the workbook containing the module you wish to export and then open VBE.
• Go to the Project explorer and right-click on the module you wish to export and choose the option for Export File.

For instance, we have a module named lockCellsContainingFormulas that has a sub procedure that locks all the cells for editing containing formulas in the Active Sheet.

• Choose the path where you wish to save this module as a .bas file on your computer.

This module will be exported from this workbook to your PC and then you can share it with anyone now.

We have saved this module on the Desktop and this is how it appears.

You can also download this module from below.

You can import the Module from your PC in your workbook project by following these steps.

• Open the VBE and right-click on the Modules section of the Workbook Project where you wish to import the module.
• Choose the option to Import File. Locate the module on your computer and then click OK.

This brings us to an end.

Thank you for reading. ❤