VbComponents.Remove does not always remove the module

Question

I am trying to use Chip Pearson code to overwrite existing VBA code with import from another project. The source code is here .

In particular, I look:

With ToVBProject.VBComponents .Remove .Item(ModuleName) End With 

But this call to VBComponents.Remove sometimes only actually VBComponents.Remove after the VBA is completed, that is, the delete operation does not take effect until all the statements are complete or the code reaches the breakpoint, and then I stop debugging . This problem is due to the following code for importing a new module or replacing an existing module code with a new module:

  Set VBComp = Nothing Set VBComp = ToVBProject.VBComponents(CompName) If VBComp Is Nothing Then ToVBProject.VBComponents.import filename:=FName Else If VBComp.Type = vbext_ct_Document Then 'delete the module code, 'import a temp module, 'copy over the temp module code, 'delete the temp module End If End If 

Removing a module has not yet VBComp effect, so VBComp not Nothing , as far as the debugger knows. Therefore .import will not be called.

Even if I comment on if VBComp.Type = vbext_ct_document then and end if , so that the new module code overwrites the existing one, regardless of what VBComp.Type , the module will still be deleted after the code is completed, and the import will not replace it.

What is strange is that this does not happen with all modules; some of them are actually deleted in real time after calling VBComponents.Remove .

I saw several different posts about this in different forums and did not find a satisfactory solution. .Remove now I'm using a workaround to change the .Remove call to:

  With ToVBProject.VBComponents .Item(ModuleName).name = ModuleName & "_remove" .Remove .Item(ModuleName & "_remove") End With 

so that by changing the name, ModuleName no longer exists and, therefore, a call to .import will be called. This assumes, of course, that no module named ModuleName & "_remove" exists.

Is there a better solution?

Answer 1

I tried renaming and found that this caused problems with the sheet and ThisWorkbook modules. Therefore, I changed it a little to rename only modules without documents. This seems to work cleanly.

  If .Item(ModuleName).Type <> vbext_ct_Document Then .Item(ModuleName).Name = ModuleName & "_OLD" .Remove .Item(ModuleName & "_OLD") Else .Remove .Item(ModuleName) End If 

Answer 2

If you have links to the code and method in the ThisWorkbook , then Excel may hang with the modules, rather than delete them. The trick is to hide calls from Excel using Application.run("foo") instead of direct calls like foo() .
This worked for me in Excel 2010

Answer 3

I used to come across such things. Often the DoEvents team helps. Did you give it a shot?

In other cases, I put the command in a do while loop and used a boolean value to constantly check if this command was successful. Sometimes a DoEvents command in a loop is required.

Just remember to put something in the loop so that after so many cycles it gives up. Getting stuck in an infinite loop can be bothersome.

Answer 4

I spent endless time removing / replacing code modules to figure out which triggers work against and not work.

Removing / replacing code modules in another book

This is by far the least troubling approach and why I implemented it using Code Module Management . In this case, for any workbook not yet open that can be selected in the file dialog box, the Workbook opens with:

 Application.EnableEvents = False ....Open "workbook-fullname" ' selected in a file dialog Application.EnableEvents = False 

"Code module management" will not work when the target workbook has been manually opened before (and therefore presented for selection as a list of open books) without interfering with the execution of any VBA code (see here so that this can be achieved ) Of course, this is only a problem when Workbook_Open executes the code.

My conclusion: after the macros have been executed (subprocedures, functions, etc.), a copy of the VBA code is in memory. Therefore, any Delete will not be executed until the completion of the procedure that deleted the code module. Therefore, Import examines an existing code module and imports a code module with the same name with a numeric suffix to make its name unique. And this from the course always happens when ...

Removing / replacing code modules in "ThisWorkbook" *)

So far, I have not found any way to achieve this - except that the class module was not declared in any of the other code modules, which I realized by accident !, Therefore, I tried to temporarily comment on all declarations until Delete . Complete success! Delete and Import worked fine. But when I finally tried not to comment on previously commented lines of ad code, Excel always crashed (in both cases .DeleteLines and .InsertLines and .ReplaceLine). Since I did not find a solution for this, I refused the attempt - at least for now.

See Managing a code module for cleaning, deleting, transferring, exporting, importing, and even synchronizing much less cumbersome, reliable, and reliable. Even choosing the wrong “target” workbook to transfer or delete is not a drama due to the undo feature.

*) The difference between ActiveWorkbook and ThisWorkbook is important here. ThisWorkbook is a workbook that runs VBA code, and ActiveWorkbook may be different. Because usually both are the same, not paying attention to the difference, it does not matter.