Re: VBcomponent: Remove Before Close
Quote from phurstthyme;315229A co-worker had the same problem and asked me for help. His code attempted to remove an old code module and then import a new version with the same name. This resulted in the problem that you describe. By trial and error we noted that the old code module was not being deleted when the Remove command was executed so the subsequent Import step didn't work correctly. But we also noted that the old code module was being deleted at the end of the procedure containing the Remove step. So our solution (for which we have no technical explanation) was to put the Remove step and the Import step into different procedures. His original code made use of a macro button that called a macro procedure to perform both the Remove and Import. We substituted a command button for the macro button. In the command button's click event we added 2 calls. The first call executed a Sub that performed the Remove and ended. When this Sub ended, the old code module was deleted. The second call executed a Sub that performed the Import. This seemed to solve the problem.
I am a VBA Newbie and was thrown into a quite complex VBA application.
I noticed that VBA forces me to copy code which I need in several workbooks.
So I wanted to develop the same thing as described above: Have a seperate workbook which does a loop over several workbooks which build the application and importing module files into them.
I ran into exactly the same problem.
I found a solution after following the idea of phurstthyme, putting the remove code into it's own procedure. But it was enough to have a small Sub for removing:
QuoteDisplay More
Public Sub RemoveComponent(ByVal WB As Workbook, _
ByVal CompName As String)
Dim VBComp As VBIDE.VBComponent
Set VBComp = WB.VBProject.VBComponents(CompName)
Application.EnableEvents = False
WB.VBProject.VBComponents.Remove VBComp
End Sub
The calling procedure which does the loop, just calls this by:
Quote
Call RemoveComponent(TargetWB, CompName)
An immediate check of the existence after that call shows that it's successfully removed.
After that the import of the code is successful, too.
Thanks to all of you and especially to phurstthyme, giving the cool hint.