How to best manage multiple workbooks with common VBA code routines?

[bnj1776]

Hello,

I'm currently up to six workbooks (add more soon) that work together and share a number of functions.

My question is how can I best manage updates and distribute changes to those functions to each of the workbooks?

Currently I update the "master workbook" and then manually copy to all the others, or at least that is what I'm trying to do.

Or is there some magic way to call a function that exists in another workbook?

Before I reinvent the wheel sorting this out, are there any suggestions for how to manage this or tools to use?

Thank you.
Brian

 

[RoryA]

I'd use an add-in. Then all you need to do is update the add-in when required - one location for code, one change each time.

 

[bnj1776]

I'd use an add-in. Then all you need to do is update the add-in when required - one location for code, one change each time.

Awesome. Now I just have to learn how to do that. Any suggestions for someone new to Add-In's?

 

[RoryA]

Essentially you move the code into a new workbook and then save that as an add-in. How complicated it gets after that depends on how your workbooks use the addin. If it's just formulas in cells, you simply alter the formulas to use the add-in version. If there are macros involved it becomes more complicated as you need to set up a menu system.

 

[bnj1776]

LOL. Nothing simple here!

I'm creating unique estimates from a number of workbooks.

Estimator (main program workbook)
Data (data file workbook)
Customers workbook
Template (required worksheets for each new estimate)
Module X (required worksheets for just Module X)
Module Y (required worksheets for just Module Y)
Module Z (required worksheets for just Module Z)
etc... (we are looking at nearly 30 different modules)

The Data file is the root of the new estimate.
I rename it and copy in the customer info and template to begin with.
Then the modules are added as needed allowing for as many copies as needed as well (I rename every worksheet & table and update the formulas with the VBA import process)

When done the Estimate workbook has no links to any other workbook, it is self contained.

At least that is the goal, I'm having an issue with one link that shows it is updated during the process but remains anyways. Grrrr.

------

I have no idea how an Add-In works at this point, so I'm not even sure how it could fit into what I'm doing above.
Sounds like it is certainly worth looking into though.

Any crazy good Add-In info sites out there that you would recommend?

Thank you

 

[RoryA]

If the goal is for the workbooks to be completely self-contained, the add-in approach won't work anyway, unless your final estimate workbook doesn't use any code functions.

 

[bnj1776]

The estimate workbook will still be able to pull in and update modules as needed using VBA.

And later we will generate pdf files for contracts, purchase orders, etc. based on the information in the workbook.

 

[RoryA]

When you say modules, are you referring to code modules or something else?

 

[bnj1776]

Estimate modules, a workbook with specific worksheets to be added to the estimate.

 

[RoryA]

So what are the "functions" that you update and distribute between all the workbooks, exactly?