Change vba code only not the workbook?

Ghost

Try Don's tutorial .Make Your Macros Available in All Workbooks in Excel Also in VB Explorer you can Export a module (and use Import on the saved .bas module to add the macro to another workbook). 

Hi Ghost

I had a similar problem maintaining a system which I had written for a friend and developed some code to do this.

The things I think you need to consider:

• You may need to update their workbooks or their personal workbook.
• You will need to be able to select the VBA modules and Forms that you are updating.
• You will need to select a file containing the updates for each workbook.
• When your users receive the updates they will need to add them to the appropriate workbook.

It is not possible to just add the new modules as they will not overwrite the existing code.  The existing code needs to be deleted before the changed modules can be added.

The system I developed:

• Displays a form to allow selection of a target file
• Displays a form to query which workbook is being processed.
• Lists all of the modules and forms in that workbook and allows for modules not required to be removed from the list. Remaining items will be extracted.

Shipping the system if on a remote machine can either be achieved using Dropbox (or similar) alternatively files can be zipped and attached to an email. (Unloaded Form definitions consist of 2 files, One text (.FRM)  and one binary (.FRX))

When the user receives the updates to the system

• Displays a form to allow selection of the delivered file.
• Displays a form to query which workbook is being uptaed.
• Displays a form listing the contents of the delivered file, Items can be removed from the list if not required. Remaimimg items will be processed.
• The processing consists of:
  • Delete any modules prefixed with Z. (Possibly left from previous failure)
  • Rename existing items by prefixing with a Z.( It is not possible to just delete the modules)
  • Add the updated modules.
  • Delete the modules prefixed with Z
•  The components used to perform the update:
  • should be stored in the personal workbook
  • need to be updated manually.

The system was developed for my friend and I so may not conform to your idea of standards, but if you are interested in a copy I could send it.

I do not believe the form definitions could be sent as a file, but could zip it and email it to you.

It's in the VB Editor's Project Explorer window where you write your code. As you know, you can select an entire module and copy it to the clipboard. You can then paste the contents of the clipboard to a module in another workbook open at the time. The other workbook will now use the code you have pasted rather than whatever code (or none) there was before.

If you have access to both workbooks on your PC you can also drag modules from one project to another in the Project Explorer window. If a module by the same name already exists in the target project the new module will be named like "Module1(2)". I often delete the original module in such cases and rename the new one after import but deleting first saves the task of renaming. The important thing to know is that dragging will not move the module but create a copy.

This method will not work for the code modules of worksheets. There you do need to apply the Copy/Paste method first above described.

If you do not have access to both files on your machine or can't open them at the same time, perhaps because they have the same name, you can Export/Import code modules. Right-click on a code module's name in VBE's Project Explorer window and select Export. Follow the instructions to name and create a file. It will be a TXT file (2 files for forms). You can then use the Import procedure from the same menu to add that file to the VBA project on another machine. Again, if a module by the same name already exists there you need to delete and rename (or delete first).

An interesting variant of this method is that you can actually edit the TXT file before import using a text editor like Notepad. In the case of forms, the import procedure will load both files while you specify only one.