Solved: Delete All Modules Macro Help Needed...

[Wolfgang]

Gentlemen,

I dug the code below from one of Chip Pearson's sites and it errors at the following statement:


Sub DeleteAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

'*****Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.Quit
'ActiveWorkbook.Close True
End Sub


I use Microsoft Office 2003 Pro SP2 on XP-Home...

Please advise...thank you...

Best,
Wolfgang

[Bob Phillips]

Either set a reference to the VBA Extensibility Libraryor chang


Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

to

Dim VBComp As Object
Dim VBComps As Object

[johnske]

If you change to Object you'll also need to change the Celect Case part of the procedure to... Select Case VBComp.Type
Case 1, 3, 2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select

[Wolfgang]

Gentlemen,
Thank you very much for your answers...

The reference was already correctly set and I made the changes to the code accordingly...

What I now get is: Run-time error 1004
Programmatic access to Visual Basic Project is not trusted

When I click on Debug the above mentioned line of code is highlighted again...

What does that mean...I've never seen anything like that as an error message before...

Best,
Wolfgang

[Norie]

Wolfgang

It's a security thing.

There is a setting you can change to avoid the message, can't remember exactly what it is of the top of my head though.

I'll try and find out and post back, perhaps someone else knows.

In the meantime it might be worth reconsidering using this code.

Why/do you need to delete all code and modules?

[Wolfgang]

Hi Norie...

that's what I thought too...
I switched to another system and here the trouble started...no one else is having that...
And it does not matter if I try on a US-system or my local one...

Strange thing is that it worked on my old system but that is gone...
The reason for this thingy is that I have some trial software to give away and I restrict its use to a couple of hous only...maby just long/short enough to test it and not break the code...

I know the story about safety regarding Excel but just for kicks...

Maybe I'm only dreaming...

Best,
Wolfgang

[Norie]

Wolfgang

Like I said there is a setting that will allow this code, but as far as I know it can only be set manually.

Could be wrong about that though.:)

If I am right though and you say you are distributing this software, how can you be sure the other user has the required setting?

If you can do it automatically then that brings up other security issues.

I for one would not appreciate somebody tampering with my settings, especially if it was to allow code that was then used to delete code/modules etc.

[Zack Barresse]

Hi Wolfgang,

I think the security setting is Tools | Macro | Security | Trusted Publishers (tab) | Trust Access to Visual Basic Project (check).

[Wolfgang]

Hi Norie,

I appreciate your concerns and if we would have experienced anything like that before we would have said "scrubb it"...

On all other systems the macro ran without any problems and now I found the reason why it behave like that...

The answer that Zack just sent exactly does the trick and I installed a trial office version from Microsoft's site...

So, they must have deactivated that setting by default and therefore my workbook just carried these settings forward...

Maybe we do reconsider all of this but this setting will not harm the other users...we'll see...

Anyway, thank you very much for your help and I really learn something new every day...especially through this fine forum...

Have a nice weekend...

Wolfgang

[Wolfgang]

Hi Zack...

What can I say....Only thany you a thousand times...

I never bothered with that parameter, I didn't even know it existed...

As I already said to Norie, this forum really is sophisticated...

Have a nice weekend, too...

Best,
Wolfgang

[Zack Barresse]

There is a way to set this setting via code, but there's no way I'm posting it here. There is also a way to check it (which mvidas has recently posted to).