Worksheet.Name causes OutOfMemoryException

Question

.Net4 C# VSTO4 Excel Add-In:

The following function is called very often, let's say every second:

   /// <summary>
    /// Gets a unique identifier string of for  the worksheet in the format [WorkbookName]WorksheetName
    /// </summary>
    /// <param name="workbook">The workbook.</param>
    /// <param name="worksheet">The worksheet.</param>
    /// <returns>
    /// A unique worksheet identifier string, or an empty string.
    /// </returns>
    public static string GetWorksheetUniqueIdentifier(Workbook workbook, dynamic worksheet)
    {
        if (workbook == null) return string.Empty;
        if (worksheet == null) return string.Empty;//Note: Worksheet can also be a diagram!

        return string.Format("[{0}]{1}", workbook.Name, worksheet.Name);
    }

After a while, I am getting the following exception:

System.OutOfMemoryException
   at System.Collections.Generic.Dictionary`2.Resize()
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at Microsoft.CSharp.RuntimeBinder.Semantics.SYMTBL.InsertChildNoGrow(Symbol child)
   at Microsoft.CSharp.RuntimeBinder.Semantics.SymFactoryBase.newBasicSym(SYMKIND kind, Name name, ParentSymbol parent)
   at Microsoft.CSharp.RuntimeBinder.Semantics.SymFactory.CreateLocalVar(Name name, ParentSymbol parent, CType type)
   at Microsoft.CSharp.RuntimeBinder.RuntimeBinder.PopulateLocalScope(DynamicMetaObjectBinder payload, Scope pScope, ArgumentObject[] arguments, IEnumerable`1 parameterExpressions, Dictionary`2 dictionary)
   at Microsoft.CSharp.RuntimeBinder.RuntimeBinder.BindCore(DynamicMetaObjectBinder payload, IEnumerable`1 parameters, DynamicMetaObject[] args, DynamicMetaObject& deferredBinding)
   at Microsoft.CSharp.RuntimeBinder.RuntimeBinder.Bind(DynamicMetaObjectBinder payload, IEnumerable`1 parameters, DynamicMetaObject[] args, DynamicMetaObject& deferredBinding)
   at Microsoft.CSharp.RuntimeBinder.BinderHelper.Bind(DynamicMetaObjectBinder action, RuntimeBinder binder, IEnumerable`1 args, IEnumerable`1 arginfos, DynamicMetaObject onBindingError)
   at Microsoft.CSharp.RuntimeBinder.CSharpInvokeMemberBinder.FallbackInvokeMember(DynamicMetaObject target, DynamicMetaObject[] args, DynamicMetaObject errorSuggestion)
   at System.Dynamic.DynamicMetaObject.BindInvokeMember(InvokeMemberBinder binder, DynamicMetaObject[] args)
   at System.Dynamic.InvokeMemberBinder.Bind(DynamicMetaObject target, DynamicMetaObject[] args)
   at System.Dynamic.DynamicMetaObjectBinder.Bind(Object[] args, ReadOnlyCollection`1 parameters, LabelTarget returnLabel)
   at System.Runtime.CompilerServices.CallSiteBinder.BindCore[T](CallSite`1 site, Object[] args)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at CallSite.Target(Closure , CallSite , Object , Object )
   at TestAddIn.ExcelAccessor.GetWorksheetUniqueIdentifier(Workbook workbook, Object worksheet)
   at TestAddIn.ExcelAccessor.GetCurrentWorksheetUniqueIdentifier()
   at TestAddIn.ExcelAccessor.timerExcelObserver_Tick(Object sender, EventArgs e)--------------------------------------------------------------------------------------------------------

Calling code is:

  private static Timer timerExcelObserver = new Timer();

...

  timerExcelObserver.Tick += new EventHandler(this.timerExcelObserver_Tick);
  timerExcelObserver.Interval = 1000;
  timerExcelObserver.Start();

...

  private void timerExcelObserver_Tick(object sender, EventArgs e)
  { 
    ...
    var updatedWorksheetIdentifierString = GetCurrentWorksheetUniqueIdentifier();
    ...
  }

  public static string GetCurrentWorksheetUniqueIdentifier()
  {
      return GetWorksheetUniqueIdentifier(ExcelApplication.ActiveWorkbook, ExcelApplication.ActiveSheet);
  }

I have no idea why I am getting an exception!

Can it maybe help to take a "using" in GetWorksheetUniqueIdentifier?

using(worksheet)
{
   return string.Format("[{0}]{1}", workbook.Name, worksheet.Name);
}

Does anyone have an answer?

looks as if something going wrong when inserting in some dictionary, can you share complete stacktrace — Brijesh Mishra, May 30 '12 at 12:53
Where is this code being called from or how is it being used? It does not appear to be the direct cause of the problem (which is dying inside a Dictionary resize method), so the calling code may have the problem. — iCollect.it Ltd, May 30 '12 at 12:55
Don't dispose the `worksheet` object, which is what would happen if you use the `using` statement you posted. It is likely that the caller still needs to reference `worksheet` after your method finishes. — Keith, May 30 '12 at 20:01
@Keith:I don't think the worksheet object is being disposed when leaving the using() clause, because there are some other references to it outside the function. — jreichert, May 31 '12 at 14:52
@HiTech Magic: The calling code is a timer, that is called every second. The worksheet-object is the Excels Application.ActiveWorksheet object. — jreichert, May 31 '12 at 14:59
The problem is the "dynamic" parameter and its runtime evaluation. Is this a bug in the implementatin of dynamic? — jreichert, May 31 '12 at 15:14
The calling code may be run via *a timer*, but what are the actual lines that are calling `GetWorksheetUniqueIdentifier`? — iCollect.it Ltd, May 31 '12 at 15:40
@DanM: Because I have to. The Application.ActiveSheet is dynamic, that means it can be an instance of Microsoft.Office.Interop.Excel.Worksheet or Microsoft.Office.Interop.Excel.Chart. Each of them supports the required "Name"-Property. — jreichert, Jun 01 '12 at 13:36

score 0 · Accepted Answer · answered Jun 03 '12 at 23:15

Try releasing the COM objects explicitly:

public static string GetCurrentWorksheetUniqueIdentifier()
{
    var workbook = ExcelApplication.ActiveWorkbook;
    var worksheet = ExcelApplication.ActiveSheet;

    try
    {
        return GetWorksheetUniqueIdentifier(workbook, worksheet);
    }
    finally
    {
        if (workbook != null &&
            Marshal.IsComObject(workbook))
            Marshal.ReleaseComObject(workbook);

        if (worksheet != null && 
            Marshal.IsComObject(worksheet))
            Marshal.ReleaseComObject(worksheet);
    }
}

This has been known to fix memory issues in some Office-related scenarios.

**I don't know if this really fixes the problem, because I can't reproduce it. But it is the best answer so far.** — jreichert, Jul 13 '12 at 15:45

Worksheet.Name causes OutOfMemoryException

.Net4 C# VSTO4 Excel Add-In:

1 Answers1