CODEWITHSUNDEEP
excelvba
LShaver
LShaver

Reputation: 305

Delete some VBA forms and modules, leave others

I have an Excel workbook with several forms and macros I use to generate reports for clients. When I send this workbook to clients, I need to remove some (not all) of these VBA objects. I'm writing some VBA code to delete the parts I don't want.

This code works well to delete code within specific sheets (adapted from here):

Sub export()

    Dim VBProj As VBIDE.VBProject
    Dim VBComp, sVBComp As VBIDE.VBComponent
    Dim sSheet As Object, strName As String

    'Delete code on sheets
    For Each sSheet In Sheets
        Select Case sSheet.Name
            Case "Home", "Input"
                strName = sSheet.CodeName
                With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
                        .DeleteLines 1, .CountOfLines
                End With
            Case Else
               'Nothing else
        End Select
    Next sSheet

End Sub

However, I'm struggling to get this similar code to work for deleting VBA modules and forms:

    'Delete forms and modules
    For Each VBComp In VBProj.VBComponents
       Select Case VBComp.Name
            Case "Module1", "Form1"
                strName = VBComp.Name
                With sVBComp = VBProj.VBComponents(strName)
                        VBProj.VBComponents.Remove sVBComp
                End With
            Case Else
               'Nothing else
        End Select
    Next VBComp

Upvotes: 0

Views: 1805

Answers (1)

TinMan
TinMan

Reputation: 7759

There is no need to iterate over the projects. You can access them by name.

Usage:

 RemoveVBComponents ThisWorkbook.VBProject, "Module1", "Form1"

RemoveVBComponents:Sub

Sub RemoveVBComponents(VBProject As Object, ParamArray ComponentNames() As Variant)
    Dim Item
    For Each Item In ComponentNames
        On Error Resume Next
        VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(Item)
        On Error GoTo 0
    Next
End Sub

Upvotes: 4

Related Questions