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
Printable View
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'?Quote:
Originally Posted by 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?
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
Same here, messagebox saying Hi but no modules only the workbook open code is left.
2003
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.
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
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
hi lucas,
can you post an example please.
thanks
attached
Quote:
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.
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?? :-)
Quote:
Originally Posted by lucas
sorry must have missed it....:whistle:
In the ThisWorkbook module...
[VBA]
Private Sub Workbook_Open()
Call AddRefsIfAccessAllowed
End Sub
Private Sub AddRefsIfAccessAllowed()
' 'Test to ensure access is allowed
If Application.Version > 9 Then
Dim VisualBasicProject As Object
On Error Resume Next
Set VisualBasicProject = ActiveWorkbook.VBproject
If Not Err.Number = 0 Then
Msgbox "Your current security settings do not allow the code in this workbook " & vbNewLine & _
" to work as designed and you will get some error messages." & vbNewLine & vbNewLine & _
"To allow the code to function correctly and without errors you need" & vbNewLine & _
" to change your security setting as follows:" & vbNewLine & vbNewLine & _
" 1. Select Tools - Macro - Security." & vbNewLine & _
" 2. Click the 'Trusted Sources' tab" & vbNewLine & _
" 3. Place a checkmark next to 'Trust Access to Visual Basic Project.'" & vbNewLine & _
" 4. Save - then Close and re-open the workbook", _
vbCritical
Exit Sub
End If
End If
Call AddReference
End Sub
Private Sub AddReference()
Dim Reference As Object
With ThisWorkbook.VBproject
For Each Reference In .References
If Reference.Description Like "Microsoft Visual Basic for Applications Extensibility*" Then Exit Sub
Next
.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
End With
End Sub
[/VBA]
thanks,
thanks, my bad, I thought with allowing a minimum security level excel granted this access. thanks a lot, great articles by the way
Well, I don't have 2003 so I can't test the veracity of that statement, but from what I can gather the "Trust Access..." is a quite separate issue (Ta)