3

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 same 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.

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Martin Dimitrov
  • 4,796
  • 5
  • 46
  • 62

1 Answers1

1

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...)

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks for the answer but it doesn't seem to work in my case. Is there anything different in your code? I wonder how to check the return value of `oVBC.Import`... – Martin Dimitrov Sep 27 '11 at 18:17
  • Import should refturn a reference to the imported module. You should be able to test if it's empty using 'If CM Is Nothing Then...' Not sure why it doesn't raise an error when it fails though. – Tim Williams Sep 27 '11 at 18:48
  • I even tried to call the import function 15 secs after workbook load as `Application.OnTime Now + TimeValue("00:00:15"), "ThisWorkbook.ImportCode"`. It is called but then again the importing doesn't work. Anyway, thanks again and +1 for the suggestion. – Martin Dimitrov Sep 27 '11 at 18:49
  • Put your ImportCode in a regular module. Also, see discussion here: http://stackoverflow.com/questions/704759/source-control-of-excel-vba-code-modules – Tim Williams Sep 27 '11 at 18:54
  • Putting it is a regular module doesn't fix it. I officially hate VBScript. :) – Martin Dimitrov Sep 27 '11 at 19:02
  • It works for me, so I'm not sure what the problem is for you. Did you check "trust access to VBProject" in the Excel options? Since you hate vbscript, it's a good thing you're not using it... – Tim Williams Sep 27 '11 at 20:42
  • Yes, I did. The other thing is 'If CM Is Nothing Then...' fires 'type mismatch' error. Thanks for the effort, though. – Martin Dimitrov Sep 27 '11 at 20:55
  • I ended up using a method I found in the resource you pointed me to so thanks again. – Martin Dimitrov Oct 05 '11 at 13:58