How to import VBScript macros into Excel workbook?

I have several Excel workbooks. They all share the same macro modules. What I would like to achieve is when editing one module in one workbook not to have to edit the s开发者_如何转开发ame module in the other workbooks.

Naturally, my first step was to export on save the modules in .bas files. But the problem is that I cannot import them on load.

I had tried this:

Private Sub Workbook_Open()
    Set objwb = ThisWorkbook
    Set oVBC = objwb.VBProject.VBComponents
    Set CM = oVBC.Import("C:\Temp\TestModule.bas")
    TestFunc
End Sub

There is a TestModule.bas in the same dir with content:

Function TestFunc()
    MsgBox "TestFunc called"
End Function

When the workbook is opened, a compile error appears: Sub or Function not defined. If I manually import the module everything works just fine.

Thanks for any advice.

Like you, I couldn't get the import to work from the workbook_open. You could put your import code in a sub a separate module, and call it from your workbook_open like this:

Private Sub Workbook_Open()
    Application.OnTime Now, "ImportCode"
End Sub

That seemed to work for me (a direct call did not...)