Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Remove all VBA (Excel 2002)

    Hi

    I Have used this macro succsesfully before but now I am getting an error?

    Many Thanks
    Braddy

    Sub DeleteAllVBA()

    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents

    Set VBComps = ActiveWorkbook.VBProject.VBComponents

    For Each VBComp In VBComps
    Select Case VBComp.Type
    Case vbext_ct_StdModule, vbext_ct_MSForm, _
    vbext_ct_ClassModule
    VBComps.Remove VBComp
    Case Else
    With VBComp.CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    End Select
    Next VBComp

    End Sub
    If you are a fool at forty, you will always be a fool

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove all VBA (Excel 2002)

    Have you upgraded excel recently. I can't seem to find an object for your VBComp variable? My Object Browser does not recognise those objects?
    Regards,
    Rudi

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove all VBA (Excel 2002)

    Hi Rudi

    Yes I believe IT installed Service pack 2 that's and office updates.


    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove all VBA (Excel 2002)

    I just did a quick check for those objects. As I could not find them I cannot help you with this query! You will need to wait for someone else to advise you Braddy. My first impression was that it could be due to an upgrade of some kind. Stay tuned for some other possible solution / code to delete the VBA Modules!

    Sorry <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Regards,
    Rudi

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

    Re: Remove all VBA (Excel 2002)

    Do you have a reference (in Tools | References...) to the Microsoft Visual Basic for Applications Extensibility 5.3 library? You need it to manipulate code in code.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove all VBA (Excel 2002)

    Hi Hans

    I did as you suggested now I don't get an error, but it doesn't appear to work, the workbook is protected and sheet one is protected, I would expect these to be removed if the macro is working.

    Is this correct?

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    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: Remove all VBA (Excel 2002)

    I don't see anything in the code to delete workbook or worksheet protection. It does nothing to excel. it works on VB...

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove all VBA (Excel 2002)

    Hi Steve

    Thanks for that, I wrongly assumed that Protection and Unprotection was Visual Basic.

    Thanks for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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