0

I recently decided to try adding VBA code to a Excel workbooks dynamically, this was to help with some projects I'm working on at work, as well as to get around the pain of memory leaks in VSTO i.e. if you miss a double dot using Excel Interop. As well aid in prototyping business requirements in a macro enabled spreadsheet.

To start I began with the classic Hello World. However, when I added a try catch to notify users who Trust center settings may need changing. I noticed that Excel would not close properly and hang around in Task Manager. Having double checked my code for any double dots, excessive rubber duck debugging. I eventually noticed the mere existence of the Try catch would facilitate this behaviour on my Office 2013 installation. I can remove the memory leak by taking try catch, however this means that any code I would write in this fashion would have no error handling. (Clearly not acceptable).

My reference sources: How do I properly clean up Excel interop objects?

https://support.microsoft.com/en-us/kb/303872

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VBIDE = Microsoft.Vbe.Interop;
using ExcelAPI = Microsoft.Office.Interop.Excel;
using Helpers;
using System.Windows;
namespace OutlookToolbarAutoTestableCode
{
class HelloWorldMacroCreation
{
    public void CreateHelloWorld()
    {
        ExcelHelper ExcelHelp = new ExcelHelper();

        //Create Excel//

        ExcelAPI._Application ExcelApp;
        //Create Workbook//
        ExcelAPI.Workbooks WorkingWorkbooks;
        ExcelAPI.Workbook WorkingWorkbook;
        VBIDE.VBComponent WorkingModule;
        //VBIDE.VBE VisualBasicExtension;
        ExcelAPI.Sheets WorkingWorksheets;
        ExcelAPI.Worksheet WorkingWorksheet;
        VBIDE.VBProjects WorkingProjects;
        VBIDE.VBProject WorkingProject;
        VBIDE.VBComponents WorkingComponents;
        VBIDE.VBComponent WorkingComponent;
        VBIDE.CodeModule WorkingCodeModule;

        try
        {
            ExcelApp = new ExcelAPI.Application();
            ExcelApp.Visible = true;
            //WorkingWorkbooks = ExcelApp.Workbooks;
            //WorkingWorkbooks.Add();
            //WorkingWorkbooks = null;
            //WorkingWorkbooks = ExcelApp.Workbooks;
            //WorkingWorkbook = WorkingWorkbooks.get_Item(1);
            //WorkingWorksheets = WorkingWorkbook.Worksheets;
            //WorkingWorksheet = ExcelApp.ActiveSheet;

            MessageBox.Show("Hello");
            //VisualBasicExtension = ExcelApp.VBE;

            //WorkingProjects = VisualBasicExtension.VBProjects;
            //WorkingProject = WorkingWorkbook.VBProject;
            //WorkingComponents = WorkingProject.VBComponents;
            //WorkingModule = WorkingComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
            //WorkingComponent = WorkingComponents.Item(1);
            //WorkingCodeModule = WorkingModule.CodeModule;

            //String Message = @"""Hello World""";
            //WorkingCodeModule.AddFromString("Public Sub HelloWorld() \n MsgBox(" + Message + ") \n" + "End Sub \n");

            //ExcelApp.Run("HelloWorld");

            //WorkingCodeModule = null;
            //WorkingModule = null;
            //WorkingComponent = null;
            // WorkingComponents = null;
            //WorkingProject = null;
            //WorkingProjects = null;
            //VisualBasicExtension = null;
            WorkingWorksheet = null;
            WorkingWorksheets = null;
            WorkingWorkbook = null;
            WorkingWorkbooks = null;
            ExcelApp = null;


            WorkingWorksheet = null;
            WorkingWorksheets = null;

            WorkingWorkbook = null;
            WorkingWorkbooks = null;
            ExcelApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            MessageBox.Show("Test 1");
        }
        catch (Exception ex)
        {
            WorkingCodeModule = null;
            WorkingModule = null;
            WorkingComponent = null;
            WorkingComponents = null;
            WorkingProject = null;

            MessageBox.Show("It would appear that your trust center settings are preventing this automation from working, please check your settings and try again.");
            MessageBox.Show(ex.Message);

            WorkingWorksheet = null;
            WorkingWorksheets = null;
            WorkingWorkbook = null;
            WorkingWorkbooks = null;

            ExcelApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            return;
        }

        //Create Worksheet//

        WorkingWorksheet = null;
        WorkingWorksheets = null;

        WorkingCodeModule = null;
        WorkingModule = null;
        WorkingComponent = null;
        WorkingComponents = null;
        WorkingProject = null;

        WorkingWorkbook = null;
        WorkingWorkbooks = null;
        ExcelApp = null;

        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();

     //   MessageBox.Show("Clean Up Complete");           

    }


}

}

Is this a known issue? Have I missed something obvious? Is there a way I can have no memory leaks and error handling? Is there any better way to track memory leak references? I cannot even imagine how'd you track this on a large project. Note I've commented out a fair chunk of unnecessary code.

Thanks in advance

Tim

Community
  • 1
  • 1
  • have you tried spreedsheetlight? it does not require interop just Open XML 2.0 (not 2.5). – Mihai Bratulescu Jun 14 '15 at 22:29
  • Hi Mihai, I've never heard of SpreadsheetLight, it does look interesting. I'm hoping for a solution within interop before moving to a different technology solution. I've not tried any OpenXML based solutions at my workplace, I cannot imagine there would be any problems, but as I already know Interop works, I'd rather stick to it for now. Thanks for the tip. – Timothy Cheong Jun 14 '15 at 22:57
  • It is a [bug in your code](http://stackoverflow.com/a/25135685/17034). – Hans Passant Jun 14 '15 at 23:22
  • Thanks Hans, I thought the Release mode solved all the zombie debug issues. Seems to not be the case. Run without shows no issues on Task Manager. Can I redirect this to your previous answer? – Timothy Cheong Jun 14 '15 at 23:42
  • @TimothyCheong I forgot to mention: if you are going to use this on a server I must warn you not to use Interop, I did and it was a bad ideea. – Mihai Bratulescu Jun 16 '15 at 11:18
  • @MihaiBratulescu Thanks for the heads up, but in my case the most I will be is offloading some processing from my team's laptops to a Desktop PC running Win 7. so II can go from from 3 PCs stuck on "Processing" to 1 that's progress. – Timothy Cheong Jun 16 '15 at 19:31

0 Answers0