Error "subscript out of range"

0
votes

I want to auto delete all VBA modules present in personal workbook, I am trying below code but this is showing an error 9 saying "subscript out of range " Please help me to solve this error 

Sub deletemodule()
    Dim vbCom As Object
    Dim i As Integer
    For i = 2 To 10
        On Error GoTo abc
        Set vbCom = Application.VBE.ActiveVBProject.VBComponents
        vbCom.Remove vbComponent:=vbCom.Item("Module" & i)
    Next
abc:
End Sub

Thanks in advance

2
excelvba
On the Set, or the .Remove line? – Luuklag
Are you running an international version of Excel? The module default names differ in different versions. Verify that your modules have names following the ModuleX syntax. – Sam
This might be helpfull? link – JvdV

2 Answers

0
votes

Try to revert loop:

For i = 10 To 2 step -1
On Error GoTo abc
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove vbComponent:=vbCom.Item("Module" & i)
Next
0
votes

This code removes all Standard & Class modules from the workbook.
Note sure what other types there will be - Chart sheets, forms, macro sheets(?)

Modules are a collection so you can step through them using a For...Each loop rather than needing to know how many or what they're called beforehand.

Public Sub DeleteAllModules()

    Dim vbcom As Object
    Dim vMod As Object

    Set vbcom = Application.VBE.ActiveVBProject.VBComponents

    For Each vMod In vbcom
        Select Case vMod.Type
            Case 1, 2 'Standard & Class modules
                vbcom.Remove vMod
            Case 100 'Sheet & ThisWorkbook modules
                'Do nothing
        End Select
    Next vMod

End Sub