VBA Express : Excel - Browse & Import Code Modules (Via Macro)

Excel

Browse & Import Code Modules (Via Macro)

Ease of Use

Easy

Version tested with

2000, 2003

Submitted by:

johnske

Description:

A window to browse through folders for the module you want to import will appear. On selecting the required module, it will be imported, a message will then appear that confirms the import and asks if you want to import any more.

Discussion:

Saves going into the VBE window all the time to import modules, plus, this can be used in a procedure as a type of 'error' message to request the user to import any code that may be missing from a workbook.

Code:

instructions for use

			Option Explicit 
 
Sub ImportCodeModule() 
    Dim Filt$, Title$, FileName$, Message As VbMsgBoxResult 
    Do Until Message = vbNo 
         'type of file to browse for
        Filt = "VB Files (*.bas; *.frm; *.cls)(*.bas; *.frm; *.cls)," & _ 
        "*.bas;*.frm;*.cls" 
         'caption for browser
        Title = "SELECT A FOLDER - CLICK OPEN TO IMPORT - " & _ 
        "CANCEL TO QUIT" 
         'browser
        FileName = Application.GetOpenFilename(FileFilter:=Filt, _ 
        FilterIndex:=5, Title:=Title) 
        On Error GoTo Finish '< cancelled
        Application.VBE.ActiveVBProject.VBComponents.Import _ 
        (FileName) 
         'finished?
        Message = MsgBox(FileName & vbCrLf & " has been imported " & _ 
        "- more imports?", vbYesNo, "More Imports?") 
    Loop 
Finish: 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Save your work

Test the code:

Select Tools/Macro/Macros.../ImportCodeModule/Run
After making the import, go to Tools/Macro/Macros... and check the macro is there, OR,
Go back into the VBE window and look for the code module in the Project Explorer.
(NOTE: This requires that some modules to import actually exist somewhere on your PC, export some known ones to test)

Sample File:

ImportCodeModule.zip 7.97KB

Approved by mdmackillop

This entry has been viewed 265 times.