VBcomponent: Remove Before Close

Re: VBcomponent remove before exit

Welcome to Ozgrid
It might help if we could see your code.

Re: VBcomponent remove before exit

I'm having the same problem. Excel will not immediately remove any modules where code has recently been executed. For example, given the following modules:

ThisWorkbook

Private Sub Workbook_Open()
      InitializeGlobals
      RemoveOne
      'Additional code
   End Sub

ModuleOne

Public Sub InitializeGlobals()
      'Code
   End Sub

ModuleTwo

Public Sub RemoveOne()
      Application.VBE.ActiveVBProject.VBComponents.Remove _
            Application.VBE.ActiveVBProject.VBComponents.Item("ModuleOne")
   End Sub

The module is not removed immediately (or in some cases, at all) as you might expect. It seems as though Excel holds onto the code for a while after executing it, delaying the remove command.

The same is true for a procedure that attempts to remove the module in which it is contained. For example, if a RemoveTwo() procedure was added to the ModuleTwo module, it would not work correctly.

I would be thrilled if anyone had a workaround for this.

Re: VBcomponent remove before exit

I found a solution to my problem. Maybe it will help you too.

It turns out that by simply including a call to a procedure in the Workbook_Open event, Excel will "lock" the code. This happens whether or not the call is actually executed. For example, given this code:

Private Sub Workbook_Open()
   If False Then
      Call MacroInSomeModule
   End If
End Sub

The "SomeModule" module will still be locked even though the code is never executed. To solve the problem, you simply have to call the macro indirectly using the Application.Run procedure:

Private Sub Workbook_Open()
   Application.Run("MacroInSomeModule")
End Sub

Even when the code is executed, the module can still be removed.

Re: VBcomponent remove before exit

This should remove all Macros from your WorkBook. make a backup first. Also works for MS Word

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveDocument ' in Word
' requires a reference to the 
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
    If objDocument Is Nothing Then Exit Sub
    i = 0
    On Error Resume Next
    i = objDocument.VBProject.VBComponents.Count
    On Error GoTo 0
    If i < 1 Then ' no VBComponents or protected VBProject
        MsgBox "The VBProject in " & objDocument.Name & _ 
            " is protected or has no components!", _
            vbInformation, "Remove All Macros"
        Exit Sub
    End If
    With objDocument.VBProject
        For i = .VBComponents.Count To 1 Step -1
            On Error Resume Next
            .VBComponents.Remove .VBComponents(i) 
            ' delete the component
            On Error GoTo 0
        Next i
    End With
    With objDocument.VBProject
        For i = .VBComponents.Count To 1 Step -1
            l = 1
            On Error Resume Next
            l = .VBComponents(i).CodeModule.CountOfLines
            .VBComponents(i).CodeModule.DeleteLines 1, l 
            ' clear lines
            On Error GoTo 0
        Next i
    End With
End Sub

Re: VBcomponent remove before exit

That procedure will only work if it is the only thing running in the VBA session. Any modules containing procedures you call before hand will not be removed immediately, which may be required.

For example, I have 75 xls files that all contain the same code modules. When I need to update the code modules, I need a way to import them into all files without doing it manually.

We use an additional "utility" xls file to sequentially open, update the code, and save each file. To do this, I have to loop through all components, remove the component, then re-import the current code. I can't import, because the .Remove command doesn't execute until all code has stopped running.

Here's what VBA does:

• Queue .Remove command, but don't execute it
• Attempt to import component
• See that the component already exists (because it hasn't executed the .Remove yet)
• Append a "1" to the end of the component name
• Perform any other steps
• Execute queued .Remove command on original component

Now we're left with all components that couldn't be .Removed right away having a "1" appended to their name.

Re: VBcomponent remove before exit

I realise this thread is quite old, but as i just encountered a similar problem I thought i'd identifiy my resolution.

I was trying to export and remove all components from an old workbook and replace them with components from an up-to-date workbook. Quite frequently i would remove a component but excel wouldn't release it.

I determined that it was Excel level events which were holding up the process and DoEvents had little to no effect. My resolution was to set the Application.EnableEvents property to False prior to any Export and Removal code.

For now this seems to have addressed my problem. The code in my project is quite extensive but if anyone is suffering the same problem with VbComponents.Remove then feel free to Reply and I will try posting it.

Kind Regards

Moorey

Re: VBcomponent: Remove Before Close

Hi Tom,

As they say a picture paints a thousand words. I've attached an example of my workbook with the form removed. (I removed the form because the workbook was 100+ KB) I'll attach the userform file separately in the next post.

I have tested this on a project that I was having problems with and it worked seemlessly. I pinpointed the EnableEvents property because this was the final step i had to include before it would work, but I guess it could also be down to the order of steps.

Let me know how you get on and whether or not there are any further questions.

Regards

Moorey

Re: VBcomponent: Remove Before Close

As per above this is the userform file (please change the extension from .txt to .frm)

Hope it helps

Moorey

Re: VBcomponent: Remove Before Close

A 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.

Re: VBcomponent: Remove Before Close

I had the same issue. It seems to surround the internal disposal method of Excel. Thus it needs to action the disposal event in its own time rather then complete the event immediately when the .Remove method is executed.

I wouldn't be surprised if this relates to the issues surrounding the .Net disposal methods either. You can trigger them as and when you wish but they don't necessarily remove the objects from memory immediately after you execute the disposal method.

I'm curious if anyone has this problem whether they have found my solution to work. As I mentioned earlier, I pinpointed the EnableEvents method because this seemed to resolve my problem. I would be very interested if anyone has any feed back as I'm interested to know why it works in some scenarios and not in others.

Re: VBcomponent: Remove Before Close

removed

Re: VBcomponent: Remove Before Close

I suggest you read the Forum rules then post your own question with a link to this one.

Re: VBcomponent: Remove Before Close

Quote from phurstthyme;315229

A 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:

Quote

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

Display More

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.

Re: VBcomponent: Remove Before Close

Have you read replies in this Thread about not posting in other member's Threads.