VBA TIPS
Importing a Module then run a procedure in it

The modules in VBE can be saved as files. For example, a standard module can be saved as a .bas file.

RIght click the target module then select Export File....



Assuming a standard module named Module1 has been saved as Module1.bas at C drive, Module1 has a procedure named TestRun. This program would import the file from C:/Module.bas and run the procedure named TestRun.

To transfer control to a Sub procedure named TestRun, usually we can use Call Statement. But in this case there is no procedure that has such a name until Module1 is imported. So if we use Call Statment, VBA would return a Compile error: Syntax error.

To avoid this matter, we can use OnTime Method instead of Call Statment.

Place the following in the standard module.

Option Explicit

Sub ImportAndRunBasFile()
'Assume Sub TestRun() is included in Test.bas
    With Application
        .VBE.ActiveVBProject.VBComponents.Import "C:\Module1.bas"
        .OnTime Now, "TestRun"
    End With
End Sub



| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo