PDA

View Full Version : Solved: How to autodelete a module



makako
08-01-2006, 07:58 AM
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
08-01-2006, 09:44 AM
What other methods did you try, and what happened?

CCkfm2000
08-01-2006, 10:22 AM
check this kb

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

makako
08-01-2006, 03:28 PM
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


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

johnske
08-01-2006, 07:38 PM
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


Is this the poper way?m is not working for meItem ("RunOnceModule") must contain the name of the module between the quotation marks. Is the module you're trying to delete named 'RunOnceModule'?

makako
08-02-2006, 04:44 PM
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
08-02-2006, 05:33 PM
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 (http://xlvba.3.forumer.com/index.php?showtopic=238) to see how to go about it

lucas
08-02-2006, 05:42 PM
Same here, messagebox saying Hi but no modules only the workbook open code is left.
2003

makako
08-03-2006, 06:43 AM
I can still see it. the RunOnceModule, I skipped the on error resume next sentence and

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

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


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


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
08-03-2006, 04:57 PM
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
08-04-2006, 06:14 AM
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
08-04-2006, 06:35 AM
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
08-04-2006, 09:49 AM
:( No, it shows an error (1004). Is there any other reference i can use or am i missing?

lucas
08-04-2006, 10:39 AM
It works for me.....do you have a Module1

CCkfm2000
08-04-2006, 10:44 AM
hi lucas,

can you post an example please.

thanks

lucas
08-04-2006, 10:52 AM
attached

CCkfm2000
08-04-2006, 11:04 AM
:( 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:

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 boxworks ok for me now with the above solution.

lucas
08-04-2006, 11:08 AM
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
08-04-2006, 11:47 AM
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
08-04-2006, 11:58 AM
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....:whistle:

johnske
08-04-2006, 02:26 PM
In the ThisWorkbook module...


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

makako
08-04-2006, 02:27 PM
thanks,

makako
08-04-2006, 02:28 PM
thanks, my bad, I thought with allowing a minimum security level excel granted this access. thanks a lot, great articles by the way

johnske
08-05-2006, 12:40 AM
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)