none
Delete VBA Modules - Range Loop RRS feed

  • Question

  • Hi all,

    I have  a list of modules that I am to delete from my workbook.

    I have listed the names in Column D

    It deletes the first one then gives an error unhandled handle?

    Sub DeleteModules()
    
    Dim ws                  As Worksheet
    Dim i                   As Integer
    
    
            Dim vbProj As VBIDE.VBProject
            Dim vbComp As VBIDE.VBComponent
    
            Set VBComps = ActiveWorkbook.VBProject.VBComponents
            Set ws = Worksheets("DeleteModules")
    
            For Each vbComp In VBComps
            For i = 3 To ws.Cells(ws.Rows.Count, 4).End(xlUp).Row Step 1
            If vbComp.Name = ws.Cells(i, 4).Value Then
            
            VBComps.Remove vbComp
              
               
                End If
                Next i
            Next vbComp
    End Sub

    if some one can advise what the problem is as it deleted the first module name in the column and then stopped

    thank you

    sam

    Sunday, June 26, 2016 2:30 PM

Answers

  • You're missing a declaration

            Dim VBComps As VBIDE.VBComponents

    but that is not the cause of the problem. The cause is that if VBComp is removed, you still continue the loop with the now invalid VBComp. You can correct this by inserting a line

                    Exit For

    below the line VBComps.Remove vbComp

    Here is a somewhat shorter and simpler version:

    Sub DeleteModules()
        Dim ws      As Worksheet
        Dim i       As Integer
        Dim VBComps As VBIDE.VBComponents
    
        Set VBComps = ActiveWorkbook.VBProject.VBComponents
        Set ws = Worksheets("DeleteModules")
    
        On Error Resume Next
        For i = 3 To ws.Cells(ws.Rows.Count, 4).End(xlUp).Row
            VBComps.Remove VBComps(ws.Cells(i, 4).Value)
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Sam_N8 Sunday, June 26, 2016 3:34 PM
    Sunday, June 26, 2016 3:08 PM

All replies

  • You're missing a declaration

            Dim VBComps As VBIDE.VBComponents

    but that is not the cause of the problem. The cause is that if VBComp is removed, you still continue the loop with the now invalid VBComp. You can correct this by inserting a line

                    Exit For

    below the line VBComps.Remove vbComp

    Here is a somewhat shorter and simpler version:

    Sub DeleteModules()
        Dim ws      As Worksheet
        Dim i       As Integer
        Dim VBComps As VBIDE.VBComponents
    
        Set VBComps = ActiveWorkbook.VBProject.VBComponents
        Set ws = Worksheets("DeleteModules")
    
        On Error Resume Next
        For i = 3 To ws.Cells(ws.Rows.Count, 4).End(xlUp).Row
            VBComps.Remove VBComps(ws.Cells(i, 4).Value)
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Sam_N8 Sunday, June 26, 2016 3:34 PM
    Sunday, June 26, 2016 3:08 PM
  • Thank you Very much Hans,

    I stare at code long enough and it all becomes one thing.

    It worked well - No error now

    Have a great day!

    Sam

    Sunday, June 26, 2016 3:33 PM