MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
With Application.VBE.ActiveVBProject.VBComponents
' 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
With .Item("TimeTracking").CodeModule
' Delete all lines in the code module
.DeleteLines 1, .CountOfLines

' Add the comment containing the file date/time modified
.AddFromString strBASModified

' Add the code from the file
.AddFromFile strBASFullPath ' **** Crashes here!

Application.StatusBar = "Master code module updated, import complete..."
End With
End If
End With


Posted by Dave Hawley on March 06, 2001 11:17 AM

' Reference the VBcomponents, to be able to access the module With Application.VBE.ActiveVBProject.VBComponents ' 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 With .Item("TimeTracking").CodeModule ' Delete all lines in the code module .DeleteLines 1, .CountOfLines ' Add the comment containing the file date/time modified .AddFromString strBASModified ' Add the code from the file .AddFromFile strBASFullPath ' **** Crashes here! Application.StatusBar = "Master code module updated, import complete..." End With End If End With

Larry, I think you need to save after deletion of your module and BEFORE importing the .bas file, this should stop any name conflict.


Dave
OzGrid Business Applications

Posted by Dave Hawley on March 06, 2001 11:26 AM


As an after though Larry, if you want easy access to Worksheets complete with Event code, maybe you could save some sheets as Worksheet templates that already have this code pre-written.

Dave
OzGrid Business Applications

Posted by Larry Jones on March 06, 2001 12:39 PM

Save the workbook? I didn't see a way to save the changes to a module. So it sounds like I need to save the entire workbook after deleting the lines, then probably again after inserting the new lines?

Ouch.

I was hoping I could keep this at the module level, but I'll give it a try.