Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stripping Macro's (Excel XP)

    How do you with code strip the macro's off a Workbook
    TIA

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

    Re: Stripping Macro's (Excel XP)

    In the first place, you must give Visual Basic permission to manipulate code (this is different from the macro security level that determines whether you can execute code).
    - In Excel, select Tools | Macro | Security...
    - Activate the Trusted Sources tab.
    - Tick the check box labeled "Trust access to Visual Basic project".
    - Click OK.

    In the second place, you must set a reference to an object library that provides the objects, properties and methods for manipulating code.
    - In the Visual Basic Editor, select Tools | References...
    - Locate "Microsoft Visual Basic for Applications Extensibility 5.3".
    - Tick its check box.
    - Click OK.

    Now, you can run the following macro. It will remove all code from the active workbook.

    Sub RemoveCode()
    Dim vbc As VBComponent
    Dim i As Integer
    For i = ActiveWorkbook.VBProject.VBComponents.Count To 1 Step -1
    Set vbc = ActiveWorkbook.VBProject.VBComponents(i)
    Select Case vbc.Type
    Case vbext_ct_StdModule, vbext_ct_ClassModule, vbext_ct_MSForm
    ' Remove modules, class modules and userforms
    ActiveWorkbook.VBProject.VBComponents.Remove vbc
    Case vbext_ct_Document
    ' Clear workbook and worksheet modules
    vbc.CodeModule.DeleteLines 1, vbc.CodeModule.CountOfLines
    End Select
    Next i
    End Sub

    Warning: make absolutely sure that the active workbook is the one you want to remove all code from. The action cannot be undone!

  3. #3
    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: Stripping Macro's (Excel XP)

    In addition to Hans' comments and code, some additional code to work with Modules and VBA using VBA can be found at Chip Pearson's site in the section on Programming To The VBE

    Steve

Posting Permissions

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