Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Unable to delete code module from VBA project

This is a discussion on Unable to delete code module from VBA project within the Excel Questions forums, part of the Question Forums category; I'm trying to delete all macros from an open workbook and for some reason it doesn't work. I got the ...

  1. #1
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Unable to delete code module from VBA project

    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:

    Code:
    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:

    Code:
    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?????!!!!

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Posts
    219

    Default Re: Unable to delete code module from VBA project

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

    HT

  3. #3
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: Unable to delete code module from VBA project

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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    Code:
    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
    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: Unable to delete code module from VBA project

    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

  6. #6
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: Unable to delete code module from VBA project

    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!

  7. #7
    New Member
    Join Date
    May 2010
    Posts
    11

    Default Re: Unable to delete code module from VBA project

    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.

  8. #8
    New Member
    Join Date
    Dec 2011
    Location
    Norway
    Posts
    4

    Default Re: Unable to delete code module from VBA project

    Quote Originally Posted by goblin View Post
    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.

  9. #9
    New Member
    Join Date
    Apr 2009
    Posts
    4

    Question Re: Unable to delete code module from VBA project

    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:


    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.

  10. #10
    New Member
    Join Date
    Apr 2009
    Posts
    4

    Default Re: Unable to delete code module from VBA project

    Well gentlemen, the new approach appears to be working.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com