Delete code module and import same name

Hi All..
I have a file used throughout the world, with code modules. When an upgrade is needed, I want to remove a module then import a new one (well actually like qty 7). I have the new modules on a sharepoint site and mapped drive to them.

This is what seems to happen... In the code below, I delete the module, then import the same module name while getting no errors. But, the imported module has a new name, but with a "1" at the end - it seems that in the Project Explorer window, even though the old module was deleted, it still shows. So, the new imported module gets the same name with the 1 after it. If I break the code after deleting the module, save and exit, then re-open, the original module is actually gone. Then when I import the new one, it imports with the correct name. Seems like the project explorer window need to be "Refreshed" after the delete to see that the module was deleted so I can import with the correct (unused) name.

Any way to refresh / rebuild the vb components so the newly imported module is imported with the correct name?

Please Login or Register  to view this content.

Hi there,

For what it's worth, the following code seems to work correctly on my system (Excel 2016):

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements.


Hope this helps.

Regards,

Greg M

Thanks for the reply... but that code is the same as mine with the same result. I tried it on a 32bit system and also on my RDC which is 64bit. I guess I can add to the workbook open a check for module names, and if any end in 1, then I could rename? Can I rename a module?

Hi again,

Thanks for that feedback.

As I can't replicate your problem I obviously can't test my suggestions, but the following approach might be worth trying:

run a routine which deletes the old VBA CodeModules;
run a routine which uses the OnTime method to immediately (or after e.g. one second) run a routine which imports the new VBA CodeModules;
allow the importing routine to run.

I have often found that using the OnTime method in this way allows Excel to "catch its breath" between operations and achieve the required results.

Hope this helps.

Regards,

Greg M

This was strange. I could delete the modules thru the immediate window, no problem. But in the udf it would not do it, as far as I saw, without closing the file. I tried to rename each module before import (that worked) and the import worked. But, after the import, I still could not delete the renamed modules. SO, my workaround is to edit every module... Delete all lines, then read the .bas and insert each line, in each module:

Please Login or Register  to view this content.

Hi again,

Many thanks for that very useful follow-up information.

Regards,

Greg M

Further followup.... Please comment on this... I read somewhere something about if a variable is set to something, the something is deleted and the variable still exists, bla bla bla. Not sure about this, but my solution works regardless of the created VBComp variable.

set the vairable to the module
rename the module
delete the renamed module
import the new module with the same name as the original module. it Works!

Please Login or Register  to view this content.

BOOM!

The method using OnTime suggested by Greg M should work. It may be that you have implemented his idea incorrectly.
I will explain how Excel behaves when you change the code in a module or replace modules.
You should know that after interfering with the VBA project via code, when the main procedure (and also the thread) runs out, the VBA project is recompiled.
When in a single thread (no matter how many procedures called consecutively) you first delete a module and then import a new one) the recompilation of the project will occur only after the main procedure ends. Assuming that we want to replace MyModule, during the operation of the macro the machine "remembers" that the project contains MyModule. Importing another module with the name MyModule will result in MyModule1 (because the name MyModule is "occupied" all the time), even though MyModule was removed a moment ago. As I mentioned earlier, only after the macro is finished will the project recompile, but that is too late.
If you remove MyModule in one procedure and at the end of this procedure add the running of a macro that imports the new module, but using the OnTime method, the whole task will be done in two threads. In the first thread you delete the module, and in the second thread you import the new one. Between the first and second threads there will be a "break" in which recompilation will take place. The second recompilation will take place after the second thread finishes.

BOOM!

Artik

Thanks Artik... I will give it a try and report back. Run a routine to delete all vb module... set an on timer event after the vb code is deleted... the on timer event will import the new modules, Correct?

Hi again,

Many thanks for the Reputation increase - much appreciated!

Best regards,

Greg M

Originally Posted by Steveapa

The one thing I don't know is how to re-lock vb code after unlock and import. Anyone able to help with the re-lock code?

If the project was protected in the file and you unprotected it, the project should still be protected when you save and reopen the file. So it seems that you don't need to do anything except save and close the file where you changed the code.

Artik

Thanks Artik. Yes, it is protected on save / close / open. BUt I would prefer to not save / close. If the file is opened from an emial attachment, I dont' wnat to save it . I guess I could ask them where they want to save it.. and default to the current location. I will consider that. Thansk.