Update VBA code in multiple workbooks

Making an Excel application with VBA for yourself to ease your workload is great.
Making an Excel application with VBA for your office neighbor is even greater, you get good feedback and everyone is a winner!
Making an Excel application with VBA for two office neighbors is also great but now you have two places to update and bugs must be fixed on both.

still good success, the word spreads and your Excel/VBA capability is now a well known resource.

Making an Excel application with VBA for the entire office works fine in the beginning but as the number of users increase, so do the number of bugs discovered and the number of updates needed...

3 or 4 workbooks is still manageble but updating the VBA code and testing is now a real chore.

Time to pull the emergency brake and tell people that we need at least a contractor to set up an Access Database for us! Of course the emergency brake is not connected to anything and no one is interested in listening, let alone spend money. "It works fine, just fix the bugs. And oh, by the way, here is another feature I want you to implement..."


Does anyone recognize this situation? Is there a way to manage and update VBA code in multiple workbooks?

Hi,

I would suggest that you put all common code into an add-in rather than replicating it in individual workbooks. You can then either redistribute the add-in when changes are required, or store it in a central location so you only have to update that one file.

I figured that Add-in would come up. I kind of know what it is but I never used it. I have a bunch of buttons in the sheet and I also use Events (selection change) although.

Can I still use buttons on the sheet or do I have to make some kind of banner setup? Would the Event thing work?

Hi Jacc,

I'm with you, never have converted my code to an Add-In. AND - I've written a lot of code...

Searching for what xlinitwit thinks is easy I find one of my favorite sites at:

http://www.cpearson.com/Excel/CreateAddIn.aspx

good luck!! Let me know if it is easy and I may try it. I've done exactly what you describe above. When too many people are using your code and want more features, you turn into a programmer and stop doing your real work. My suggestion is to be happy and "don't burn out". Keep learning!!!

An add-in is basically just a workbook with its IsAddin property set (by saving as an .xlam file).

You can't use buttons on a sheet in the add-in since it's a hidden workbook. You may be able to use buttons in the workbook(s) to simply call code in the add-in, and the same for your events. Alternatively you can use application events in the add-in to trap events in any open workbook.

Maintaining a centrally stored add-in takes a little initial setup but makes life much easier thereafter- http://www.excelguru.ca/content.php?...rk-Environment

Alternatively, it is possible to update them using login scripts if you know a friendly network admin.

Without any specific information as to what it is that the code does, I am not really in a position to say which one- if either- makes more sense.

The Excel application in case is a basic purchase-order-pdf-generator. Data is added to a list and a pdf is can be generated based on a data line and some details filled in later in another sheet. The list also serves as a log, conditional formatting is used to warn about parts not having arrived at the specified date etc. Buttons are used for many things, copy previous lines, generating pdf, timestamp etc.

Argument for keep working on the current setup is that it is not a central database, it's just used in a project and when the project is finished that version of the Excel app is forgotten. A new project is a fresh start with the latest revision. So far it's working pretty well and I'm happy with the userinterface side of it.

I guess what I'm really looking for now is a way to compare code in different workbooks and a way of updating that code in a smooth way.