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
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
What other methods did you try, and what happened?
check this kb
http://www.vbaexpress.com/kb/getarticle.php?kb_id=511
[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'?Originally Posted by makako
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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?
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
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Same here, messagebox saying Hi but no modules only the workbook open code is left.
2003
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.
Last edited by makako; 08-03-2006 at 07:21 AM.
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
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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
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
No, it shows an error (1004). Is there any other reference i can use or am i missing?
It works for me.....do you have a Module1
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
hi lucas,
can you post an example please.
thanks
attached
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Originally Posted by makako
thanks lucas,
i got the same error message, but found the following solution
To turn on trusted access to Visual Basic Projects:
works ok for me now with the above solution.
- On the Tools menu, point to Macro, and then click Security.
- On the Trusted Sources tab, select the Trust access to Visual Basic Project check box
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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?? :-)
Originally Posted by lucas
sorry must have missed it....