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

0

There are 0 answers