Excel crashes with "Access Violation" when trying to set a range value

Jun 3, 2013 at 5:01 PM
Hello guys,

I am using ExcelDNA within my C# .Net 4 code and I encountered an issue with one UDF.

Actually I have multiple UDFs running in 3 different Excel workbooks. And one of the UDF reference a target cell and copy a table in it.

Basically, the code for this UDF is as follows :
[ExcelFunction(IsMacroType = true)]
public static string TestFunc([ExcelArgument(AllowReference = true)] object targetCell)
        {
            try
            {
                dynamic app = ExcelDnaUtil.Application;
                ExcelReference ER = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
                
                if (targetCell is ExcelReference && ER.SheetId = ((ExcelReference)targetCell).SheetId)
                {
                    object[,] res = OtherFunc();
                    if (res.GetLength(0) != 0)
                    {
                        string strTarget = (string)XlCall.Excel(XlCall.xlfReftext, targetCell, true);
                        dynamic targetRange = app.Range[strTarget];
                        targetRange = targetRange.Resize[res.GetLength(0), res.GetLength(1)];

                        // This line make Excel crashes
                        targetRange.Value2 = res;
                    }
                }
                return "ok";
            }
            catch (Exception e)
            {
            }
        }
However, whenever I try to assign the table to the targetRange, the range takes correctly the table values then when Excel finishes updating everything (all the other UDFs, using CalculateFullRebuild) it crashes without any message. Even a "try.. catch" loop doesn't catch the error.

I managed to catch the error by forcing the debugger to catch Excel's errors (right clicking on Excel process in the Task manager and Debug it). And I got the following error message:
"Non handled Exception at ... in EXCEL.EXE : 0xC0000005: Access violation at 0x0000001f".

For info, If I replace this part :
                        targetRange = targetRange.Resize[res.GetLength(0), res.GetLength(1)];

                        // This line make Excel crashes
                        targetRange.Value2 = res;
by that one :
                        targetRange.Value2 = "Test";
Everything works fine and there is no Excel crash.

A second point is that I encountered some problems with ExcelReference as the active workbook/worksheet is not necessarily the one launching the function. ((ExcelReference)targetCell) may target a wrong sheet. I use this code to prevent working on another sheet :
ER.SheetId = ((ExcelReference)targetCell).SheetId
Did I miss something?

Thanks,
Riadh
Jun 3, 2013 at 5:08 PM
I forgot to add something : The Excel crash appears only when calculation mode is "Automatic". On manual I have no crash at all.
Coordinator
Jun 4, 2013 at 6:52 PM
Hi Riadh,

Excel does not allow you to make changes to the worksheet from inside a worksheet UDF. One reason for this is that Excel maintains internal dependency structures that keep track of which cells depend on which other cells. Allowing parts of the sheet to update from a worksheet function during a calculation makes it impossible for Excel to maintain a consistent calculation state. By using the COM automation interface from inside your UDF, you can sometimes get past these Excel restrictions, but as you report that is not a safe approach and may cause Excel to crash.

The work you are doing looks like it is better suited to a macro that you run from a ribbon button or shortcut key. From a Ribbon button, you can use the Excel COM automation interfaces safely as you would from VBA, and need not use the C API type ExcelReference.

Regards,
Govert
Jun 4, 2013 at 7:47 PM
Thanks Govert,

Indeed, I probably should use a macro from a ribbon for my work.

However, I understand that Excel doesn't natively allow this kind of cell change but, for example, Reuters Eikon's functions use this feature.
Maybe they're using an asynchronous macro call or something.

Best regards,
Riadh
Coordinator
Jun 4, 2013 at 8:02 PM
Edited Jun 4, 2013 at 8:02 PM
Hi Riadh,

Yes, it can be done. But I'd advise against changing other parts of the sheet from inside a function.

Excel-DNA has some support for scheduling work to do done after recalculation completes, using ExcelAsyncUtil.QueueAsMacro. There is a small example here: https://exceldna.codeplex.com/wikipage?title=Performing%20Asynchronous%20Work

Regards,
Govert