Unable to delete code module from VBA project

[goblin]

I'm trying to delete all macros from an open workbook and for some reason it doesn't work. I got the code from Chip Pearson's site and it worked fine in XL2000, but now doesn't work in XL 2002.

I have enabled the 'Trust access to VB project' under options->security.

I need to use 'current file' as a template file for a lot of other files, except the other files should not have the macros that the original file has. It goes something like:

Function PrepareStrippedTemplate() As Workbook
    Dim wb As Workbook
    ThisWorkbook.SaveCopyAs Application.DefaultFilePath & "\BudgetTmpl.xls"
    Set wb = Workbooks.Open(Application.DefaultFilePath & "\BudgetTmpl.xls", False, False)
    wb.Sheets("tables").Range("A2", .Range("M2").End(xlDown)).Clear
    DeleteAllVBA wb
    Set PrepareStrippedTemplate = wb
End Function

DeleteAllVBA is like this:

Sub DeleteAllVBA(wb As Workbook)
    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents
    
    Set VBComps = wb.VBProject.VBComponents
    
    For Each VBComp In VBComps
        Select Case VBComp.Type
            Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
                With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                End With
                VBComps.Remove VBComp
            Case Else
                With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                End With
        End Select
    Next VBComp
End Sub

This code clears all code from all modules, but it doesn't remove modules itself. Thus when the template file is used, it will always display the macro warning. How do I remove that code module?????!!!!

 

[headtoadie]

Can you access the modules in the VBA editor (ALT+F11)? If so, you can right-click then choose "Remove Module ..."

HT

 

[goblin]

I can and I do. But I want this to be automatic, because I'm not going to use this myself. I´m just writing the macros

 

[Ivan F Moala]

Sub DeleteAllModules()
' you need MS extensibility.
Dim x As Variant
For Each x In ActiveWorkbook.VBProject.VBComponents
    If x.Type = 1 Then
        ActiveWorkbook.VBProject.VBComponents.Remove x
    End If
Next
End Sub

 

[goblin]

That one worked Ivan. Why, I don't know. I've stepped through both programs, and in my program the REMOVE line was executed for modules of type 1, but the module wasn't removed and there was no exception raised.

Your sub works so unless someone can offer an explanation, I'll consider the matter closed.

Thank a bunch Ivan!!

Goblin

 

[goblin]

Sorry for the premature victory statement in my posting above. The same thing happens with Ivan's code as with my code. It works perfectly when I run it in isolation but not when run within my program.

My program creates a new file based on an old file, clears out all VBA code, and then repeatedly fills in the new template file with data and saves one file for each subsidiary. When trying to clear code within the main program, the code module does NOT go away.

So, still at a loss on removing that code module!

 

[stuartjay45]

Have you had any joy on fixing your problem?

I am experiencing the same problem, needing to save the excel sheet in an older version without the code but the users screens just hang with this code in.

 

[jobohm]

It works perfectly when I run it in isolation but not when run within my program.

I'm experiencing the same symptom, and I believe i know why. Please verify.

I've made a template for our business that contains a set of standard modules in addition to those developet for each "instance" of the template. The template hass a Sub that updates my modules by looking for the exported version (*.bas) on a server, and if it's there "delete and import".

It works to spec for 7 modules, but the 8th isn't deleted. The imported one just gets a 1 after it's name. This module is the only one that is used by this sub (to find out what *.bas i got available) and I don't think thats a coincidence. Even so I don't se anything in window:Locals that i imagine could stop the module.remove, and theres no error halting the execution.

Thanks for any pointers.

 

[CmputrAce]

Don't know if any of you have figured this one out yet, but I have been battling it for months, and when it looks like I have it working, Excel throws another wrench in my process.

I think that somehow VBA does some dependency checking and pseudo-compiling in realtime, so it doesn't like it when you use VBA (which means the VBA interpreter/checker is running) to screw with VBA. Sometimes it works and sometimes it doesn't - and it seems to work more often when I am not replacing an entire set of modules.

Here are the outlines for the main methods I have tried:
<Pseudocode, not real VBA>

Get list of modules to replace
For each module in modules
remove the existing module
import the new module
next module

and

Get list of modules to replace
for each module in modules
RENAME existing module to "zz_" & module.name
import new module
next module

for each module in VBModules
if module.name = "zz_*" then remove it
next module

The second method APPEARED to be working until I started getting errors for duplicate public variables before removing the old modules.

What I think I will try now:

Get list of modules to replace
for each module in modules
RENAME existing module to "zz_" & module.name
import new module
remove old module
next module

I'll let you know if it works.

 

[CmputrAce]

Well gentlemen, the new approach appears to be working.