Cannot remove code modules from a destination workbook through VBA

Question

I am running an Excel 2010 macro that opens another workbook and removes a few code modules (a form and a BAS module). After that it re-imports them as an updated version. This is the code:

For Each x In destination_wb.VBProject.VBComponents
  If LCase(x.Name) Like LCase("frmCCLogin*") Or _
      LCase(x.Name) Like LCase("modCQ_test*") Then
  destination_wb.VBProject.VBComponents.Remove (x)
Next

I have no problem with the import but the remove process doesn't always work as expected. For some reason the BAS (modCQ_test.bas) module is not always removed. As a result, when I re-import, a new duplicated module is created ending with a "1" (i.e. modCQ_test1.bas). I could see that many people experienced the same problem however, none of the proposed solutions worked for me. Not sure why this is happening? Please advise.

Answer 1

If you can use the exact name of the module, you can write something like:

Public Sub RemoveComponent(ByVal Book As Workbook, ByVal Name As String)

On Error Resume Next
With Book.VBProject.VBComponents
    Call .Remove(.Item(Name))
End With

End Sub

If you're stuck with wildcard matching (i.e. SomeName*) you could iterate the VBComponents collection and cache the names into a collection or array or whatever and call the function above for each name matched.

Additionally, if you wish to enumerate the VBComponents collection and remove like your code sample, I recommend that you go in the reverse order.

So something like:

Public Sub RemoveComponent1(ByVal Book As Workbook, ByVal NameSearch As String)

Dim oCompS As VBComponents
Dim oComp As VBComponent
Dim i As Integer

Set oCompS = Book.VBProject.VBComponents
For i = oCompS.Count To 1 Step -1
    Set oComp = oCompS(i)
    If oComp.Name Like NameSearch Then Call oCompS.Remove(oComp)
Next

End Sub

Answer 2

Problem is resolved. This simple line of code that hides the destination workbook, fixed the duplication issue in my case:

destination_wb.Windows(1).Visible = False

After this you can remove, then add the components. No duplication will occur.

Answer 3

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.

Answer 4

As time (some years) has passed I do now have an answer for the phenomenon and a solid and stable solution.

1. In order not to "cut off the branch you sit on" one will have to envoke another Workbook/VB-Project for deleting and re-importing a Component.
2. Even another VB-Project performing the task will have to consider that the Component is definitely removed when the code which removed it has "finished".

Conclusion: Rename, Remove, Import, all performed by a VB-Project invoked via "Run ...." will do the trick.