VBA to remove macro and save...

    Hello all. I am looking for some vba to save a worksheet minus the macros. All formatting would have to be maintained while removing the macros. I have 2 macros (modules) and code within the sheet itself (object). If that makes any sense. I guess the ultimate solution would be to remove all vba from the project itself. Can anyone point me to a solution or provide some guidance.


    Thank you,


    Brian

    I am not sure if you would want to do this but in your case I would use a separate workbook for my macro and call the sheet you want to work with.


    Of course I have not followed the link and it might be better.


    But the reason is self destructing macro's tended to self destruct on me while I would code them. I of course do not have your application at my fingertips and your method may very well be better. I am just chiming in.

    Well I gots to admitt it is a cool link.


    So I can decide if I would like to do the same what would be the reason for doing this?


    I kinda can't figure out what would be the benefit of a macro which is pulled out.


    I picture opening up a spreadsheet with a module in it and it does its business and self destructs. This makes it unusable.


    I can not figure an application for this from my line of work. could you kinda describe the application?

    Epidemic,


    We can´t always imagine what XL is used to and therefore need to rely on the OP point of view ;)


    I recently built a solution where the client had a similar demand, delete all modules, convert formulas to values and then add the workbook as an attachment to OL and send it away.


    ...and all is executed with a button to push:D


    I usually post relevant links so I guess it worth to follow the link for further investigations :P


    Warm regards from a cold city of Östersund,


    Dennis

    The reasoning for this is:


    1. I have an Excel workbook which is the master copy. It performs various parsing of data and formatting of the resultant text via these macros embedded in it.


    2. Upon completion of the macros in the master copy above, a new macro will be called to generate a copy of the master, then remove all vba code in the copy. This is then sent to various users who do not have to worry about macro security alerts being called everytime they open it.


    3. The master copy retains all code for future use.


    Hope that helps. Still doesn't work though. Keep getting a fail message of


    "Programmatic Access to Visual basic Project is not trusted"


    Any one know why this is happening.


    Thanks,


    Brian

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


    My dims kept failing


    any idea why. I did not have the option of vbide.vbcomponent

    This you might find helps a bit, all it does is rip out all modules, so I guess its useful to you, if I understand, add a save routine in that’s to your suiting and that should provide you with a useful working solution.


    Good luck


    HTH


    Jack in the UK


    Sub RemoveAllVBAElements()



    Dim vbc As Object
    Dim wks As Worksheet
    Dim dlg As DialogSheet


    With ActiveWorkbook.VBProject
    For Each vbc In .VBComponents
    Select Case vbc.Type
    Case 1, 2, 3
    .VBComponents.Remove vbc
    Case 100
    vbc.CodeModule.DeleteLines 1, vbc.CodeModule.CountOfLines
    End Select
    Next vbc
    End With


    'For old MACRO Excel4MacroSheets
    Application.DisplayAlerts = False
    For Each wks In Excel4MacroSheets
    wks.Delete
    Next
    For Each dlg In DialogSheets
    dlg.Delete
    Next
    Application.DisplayAlerts = True


    MsgBox "VAB removed", vbExclamation, "Im Done Jack in the UKHi, This you might find helps a bit, I got this from somewhere all it does is rip out all modules, so I guess its useful to you, if I understand, add a save routine in that’s to your suiting and that should provide you with a useful working solution.


    Good luck


    HTH


    Jack in the UK




    :o

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login