#NA is first time result for all UDFs

Feb 19, 2014 at 2:38 PM
I am running into a funny behavior that I can't seem to understand. I have a number of UDFs available via an Excel-DNA add-in that all work, but they all return #NA the first time they are executed, and some do this several times before displaying the results. With logging I have found that the issue is not in retrieving the data from the backend repository, but something in the sending it to Excel.

I have looked through the discussions here and don't seem to see anything that fits my situation. The queries are not larger or long running (the longest might be 3-4 sec) and they do work, just not the way I would expect. Is there any general reason why this would occur and things I should be on the lookout for in using this toolkit?

Thank you

Jason
Coordinator
Feb 19, 2014 at 6:16 PM
HI Jason,

I presume you have some UDFs that use the Excel-DNA RTD-based async functionality? I'm not sure what you mean by -some do this several times before displaying the results'. Are you using manual recalculation?
If so, the behaviour you describe is as expected.

Otherwise, I suggest:
  • you check the behaviour of the RTD samples in the distribution in your Excel, and
  • make a small example, preferably just code in a .dna file, that reproduces the behaviour you see.
There are some settings in Excel related to how the RTD services update, but these apply to all RTD services

You can also post to the Excel-DNA Google group at: https://groups.google.com/forum/#!forum/exceldna
There are some heavy users of the async and RTD features who might be of more help.

Regards,
Govert
Feb 19, 2014 at 7:33 PM
Govert,

Thank you for the quick reply. I am actually using neither RTD or Async from Excel-DNA. An example of an exposed function is below.
    [ExcelFunction(Description = "Get Instruments time series")]
    public static object GetTSByAttributeAndDate(int attributeID, DateTime effectiveDate)
    {
        object returnArray;

        try
        {
            List<TimeSeriesItemUI> returnCollection = dataProvider.GetTSByAttributeAndDate(attributeID, effectiveDate);

            if (returnCollection != null)
            {
                returnArray = ConvertToObjectArray(returnCollection, new TimeSeriesItemUI(), true);
                return ReturnResizedArray(NumberOfRows(returnCollection), NumberOfProperties(new TimeSeriesItemUI()), returnArray);
            }
            else
                return null;
        }
        catch (System.Exception e)
        {
            LogError(MethodName, e);
            return null;
        }
    }
The line of code "List<TimeSeriesItemUI> returnCollection = dataProvider.GetTSByAttributeAndDate(attributeID, effectiveDate);" is calling out to our database layer and the response time on this is 1-2 sec. When we run this function in a session of Excel we see the #NA immediately and no amount of waiting produces another result. We need to re-execute the function and generally on that second call the actual data is returned. Sometimes a third time is required. Is there something you see in the above that might give you some idea of why the first entry to the function doesn't return the data?

Thank you

Jason
Coordinator
Feb 19, 2014 at 7:38 PM
Edited Feb 19, 2014 at 7:38 PM
OK - What does the implementation of ReturnResizedArray look like?
And are you in manual or automatic calculation mode?

-Govert
Feb 19, 2014 at 7:53 PM
I haven't set the calculation mode, so the default is what I am assuming. I should also mention that I didn't originally implement this solution, just taking it over from someone else. The Resizer code I found is the following. I haven't look at it until today so I may be missing something in there.

Jason

public class Resizer
{
    static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();

    // This function will run in the UDF context.
    // Needs extra protection to allow multithreaded use.
    public static object Resize(object[,] array)
    {
        ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
        if (caller == null)
            return array;

        int rows = array.GetLength(0);
        int columns = array.GetLength(1);

        if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
            (caller.ColumnLast - caller.ColumnFirst + 1 != columns))
        {
            // Size problem: enqueue job, call async update and return #N/A
            // TODO: Add guard for ever-changing result?
            EnqueueResize(caller, rows, columns);
            AsyncRunMacro("DoResizing");
            return ExcelError.ExcelErrorNA;
        }

        // Size is already OK - just return result
        return array;
    }

    static void EnqueueResize(ExcelReference caller, int rows, int columns)
    {
        ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
        ResizeJobs.Enqueue(target);
    }

    public static void DoResizing()
    {
        while (ResizeJobs.Count > 0)
        {
            DoResize(ResizeJobs.Dequeue());
        }
    }

    static void DoResize(ExcelReference target)
    {
        try
        {
            // Get the current state for reset later

            XlCall.Excel(XlCall.xlcEcho, false);

            // Get the formula in the first cell of the target
            string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
            ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

            bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
            if (isFormulaArray)
            {
                object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
                object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);

                // Remember old selection and select the first cell of the target
                string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
                XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
                object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
                XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);

                // Extend the selection to the whole array and clear
                XlCall.Excel(XlCall.xlcSelectSpecial, 6);
                ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

                oldArray.SetValue(ExcelEmpty.Value);
                XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
                XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
            }
            // Get the formula and convert to R1C1 mode
            bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
            string formulaR1C1 = formula;
            if (!isR1C1Mode)
            {
                // Set the formula into the whole target
                formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
            }
            // Must be R1C1-style references
            object ignoredResult;
            XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
            if (retval != XlCall.XlReturn.XlReturnSuccess)
            {
                // TODO: Consider what to do now!?
                // Might have failed due to array in the way.
                firstCell.SetValue("'" + formula);
            }
        }
        finally
        {
            XlCall.Excel(XlCall.xlcEcho, true);
        }
    }

    // Most of this from the newsgroup: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
    private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1);
    static void AsyncRunMacro(string macroName)
    {
        // Do this on a new thread....
        Thread newThread = new Thread(delegate()
        {
            while (true)
            {
                try
                {
                    RunMacro(macroName);
                    break;
                }
                catch (COMException cex)
                {
                    if (IsRetry(cex))
                    {
                        Thread.Sleep(BackoffTime);
                        continue;
                    }
                    // TODO: Handle unexpected error
                    return;
                }
                catch (Exception ex)
                {
                    // TODO: Handle unexpected error
                    return;
                }
            }
        });
        newThread.Start();
    }

    static void RunMacro(string macroName)
    {

        object xlApp;            
        xlApp = ExcelDnaUtil.Application;

        try
        {   
            xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] { macroName });
        }
        catch (TargetInvocationException tie)
        {
            throw tie.InnerException;
        }
        finally
        {
            Marshal.ReleaseComObject(xlApp);
        }
    }

    const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
    const uint VBA_E_IGNORE = 0x800AC472;
    static bool IsRetry(COMException e)
    {
        uint errorCode = (uint)e.ErrorCode;
        switch (errorCode)
        {
            case RPC_E_SERVERCALL_RETRYLATER:
            case VBA_E_IGNORE:
                return true;
            default:
                return false;
        }
    }
}
Coordinator
Feb 19, 2014 at 8:06 PM
OK - that looks like an Excel-DNA sample that does automatic array resizing for functions, which explains the source of the #NA (line 23 or so).

It's a popular but somewhat dangerous approach to making array formulae easier to use. My original blog post about it is here: http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/ Recent Excel-DNA check-ins have an updated version in Distribution\Samples\ArrayResizer.dna.

It works like this:
  • If the Resize function is called from a range that is not the right size, then the function will immediately return #N/A, and will schedule a macro to run after the current calculation loop is done.
  • The macro then modifies the sheet by creating an array formula that covers the right size, and recalculates the function.
  • This time around, the calling range of the function will have the right size, and will return the array correctly.
The version of this sample code you have here is quite old, and newer versions of Excel-DNA have streamlined it a bit, but the idea is still the same.

One issues with the combination of your database access code and this resizer helper, is that your database function will be called multiple times in the process. If it returns different sized results, it gets even worse. One way to mitigate this is to put a short-term cache (a few seconds or so) of the call in place, and return the array from that cache when possible. That way you only go out to the database once, even if the function needs to be called a second time after the resizing work is done.

You should check the calculation mode of your sheet - it's probably manual, which would explain why you have to recalculate a few times for the results to come through. If not, something else is going wrong, and you need to pull your function apart a bit, and experiment with this resizing story until you're comfortably with how it works. Logging all the calls to your function might illuminate things a bit too.

-Govert
Feb 19, 2014 at 8:13 PM
Thank you! That is enough for me to work with for now.

Jason