Centralized code for multiple workbooks
Posted by Larry Jones on March 06, 2001 11:06 AM
I would like to have one .bas file, that 10 different workbooks would import to allow me to centralize my code into one location instead of having to make changes to all the workbooks whenever something comes up.
This .bas file contains all the sub/functions used in the workbook, including all the open, newsheet, activate, etc. type of functions that may be triggered within the workbook.
In the workbook module (Workbook_SheetActivate for example) I redirect the subroutine to a subroutine in the standard module called wkbSheetActivate where I pass it the sheet passed into the Workbook_SheetActivate subroutine. I have this for every Workbook level method that needs some code behind it.
In the Workbook_Open method, I have tried putting code to delete the current standard module (named TimeTracking) and import a new standard module and name it TimeTracking, but I started getting problems with name conflicts and the such.
So now, I am simply trying to delete all the code in the TimeTracking module and use the AddFromFile method to put the code back into it from the .bas file. But this is causing Excel97 to crash!
Can this really be done and if so, how, or what am I doing wrong????
Thanks for any help.
Here is some of the code I am attempting, including the line causing the crash.
' Get the modified date/time from the master .bas file, put a quote in front to make it a comment
strBASModified = "'" & FileDateTime(strBASFullPath)
' Reference the VBcomponents, to be able to access the module
' Get the first line of the TimeTracking module, which should be the commented last modified date/time
strModModified = .Item("TimeTracking").CodeModule.Lines(1, 1)
' If the module date/time does not match the file date/time then the module needs to be updated
If strBASModified <> strModModified Then
Application.StatusBar = "Master code module updated, importing changes..."
' Reference the code module
' Delete all lines in the code module
.DeleteLines 1, .CountOfLines
' Add the comment containing the file date/time modified
' Add the code from the file
.AddFromFile strBASFullPath ' **** Crashes here!
Application.StatusBar = "Master code module updated, import complete..."