Remove Module Code Not Working...

[R-Enemy]

I have code that removes a module and then imports a new one with the same name. The problem is that is doesn't remove the module before it imports the new one and I get two modules named Menu and Menu1. The remove does work though becuase I remove other modules with it and they are gone after the macro is done. It's almost like the module is cached and can't be removed because it is in use. I've tried everything, including running the remove statement like 10 times before importing once. Still no luck.


Please help, this is causing some serious problems.

Thanks, R

 

[mikey66]

Hi,
It sounds like one possibility might be that you have a corruption in your document. I have had this happen to me at times when things have been going along great, then all of a sudden it goes weird on me. (I like to blame it on Billy G.).

So, if your "remove" and "add" code is in the same macro, I'd first try stepping through it if you can to see if that tells you anything. (Why do I think you've probably done that already?)

Then, if it is part of the same macro, I'd try separating them into two, and running them one at a time; then making a third that basically runs them one after another if they work separately.

If that all fails, I'd try rebuilding the sheet to eliminate a possible corruption. This doesn't have to be as painful as it sounds but you do have to be careful about what you copy to the new sheet(s). If you can do it in steps and check them as you go, you may stumble across the cause of your problem. It could be in something you changed in a preceeding macro, if you build your process in a series of macros.

Hope this may provide an idea that turns out to be useful.
MikeY

 

[warbe]

I have experienced exactly the same phenomenon and it drove me mad for weeks already.
I do check whether the Code Module had definitely been removed directly after the removal and although it had vanished from the VBE's Project View, it still exists and consequently the subsequent import creates a Code Module named xxx1.
Any of the hints given proved not to be reliable on the long run and thus are nothing but guesses. Since the phenomenon is unpredictable as mentioned you never can really tell what did the trick.
My Code Moule Management Workbook at least supports the job in a way a second try is fast and easy. It helps to overcome the problem by checking the success of the remove and not importing when it failed. Fortunately the second transfer works because the system then considers the removed Code Modules correcly as being removed. Strange enough, after it worked for the first time it does it with each subsequent try - until the same Workbook is opened again. The experience is: Only with some 'target' Workbooks a removed Code Module is not considered as such immediately.

 

[mikerickson]

Have you tried saving the workbook after the remove but before the import?

 

[shg]

One of the users at EF has posted a workbook that may help (I have not used it).

Excel Help Forum

 

[warbe]

Removing Code Modules did not work reliably and I've spent endless time on this to find a trigger. Fortunately, amongst many cases where I had transferred Code Modules from one Workbook to another worked just perfect, I had one case which persitently refused to work - untill I open the 'target' Workbook with

<code>Application.EnableEvents = False
 ....Open "workbook-fullname"
Application.EnableEvents = False</code>

to prevent any VBA-Code from being executed.
My conclusion: Once Macros(Subprocedures, Functions, etc.) had been executed, a copy of the VBA-Code resides in memory and Import considers the Code Module in memory rather than in the 'Project'. I.e. while a Code Module appears to no longer exists in VBE's 'Project Explorer' it still resides in memory.

 

[shg]

untill I open the 'target' Workbook with ...

See Application.AutomationSecurity property as an alternative.