Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete a Module (XP:SR3)

    I am having an issue deleting a module via the following code:

    Sub AddModule() 'Adding a module is not an issue
    Dim vbp As VBProject
    Dim vbc As VBComponent
    Dim mdl As CodeModule

    'Set a reference to the current project
    Set vbp = ActiveWorkbook.VBProject

    'Create a new VB component, a standard module
    Set vbc = vbp.VBComponents.Add(vbext_ct_StdModule)
    vbc.name = "NewModule"
    Application.Visible = True
    End Sub

    Sub DeleteModule()
    Dim vbp As VBProject
    Dim vbc As VBComponent
    Dim mdl As CodeModule

    'Set a reference to the current project
    Set vbp = ActiveWorkbook.VBProject

    'Set the component you want to delete
    Set vbc = vbp.VBComponents("NewModule")
    vbp.VPComponents.Remove vbc 'Error Msg: Object doesn't support this property or method
    End Sub

    From what I can tell "vbp.VPComponents.Remove vbc" should work.

    John

    PS - The extensibility library is checked.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete a Module (XP:SR3)

    Perhaps you'd fare better if you used VBComponents instead of VPComponents. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a Module (XP:SR3)

    Hans,

    I think you can tell it has been one of those days. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete a Module (XP:SR3)

    I know the feeling... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a Module (XP:SR3)

    Hans,

    One more question...

    Is it possible to copy all code from the "ThisWorkbook" object to another Excel file's "ThisWorkbook"? When I run the code it actually creates a "class module" with the code in it (reference book4's project).

    Thanks,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete a Module (XP:SR3)

    Import always creates a new VBComponent. You can use code like this to transfer code from ThisWorkbook in one workbook to that in another workbook:

    Dim strLines As String
    With oCurWb.VBProject.VBComponents("ThisWorkbook").Code Module
    strLines = .Lines(1, .CountOfLines)
    End With
    With Workbooks("Book4").VBProject.VBComponents("ThisWor kbook").CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromString strLines
    End With

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a Module (XP:SR3)

    Hans,

    You're amazing.

    Thanks,
    John

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete a Module (XP:SR3)

    I know Hans has answered most of your questions, but a good site for general info on this topic is from Chip Pearson in Programming To The VBE. He has code examples to do several differnent things.

    You may already be aware of it, but others may find it useful...

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a Module (XP:SR3)

    Steve,

    It would appear that the code to unprotect a VBA module has been successfully tested by Michael Beckinsale and Bill Manville.

    I have toyed with it and also got it to work after some minor tweaking to meet my needs.

    I know there have been some threads on this topic and thought I would share this find with the lounge.

    Regards,
    John

Posting Permissions

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