[VBA] How to Manipulate VBA Modules with Macros (Export/Import/Remove)

Introduction

Have you ever wanted to manipulate the VBA project itself (modules, forms, etc.) using macros? For example, you might want to “import a frequently used general-purpose module into another workbook with a single button press” or “automatically create backups of modules under development.”

By accessing the VBProject object in VBA, you can automate operations on the VBA project itself. In this article, I will explain three basic methods using VBProject: exporting a standard module to a file, removing it from a project, and importing it from a file.

Important: Prerequisite – Enable Access to VBA Project

To run macros that manipulate VBProject, you must change Excel’s security settings to enable “Trust access to the VBA project object model.”

1. Open the File tab in Excel and go to Options.
2. Click Trust Center > Trust Center Settings.
3. Open Macro Settings and check the box for “Trust access to the VBA project object model.”

If this setting is not enabled, an error will occur when you try to access VBProject.

1. Exporting a Module

This saves a specified standard module as a text file with the .bas extension.

Sample Code

' Export a standard module named "Utils"
Sub ExportModule()
    Dim exportFilePath As String
    
    ' Specify the destination path and file name
    exportFilePath = ThisWorkbook.Path & "\Utils_Backup.bas"
    
    ' Write the module to the specified path using the .Export method
    ThisWorkbook.VBProject.VBComponents("Utils").Export Filename:=exportFilePath
    
    MsgBox "Module 'Utils' has been exported."
End Sub

Explanation

• ThisWorkbook.VBProject: Refers to the entire VBA project of the current workbook.
• .VBComponents("Utils"): Specifies the module named “Utils” from the components included in the project.
• .Export: The method used to write the specified component to a file.

2. Removing a Module

This deletes a specified standard module from the project. Please execute this with caution, as this operation cannot be undone.

Sample Code

' Remove a standard module named "TempModule"
Sub RemoveModule()
    ' Remove the specified module from the project using the .Remove method
    ThisWorkbook.VBProject.VBComponents.Remove _
        VBComponent:=ThisWorkbook.VBProject.VBComponents("TempModule")
        
    MsgBox "Module 'TempModule' has been removed."
End Sub

Explanation

• .VBComponents.Remove: The method used to delete a component.
• You must pass the component object itself to the VBComponent:= argument.

3. Importing a Module

This adds a .bas file to the project as a new standard module.

Sample Code

' Import a .bas file as a new module
Sub ImportModule()
    Dim importFilePath As String
    
    ' Specify the path of the file to import
    importFilePath = ThisWorkbook.Path & "\Utils_Backup.bas"
    
    ' Add the specified file to the project using the .Import method
    ThisWorkbook.VBProject.VBComponents.Import Filename:=importFilePath
    
    MsgBox "Module has been imported."
End Sub

Explanation

• .VBComponents.Import: The method used to bring a file into the project as a component.

Summary

In this article, I explained how to programmatically manipulate VBA modules using the VBProject object.

• .Export: Write a module to a .bas file.
• .Remove: Delete a module from the project.
• .Import: Bring a .bas file in as a module.

By combining these functions, you can create highly advanced macros, such as equipping your custom tools with an “auto-update feature for library modules” or automating project version control, thereby streamlining the VBA development process itself.