Distribute updated Excel VBA code to Multiple End-Users

Question

I've created an Excel 2010 workbook with a number of sheets. VBA code that performs various data manipulations is in a couple of modules, and also attached to a couple of forms. The workbook is being distributed to a couple dozen people in different departments to use. They will be populating their workbook with their own department-specific data.

If I need to distribute an update to the code (either a bug fix or some new function), how can that be done? I don't want the users to have to reenter or copy/paste all their data into the 'new' workbook - I'm essentially looking for a method to update the VBA Project that's inside their existing workbook.

Answer 1

You could create an additional helper workbook Help.xlsm, that has its file attributes set to read-only.

Move all the vba code, that you might need to change in the future, into Help.xlsm

The file that you distribute then needs a reference adding to Help.xlsm

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

Above assumes all your customers are on the same network so that you can store Help.xlsm somewhere that is accessible to all their files.

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

Answer 2

You would need to export the modules and forms and manually import them into the existing workbooks. I used to have to do this for some projects i worked on.

Alternatively you would need to write some helper code to import the old data into a newly published workbook, but this depends on how the data is organised of course. Again this is another approach I took for a different project.

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