XlCall memory overflow

Jun 27, 2013 at 9:49 PM
I am observing memory overflow when using "IsInFunctionWizard" shortcut which calls excel directly using XlCall. The overflow was observed in Excel 2003 and 2010.

The ExcelDna.dna below quickly overflows memory and makes my excel session crash. In real life usage ( ie when XlCall as below is used in addin ) excel eventually becomes very slow after prolonged period of use.

I wonder if this is an issue with Excel not being able to deallocate resources or a potential problem with implementation of ExcelDna.Loader.XlCallImpl.cs

Thank you,
Alex
<DnaLibrary Language = "CS" RuntimeVersion="v4.0">

<![CDATA[

using ExcelDna.Integration;

public class MyFunctions
{
            
        [ExcelFunction(Description="Overflows excel memory",  IsMacroType = true, Category="My functions")]
        public static string MemoryFunk()
        {
        object[] xlFuncWizParams = new object[3] { 4, "Standard", 1 };
        bool dummy = false;
        for (int i = 0; i < 1000000; ++i )
            dummy = (bool)XlCall.Excel(XlCall.xlfGetTool, xlFuncWizParams);
        return "";
        }
}

]]>

</DnaLibrary> 
Coordinator
Jun 28, 2013 at 11:29 PM
Hi Alex,

I've tried it and can see that there is a problem.
I'll have a closer look.

Thanks for reporting the issue.

Regards,
Govert
Coordinator
Jun 29, 2013 at 10:49 PM
Hi Alex,

I have a closer look, and I believe this is a bug in Excel, not related to Excel-DNA.

If we call the xlfConcatenate function with the same parameters, instead of the xlfGetTool function, there is no memory issue. This alternative uses the same marshalling and memory management in Excel-DNA, but does not lead to the excessive memory usage.

Try this:
    [ExcelFunction(Description="Overflows excel memory",  IsMacroType = true, Category="My functions")]
    public static string MemoryFunkConcat()
    {
        object[] xlConcatParams = new object[3] { 4, "Standard", 1 };
        string dummy = null;
        for (int i = 0; i < 1000000; ++i )
            dummy = (string)XlCall.Excel(XlCall.xlfConcatenate, xlConcatParams);
        return dummy;
    }
To confirm 100% that your memory leak is an Excel issue, one would have to make a pure C add-in that makes the same calls...

I don't think the xlfGetTool approach is a good one for checking whether the function wizard is displayed. There have been other reports of it not working right on newer Excel versions. What Excel versions and Excel-DNA version are you using. I know the current check-in of Excel-DNA has a bug in the ExcelDnaUtil.IsInFunctionWizard() under Excel 2010, but the latest Excel-DNA release version 0.30 should work fine with all Excel except Excel 2013.

Regards,
Govert
Jul 1, 2013 at 6:53 PM
Hello Govert,

thank you very much for looking into it. I detected the issue in Excel 2010 and 2003.
I was using the latest version of ExcelDna as well as the 0.30 release for testing.

The reason for checking the wizard this way was that it runs much faster than IsInFunctionWizard().
In a particular session I was timing, it ran about seventy times faster, with IsInFunctionWizard() taking about 1.5ms per call.

If the difference in speed were not as drastic I would be ok using the official function ( or as you recommend, the version that is in ExcelDna 0.30 ).
But given that it's slow and yet knowing that the results can be obtained much faster I was really hoping there exists a (safely working) alternative.

Alex
Coordinator
Jul 1, 2013 at 7:27 PM
Hi Alex,

I was checking in Excel 2013, which also seems to have this issue with xlfGetCell.

Perhaps if 1.5 ms is a long time relative to the execution time of your function, then you need not check whether you are running in the function wizard, but just put in place robust handling of the argument values. The built-in Excel functions all calculate in the function wizard too, so maybe it's not really an issue.

The typical use case for the IsInFunctionWizard check is when a function is quite slow, and it is important to avoid excessive recalculation in the function wizard. But in such a case the overhead is not significant.

-Govert