Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Delete VBA Module

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    UK - London
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to write a piece of code which will open a workbook and delete one of it's VBA modules.

    Any suggections?
    Thanks

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need to set a reference to the VBIDE in Tools-References from the VB editor (the name is "Microsoft Visual Basic X.X Extensibility"). Then you can do something like this:

    Code:
    Sub DelModule()
    
        Dim VBP As Object
        
        Set VBP = ActiveWorkbook.VBProject
        
        VBP.VBComponents.Remove (VBP.VBComponents("Module2"))
        Set VBP = Nothing
    End Sub
    HTH,

    Russell

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    UK - London
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great - thanks.

    Do I use a silimar method is I just decide to delete a Sub routine within a module?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nope use something like what Chip has created:

    Sub DeleteProcedure()

    Dim VBCodeMod As CodeModule
    Dim StartLine As Long
    Dim HowManyLines As Long

    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    With VBCodeMod
    StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
    HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
    .DeleteLines StartLine, HowManyLines
    End With

    End Sub
    http://www.cpearson.com/excel/vbe.htm

    Define the module and procedure name.

    HTH. Cheers, Nate

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about just a single line of code like in workbookopen event?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    all i could come up with was

    Sub DeleteProcedureWB()

    DisplayAlerts = False
    Dim VBCodeMod As Object
    Dim StartLine As Long

    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
    With VBCodeMod
    StartLine = (3)
    .Deletelines StartLine
    End With
    ActiveWorkbook.Save
    End Sub

    it works but can u specify text to delete?

Some videos you may like

User Tag List

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
  •