Import a module from a file using VBA in Microsoft Excel

In this article, we will create a macro to import a module to the active workbook.

We will be using Filename.bas as a module, which we want to import.

Click on the insert button to import the module from Filename.bas to the active workbook.

ArrowMain

Workbook contains only one module “MainModule”.

ArrowMainModule

After running the macro, a new module will be imported from Filename.bas

ArrowOutput

Logic explanation

In this article, we have created two macros, InsertVBComponent and Calling_Procedure

InsertVBComponent

It takes the workbook name and file name as inputs, and imports the module to the workbook.

Calling_Procedure

It is used to call the InsertVBComponent macro with the active workbook and Filename.bas as inputs.

Code explanation

If Dir(CompFileName) <> “” Then

Above code is used to check whether the file name exists in the directory.

wb.VBProject.VBComponents.Import CompFileName

Above code is used to import module to the active workbook.

 

Please follow below for the code

Option Explicit

Sub InsertVBComponent(ByVal wb As Workbook, ByVal CompFileName As String)

' Inserts the content of CompFileName as a new component in workbook
' CompFileName must be a valid VBA component suited for
' import (an exported VBA component)

'Checking whether CompFileName file exists
If Dir(CompFileName) <> "" Then
    
    'Ignore errors
    On Error Resume Next
    
    'Inserts component from file
    wb.VBProject.VBComponents.Import CompFileName
    
    On Error GoTo 0
End If

Set wb = Nothing

End Sub

Sub Calling_Procedure()
    
    'Calling InsertVBComponent procedure
    InsertVBComponent ActiveWorkbook, "C:\Users\Ramandeep\Desktop\Filename.bas"
    
End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Users are saying about us...

  1. You should indicate how to handle the condition when the module already exists. The new module will have a different name and the old one will remain.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube