DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How To Import a module from a file using VBA in Microsoft Excel?

#toimportamodulefroma #vbamacros

In this blog post, we are going to see how to import a module from a file using VBA in Microsoft Excel. Let’s get into this article!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

  • Firstly, you need to create a macro to import a module to the active workbook.

Create a macro

  • In the Excel Worksheet, you have to go to the Developer Tab.
  • Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic
Select Visual Basic

  • Now, you have to click on the Insert ** and select **Module and name it as MainModule.

Insert a module
Insert a module

  • After that, you need to copy and paste the code given below. 

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
Enter fullscreen mode Exit fullscreen mode
  • You have to save the code by selecting it and then close the window.

Save the Code

  • Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
  • Then, you need to choose the Macros option in the Code section.

Choose Macro option
Choose Macro option

  • Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

  • Finally, after running the macro , a new module will be imported from Filename.bas in VBA.

Verdict

In the above post, you can learn the simple steps ** ** on how to import a module from a file using VBA in Microsoft Excel. Kindly, share your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!

Read Ahead:

Top comments (0)