Edit Question - KnowledgeHub

Title Body

I'd like to be able to source control my Excel spreadsheet's VBA modules (currently using Excel 2003 SP3) so that I can share and manage the code used by a bunch of different spreadsheets - and therefore I'd like to re-load them from files when the spreadsheet is opened. I've got a module called Loader.bas, that I use to do most of the donkey work (loading and unloading any other modules that are required) - and I'd like to be able to load it up from a file as soon as the spreadsheet is opened. I've attached the following code to the Workbook_Open event (in the ThisWorkbook class). Private Sub Workbook_Open() Call RemoveLoader Call LoadLoader End Sub Where RemoveLoader (also within the ThisWorkbook class) contains the following code: Private Sub RemoveLoader() Dim y As Integer Dim OldModules, NumModules As Integer Dim CompName As String With ThisWorkbook.VBProject NumModules = ThisWorkbook.VBProject.VBComponents.Count y = 1 While y <= NumModules If .VBComponents.Item(y).Type = 1 Then CompName = .VBComponents.Item(y).Name If VBA.Strings.InStr(CompName, "Loader") > 0 Then OldModules = ThisWorkbook.VBProject.VBComponents.Count .VBComponents.Remove .VBComponents(CompName) NumModules = ThisWorkbook.VBProject.VBComponents.Count If OldModules - NumModules = 1 Then y = 1 Else MsgBox ("Failed to remove " & CompName & " module from VBA project") End If End If End If y = y + 1 Wend End With End Sub Which is probably a bit overcomplicated and slightly crude - but I'm trying everything I can find to get it to load the external module! Often, when I open the spreads

Tags (comma-separated) Cancel