Despite *knowing better* I also did a very similar thing to allow many users to easily receive code updates (In fact Chip Pearson I’d like to thank you for your well organized website, with a few wrappers, I used a lot of your vba module copy, delete code to get the job done). In my case it works great, but I did note a few issues and can point out an annoyance or two that may be related to your problems. Specifically your issue may be related to your use of class modules since (argh!) the class module name IS the class name (but in your post you don’t mention compile errors occur so it may not be the issue noted below). Here is what I did and issues I found …

So as mentioned in the thread, there are two obvious choices – push code out to users workbooks or allow them to suck it in – a.k.a. pull (again, like you, I chose the latter, but generally as you’ve heard, the former is recommended). What I wanted was to keep the source in virtually a single workbook <fooVbaSrc.xls (with revision history) and have the users workbook (upon Auto_open) look for this book, and if available, check the version control sheet comparing to the current const version_no., if newer optionally allow an upgrade. [BTW - What I did not like about the push model, was that while I could have, upon update selection, run the source wb and shut down the user wb allowing the upgrade to occur via a push, if more than a single user wb was in the directory (which was the case), I’d somehow have to pass the target to the source wb or allow all wb’s to be updated]. Upon selecting the update I chose to ‘scrub’ the workbook of all modules and copy all modules from the src book to the user target (I ‘scrub’ and copy with out regard to module names that way the set of updated module names does known or coded a priori – allowing any and all code to be modified). I found two primary issues.

Firstly, as you scrub the workbook of current code modules they won’t actually be deleted until the book is closed and even then the user is prompted by excel as it exits to ‘Save the changes’ - at which point anything other than ‘Yes’ will leave the ‘deleted modules’ (excel probably needs extra flags to indicate the delete from the scrub was already pending and should complete regardless of user input as the book closes – but I wouldn’t hold my breath for that:-). Also, as the updated modules are copied across, excel renames them from <foo to <foo1 to prevent a module name collision (actually I noted that it will properly and immediately delete modules that have not been used thus far - this may answer your question as to why some and not all modules cause you grief). For me this renaming is not an issue for standard modules. With this mechanism, if the user selects yes to the save msgbox upon exit, the wb is properly updated, if the user selects ‘no’ to the excel closure prompt, then the wb is reverted back to the previous rev (i.e. the foo1 modules are not saved – unless you did so in vba then you are in real trouble as both sets of modules will be preserved (<foo and <foo1 each with the same functions and subroutine names) and vba will throw a ambiguous module compiler exception). BTW when things go right the next time <foo is copied to the user book it works fine since there is no module name collision with <foo1 (so the workbook module names oscillate between <foo and ,<foo1 on each subsequent update). [so you can really see the Excel is straining to tell you not to do the pull method – but we press on...]

Related to the first issue is the second issue I encountered. This occurred when using MS Excels semi-useful classes (no inheritance, no polymorphism and no c’tor/d’tor make them essentially data encapsulation object of marginal organizational use). The problem here was that the module name IS the class name. So, noting issue #1 above when the class name changes to <foo1 the code will hit a vba compile error (the object of class foo can not be instantiated). *Luckily* as noted above the class module feature is so weak, I did not really mind switching back and avoiding it's use.

Hope this helps,

Regards,
Paul Schmitt