Delete VBComponent

[razman10]

I have the following code in a userform to delete "Module 2" and it works - sometimes. (I "borrowed" this code from a website, I don't remember which one)

DeleteVBComponent ThisWorkbook, "Module2"

Sub DeleteVBComponent(ByVal wb As Workbook, ByVal CompName As String)
' deletes the vbcomponent named CompName from wb
    Application.DisplayAlerts = False
    On Error Resume Next ' ignores any errors
    wb.VBProject.VBComponents.Remove wb.VBProject.VBComponents(CompName)    ' delete the component
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

Immediately following this code, the file saves and sends itself.

My issue is that If I stop the code BEFORE the Save & Send, Module 2 goes away - as it should. However, if I let it run the file saves itself WITH Module 2 still there!

Any ideas?

 

[TonyJollans]

Try adding a [tt]DoEvents[/tt] after the Remove.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[razman10]

Thanks for the reply, Tony, but that's not it. Module 2 is still there.

Module 2 has a bunch of macros and what not that run BEFORE moving to Module 3 which is where the code I posted above is located (along with other code).

It's almost as if it's keeping Module 2 in memory until the code is stopped. By the time the code is stopped on its own, it's too late - the file has already saved itself.

This is frustrating!

 

[TonyJollans]

The short answer is that I don't know, but if stopping the code makes it succeed, then whatever is preventing the deletion take effect is released somehow. What about the DisplayAlerts?

Also why have the error trap? Maybe whatever is going wrong, may just beb being ignored because of that.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[razman10]

I've tried it both with and without the error trap and with and without the DisplayAlerts and it does the same. There is no error message; the code is properly laid out and it runs properly. The Module is removed if I stop the code, but not if I let it run.

Is there any way of releasing memory (while still keeping variables)? Whatever it has in memory is what is keeping it from removing the Module.

 

[HughLerwill]

>wb.VBProject.VBComponents.Remove ...

Will fail with a trappable error if Excel - Tools menu - Macro - Security - Trusted Publishers - Trust access to Visual Basic Project is unticked (in Excel 2003 speak); that is probably why the 'borrowed code' uses the On Error Resume Next on that line ie. it deletes the code if allowed to and leaves it in otherwise.

To reliably remove such code you must trust users to tick the trust box, or preferably copy all but only the stuff you want into a new WorkBook and then send that.

 

[razman10]

[red]...or preferably copy all but only the stuff you want into a new WorkBook and then send that. [/red]

That is actually where I started, but I need to send some code with it (Module 3) so the recipient can in turn run a macro. I could not find a way to easily copy a module into a new workbook, so I opted to use the same workbook and remove data I don't need. It seemed easier, and it works if I stop the code before saving!

 

[HughLerwill]

I have to admit to having settled for an On Error Resume Next solution so far but this link suggests it is posible to copy a module between WorkBooks;

http://exceltip.com/st/Copy_modules...another_using_VBA_in_Microsoft_Excel/501.html

 

[HughLerwill]

... I expect however that the Import will fail unless Trust access to Visual Basic Project is ticked. So we're back to where we started; sorry.