Best practice for deploying a VBA system used in multiple locations?

[Chrisso]

Hi All

I have three different depots where I work and they each have an
instance of my Excel VBA system. My Excel VBA is generic to each depot
but of course the data is different.

I want to maintain my code in one location and distribute easily to my
three depots when I need to make a change.

Currently I have been doing this at the module level and manually.
That is, I choose one of the systems as the master - only make changes
there and then when I want to deploy this to the other two depots I
delete all their modules and replace them with copies from the master
system This is slow, laborious and prone to error.

What is the best way for me to maintain one copy of my system yet
deploy it easily to all my different locations?

I thought that AddIns might be the way to go but my system does not
use any menus or command bars and I dont have time to change this.

The other thought I have had is to use Rob Bovey's CodeCleaner as I
beleive that this can be used to automate my current manual process.

Any ideas? What is the best practice?

Thanks for any ideas.
Chrisso

 

[Zone]

One way is to use one workbook for your code modules and a different
workbook for your worksheets and data. The workbook with the code can be
set so when it opens it automatically opens the workbook with the
worksheets. Then, to update the program, you need only distribute the
workbook with the code modules. This creates its own set of problems
because the code is constantly referring to sheets in a different workbook.
This can be made somewhat easier by using global variables for the sheets.
Sounds like you are far enough along that rewriting your code to do its work
on a second workbook would be a real chore. But it could be worth it in the
long-run, especially if you have time to work on it as time allows, for
future distribution. James