none
Excel Add-In: How to detect whether the current Excel instance has any macros installed? RRS feed

  • Question

  • We are upgrading our inside-Excel applications from some old macros to a new AddIn. They are mutually incompatible, and the macros should be removed. What I would like to do is inform the user:

       "Macro named 'xyz' has been detected. If you choose to proceed, it will be removed from your PC"

    Or else, we may decide to simply remove the Macro(s) silently.

    How can they be detected? Removed?

    Tuesday, May 29, 2018 12:07 AM

Answers

  • Hello Travis Banger,

    You could go through all the workbooks in the current application instance and then loop through their VBProject to deleted all the code in every single VBComponent(sheet, module, class module).

    Here is the simple code. PS:need add reference to Microsoft.Vbe.Interop.

                Excel.Application xlApplication = Globals.ThisAddIn.Application;
                foreach (Excel.Workbook xlWorkBook in xlApplication.Workbooks)
                {
                    Microsoft.Vbe.Interop.VBComponents vbComponents = xlWorkBook.VBProject.VBComponents;
    
                    foreach (Microsoft.Vbe.Interop.VBComponent vbComponent in vbComponents)
                    {
                        vbComponent.CodeModule.DeleteLines(1, vbComponent.CodeModule.CountOfLines);
                    }
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Travis Banger Wednesday, May 30, 2018 11:27 AM
    Wednesday, May 30, 2018 6:07 AM

All replies

  • Another alternative would be to establish some security policies: blocking macros from running, or even being installed. Preventing the "Developer" thing from being enabled, you name it.

    However, I cannot go PC by PC doing any of this. I need my AddIn to do it, programmatically.

    TIA



    Tuesday, May 29, 2018 12:14 AM
  • Hello Travis Banger,

    You could go through all the workbooks in the current application instance and then loop through their VBProject to deleted all the code in every single VBComponent(sheet, module, class module).

    Here is the simple code. PS:need add reference to Microsoft.Vbe.Interop.

                Excel.Application xlApplication = Globals.ThisAddIn.Application;
                foreach (Excel.Workbook xlWorkBook in xlApplication.Workbooks)
                {
                    Microsoft.Vbe.Interop.VBComponents vbComponents = xlWorkBook.VBProject.VBComponents;
    
                    foreach (Microsoft.Vbe.Interop.VBComponent vbComponent in vbComponents)
                    {
                        vbComponent.CodeModule.DeleteLines(1, vbComponent.CodeModule.CountOfLines);
                    }
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Travis Banger Wednesday, May 30, 2018 11:27 AM
    Wednesday, May 30, 2018 6:07 AM