SUMMARY
The VBA Updater is a tool that allows you to import VBA code from one excel sheet into another and thereby helps working around the problems that are caused by the coupling of code and data in one Excel workbook.

DOWNLOAD
VBAUpdater.xls

THE PROBLEM

When working on an MS Excel VBA project that is already in use, the fact that VBA code and data both reside in the same document may pose a problem:
To add or update functionality you have to work on the code, but the user may not want to give you the whole document, since it contains business-critical data. To work around this problem, the user can delete all his data, send the document to you and after receiving a fixed version he can copy his data back. However, depending on the amount of data that can be a cumbersome task.

The aggregation of code and data in MS Office documents becomes a more serious problem if multiple copies of the document are used by different users. Certainly you wouldn’t want to fix the code in every user’s document.

THE SOLUTION

The functionality of importing and exporting code into and from MS Office documents can help to solve this problem. Imagine the following scenario:

1. A user discovers a bug in the VBA code and sends a report to you.
2. By looking at your code you confirm the bug and fix it your copy.
3. You send your copy (containing the fixed code but only test data) to the user.
4. The user stores the file you sent him somewhere on his harddisk.
5. The user uses some tool to replace the buggy code with the one from your document.
6. Everyone lives happily ever after.

The VBA Updater that makes above solution possible for MS Excel documents.

How to use it

1. Open VBAUpdater.xls
2. Select the source .xls file (containing the new code)
3. Select the target .xls file (containing the precious data and the buggy code)
4. Click “Update”, read the message carefully and then click “Ok”
5. The VBA Updater opens the source and the target document. It removes all code from the target document and replaces it with the one from the source document. When done, another message box is shown.
6. The target document now contains the new code.

HOW IT WORKS

The VBA Updater removes all VBA code and forms from the target document. Then it cycles through all modules and sheets in the source workbook. All modules, class modules and forms are copied to the target document. If a sheet has code attached, the code is copied to the according sheet of the target document. If that sheet does not exist in the target document, it will be copied from the source document. This means that:
– all VBA code of the target workbook will be lost, so take care
– sheets that have no code attached are not copied to the target document
– no data or formulas in the sheets of the target document are touched

The VBA Updater will access the VBA Object in MS Excel. Probably the user will have to change security settings to make the VBA Updater work. If the options are not set correctly, the user will be prompted with the following message when opening VBAUpdater.xls:

I have to confess, I blatantly stole the code that does that from http://www.j-walk.com/ss/excel/tips/tip96.htm.

LAST WORDS

Though the VBA Updater comes with no warranty, if you experience any problems feel free to drop me a mail, post a comment or yell.