Distribute updated Excel VBA code to multiple end users

Question

I created an Excel 2010 workbook with several sheets. VBA code that performs various data manipulations is located in several modules, and is also attached to several forms. The book is distributed to several dozen people in different departments. They will fill out their book with their own data pertaining to specific departments.

If I need to distribute an update for the code (bug fix or some new function), how can this be done? I don’t want users to have to re-enter or copy / paste all their data into the “new” workbook - I am basically looking for a method to update the VBA Project, which is inside their existing workbook.

Answer 1

You can create an additional Help.xlsm helper reference book that has read-only file attributes.

Move all the vba code you may need to change in the future to Help.xlsm

Then the file you distribute needs a link adding to Help.xlsm

Now in the future, changes can be made to Help.xlsm , and they should appear in the client files.

It is assumed above that all your clients are on the same network so that you can store Help.xlsm somewhere, accessible for all your files.

This article explains this better than me: http://www.excelguru.ca/content.php?152-Deploying-Add-ins-in-a-Network-Environment

Answer 2

You will need to export modules and forms and manually import them into existing books. I had to do this for some of the projects that I worked on.

Alternatively, you will need to write some helper code to import old data into a recently published book, but it depends on how the data is organized, of course. Again, this is a different approach that I took for another project.

You can also do this procedurally. I used this for small patches. http://cpearson.com/excel/vbe.aspx