I've spent endless time on removing/replacing Code Modules in order to find out what triggers works versus doesn't work. My implementation in Code Module Management Workbook covers:- Cleanup, Export, Import, Remove, Transfer, and Synchronize for any number of Code Modules via a user interface.
- Closes any target Workbook opened prior opening CoMMa.xlsm and re-opening it by preventing any macro is executed. That is because once any procedure had been executed the removal of a module will not take place before the procedure which removed the module has finished. A subsequent import considers the module still existing and provides a numeric suffix to make its name unique.
- When the opened Workbook's VBA Project is password protected, VBE has to be opened manually in order to provide access to the Code Modules. This is possible after open and before selection.
- Data Modules (i.e. the code in 'ThisWorkbbok' and any Worksheet) can be replaced just as any other module.
- With Synchronize and Transfer a Worksheet yet not existing in the target Workbook is added (without creating any back-links!)
- When Synchronize with the option Changed Only is selected, any UserForm is included in the list of selected Code Modules by default, because there is no way to obviate a design change.
- Undo when the wrong target Workbook had been selected accidentially
For those heading for their own implementation of a Code Module Managment means the following two key procedures may be of interest.
The first one can be copied from the Test-Workbook CoMod.xlsm).
The function CoMReplaceLbL in the Class Module clsCoMods replaces a Code Module in a target Workbook line by line by one with the same name whereby the source for the new code may be provided either through an export file (ends with .bas or .cls) or through a named Code Module in a source Workbook.
The function returns a vbNullString or a short error message. It is well tested (and so you can do as well) by the above Test-Workbook.
When the function is copied to your own project, please notice that Trust access to the VBA project object model and a Reference to Microsof Visual Basic for Applications Extensibiltiy 5.3 is required.
With the function incorporated in a VBA project the project may update its own code (excluding the Code Module the function CoMReplaceLbL resides in).
The second is a procedure which copies a Worksheet from a source Workbook to a target Workbook without any back-links to the source Workbook:
Bookmarks