» Insert a new module from a file using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions With the macro below you can easily insert new modules with contents in a workbook.
This requires that you have created the new module previously in another (temporary) workbook.
Export the finished module to a text file by rightclicking the module name and select Export file... in the shortcut menu.

Sub InsertVBComponent(ByVal wb As Workbook, ByVal CompFileName As String)
' inserts the contents of CompFileName as a new component in wb
' CompFileName must be a valid VBA component suited for 
' import (an exported VBA component)
    If Dir(CompFileName) <> "" Then 
        ' source file exist
        On Error Resume Next ' ignores any errors if the project is protected
        wb.VBProject.VBComponents.Import CompFileName 
        ' inserts component from file
        On Error GoTo 0
    End If
    Set wb = Nothing
End Sub

Example:

InsertVBComponent ActiveWorkbook, "C:\FolderName\Filename.bas"