Solved: How to autodelete a module

[makako]

Is there any way to auto-delete a module? or an entire add-in?
I have tried several methods shown in other forums but none of them seem to work properly, Im using Office 2003

[Bob Phillips]

What other methods did you try, and what happened?

[CCkfm2000]

check this kb

http://www.vbaexpress.com/kb/getarticle.php?kb_id=511

[makako]

[VBA]
Option Explicit

Private Sub Workbook_Open()
Dim ThisModule As Object

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3

MsgBox "This project has been registered using code in this module" & vbLf & _
"(Demo: the registration code module will now be deleted)"

Set ThisModule = Application.VBE.ActiveVBProject.VBComponents
ThisModule.Remove VBComponent:=ThisModule.Item ("RunOnceModule")
End Sub
[/VBA]

Is this the poper way?m is not working for me

[johnske]

[VBA]
Option Explicit

Private Sub Workbook_Open()
Dim ThisModule As Object

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3

MsgBox "This project has been registered using code in this module" & vbLf & _
"(Demo: the registration code module will now be deleted)"

Set ThisModule = Application.VBE.ActiveVBProject.VBComponents
ThisModule.Remove VBComponent:=ThisModule.Item ("RunOnceModule")
End Sub
[/VBA]

Is this the poper way?m is not working for me

[vba]Item ("RunOnceModule")[/vba] must contain the name of the module between the quotation marks. Is the module you're trying to delete named 'RunOnceModule'?

[makako]

Yes, here's the zip file

Pd: will the workbook_open sentence will also delete or should it insert the delete argument between "on error resume next" etc?

[johnske]

Well, I opened your zip file, got a message box saying "Hi" and there was no code modules, so it must have worked (Office 2k) And if I had saved there would be a workbook minus a module saved.

In Excel 2002 or above the "Trust Access To Visual Basic Project" box must be selected in Tools|Options|Security|Macro Security or the macro won't run, has this been done?

If you also want to delete all code referring to this from the Workbook_Open event I suggest you have a read of this article to see how to go about it

[lucas]

Same here, messagebox saying Hi but no modules only the workbook open code is left.
2003

[makako]

I can still see it. the RunOnceModule, I skipped the on error resume next sentence and
[VBA]
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3
[/VBA]
shows an error 1004, is there anything concerning my regional config?
and yes, the Trust Access To Visual Basic Project is selected, i get the Hi message

[VBA]
Sub SelfDeleteProcedure()
Dim FirstLine As Long, NumberOfLines As Long, N As Long

On Error Resume Next
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
FirstLine = .ProcStartLine("SelfDeleteProcedure ", vbext_pk_Proc)
NumberOfLines = .ProcCountLines("SelfDeleteProcedure ", vbext_pk_Proc)

.DeleteLines FirstLine, NumberOfLines
End With
End Sub
[/VBA]

I also tried this code and added the mentioned reference to Visual Basic 5.3 stuff (Great article by the way Johnske) but it is still not working.

[johnske]

The On Error statement is required for the case where the reference already exists...

Apart from that, if you've checked "Trust Access" and your VB project is not locked for viewing... sorry, can't help you. It should be working OK but someone else may have some ideas.


EDIT: In the last example you showed above... I just discovered a typo, both instances of ("SelfDeleteProcedure ", contain an unwanted space before the last quotation mark, delete those spaces and then try it again (it's usually something quite simple like that) - sorry

[makako]

I edited the space after SelfDeletePrecedure but still not working. Can anybody send me a functional archive or anwer me if my regional setting may affect the result? thanks

[ndendrinos]

maybe this code I've picked up somehere will help ? It runs once then self destroys.

Sub DeleteThisModule()
Dim vbCom As Object

MsgBox "Hi, I will delete myself "

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

End Sub

[makako]

No, it shows an error (1004). Is there any other reference i can use or am i missing?

[lucas]

It works for me.....do you have a Module1

[CCkfm2000]

hi lucas,

can you post an example please.

thanks

[lucas]

attached

[CCkfm2000]

No, it shows an error (1004). Is there any other reference i can use or am i missing?

thanks lucas,

i got the same error message, but found the following solution

To turn on trusted access to Visual Basic Projects:

1. On the Tools menu, point to Macro, and then click Security.
2. On the Trusted Sources tab, select the Trust access to Visual Basic Project check box

works ok for me now with the above solution.

[lucas]

That was discussed in post 7 of this thread. I thought you would understand from previous posts in this thread that the trust access to vb project must be checked in order to make this work. Sorry for the omission.

[BexleyManor]

Hi Guys,

You can switch this access on and off with code using the Wscript "Shell.Application" and using wsh.RegWrite to alter the key from 0 for off to 1 for on, for access the VB Object Module. I would effectively have the registry written to switch access on, followed by a application.quit to force the user to reboot Excel and to ensure access to the object model was set to on.

The registry key can be found at:-

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\AccessVBOM


Hope this adds some flavour to your thoughts about the subject?? :-)

[CCkfm2000]

That was discussed in post 7 of this thread. I thought you would understand from previous posts in this thread that the trust access to vb project must be checked in order to make this work. Sorry for the omission.

sorry must have missed it....