Sheet with UDF runs UDF whenever any column on the sheet is deleted

Dec 4, 2013 at 2:39 PM
Edited Dec 4, 2013 at 2:39 PM
Hi,

I created a UDF Excel function. Lets say that the function is in Cell A1 and I go to delete Column Z. I have found that this action causes the function to execute again.

Is there a way to prevent this behavior from occurring? Does this have to do with the calculation model or volatility?

Thanks,
Coordinator
Dec 4, 2013 at 5:30 PM
Hi Lee,

This is the normal Excel behaviour.
I don't know of a workaround.

Why is it a concern to you?

-Govert
Dec 5, 2013 at 1:49 PM
Lee,

I agree with Govert that this is normal Excel standard operating procedure because your UDF arguments could refer to other cells as arguments in which case Excel has to re-evaluate the cell dependency graph and recursively re-evaluate functions.

One approach to work around this "feature" is to treat your long-running UDF's as computational "anchors", have them return some helpful inline message immediately sans computation, then have a user-driven Refresh mechanism (e.g. button in Ribbon or Context menu) that then parses the Excel sheets for your UDF anchor references, computes the UDF's, then displays the UDF output below/right of the anchor. This approach works well when you are generating relatively large data sets calling remote services similar to Bloomberg's BDH and want to control the timing of the computations, but I admit it is clunky when it is simply for an inline function. Note that you will likely need to mark your UDF as a IsMacroType=true.

Hope this helps.

-Bishr
Dec 5, 2013 at 5:39 PM
Hi Govert,

I am switching the way I was writing my data so that the UDF has a quick return and uses ExcelAsyncUtil.QueueAsMacro to write data after the UDF returns. I am in the initial stages of trying to implement your asynchronous macro example plus the Google forum article "Update value and formula of cell through Asynchronous function..." and the Resizing Excel UDF Result Arrays example. My relevant code is as follows:

Caller
                        excelRef = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
                        if (excelRef is ExcelReference)
                        {
                            ExcelAsyncUtil.QueueAsMacro(delegate
                            {
                                WriteData data = new WriteData(excelRef, arrayToWrite);
                            });
                        }
Delegate
        #region WriteData
        public class WriteData
        {
            object origScreenUpdating;
            object origSelectionOnActiveSheet;
            object origActiveCellOnActiveSheet;
            object origSelectionOnRefSheet;
            object origActiveCellOnRefSheet;

            public WriteData(ExcelReference refToSelect, object[,] data)
            {
                AddInFacade facade = new AddInFacade();
                try
                {
                    XlCall.Excel(XlCall.xlcEcho, false);

                    string szFormula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, refToSelect);
                    ExcelReference firstCellRef = new ExcelReference(refToSelect.RowFirst, refToSelect.RowFirst, refToSelect.ColumnFirst, refToSelect.ColumnFirst, refToSelect.SheetId);

                    bool bIsFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, refToSelect);
                    if (bIsFormulaArray)
                    {
                    }

                    // write data here

                    bool bIsR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
                    string szFormulaR1C1 = szFormula;
                    if (!bIsR1C1Mode)
                    {
                        // Set the formula into the whole target
                        szFormulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, szFormula, true, false, ExcelMissing.Value, firstCellRef);
                    }

                    // Must be R1C1-style references
                    object ignoredResult;
                    XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, szFormulaR1C1, refToSelect);

                    if (retval != XlCall.XlReturn.XlReturnSuccess)
                    {
                        firstCellRef.SetValue("'" + szFormula);
                    }
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    facade.SetMouseCursor(XlMousePointer.xlDefault);
                }

            }
        }
        #endregion
My problem is that the delegate code locks the workbook if I do not perform the XlCall.TryExcel and goes in an endless loop if I do. Would you offer some pointers on how to get this block to work?
Coordinator
Dec 5, 2013 at 5:53 PM
Hi Lee,

This is a terrible idea. Writing data out to the sheet based on a function call breaks Excel in many ways. You end up with something that is no longer a 'function' in any sense that maters to Excel.
The array resizer example makes sure that the resulting recalculation is stable, and that Excel is left in a consistent state with all the dependencies correct (since the array formula ends up being the right size for the result).

If you have performance concerns for your function being called repeatedly, you should rather implement a caching solution.
Otherwise you are going down a dark and dangerous road.

-Govert
Dec 5, 2013 at 6:04 PM
Hi Govert,

I am attempting to do the Bloomberg BDH functionality (it is a requirement). This means that the cell that contains the formula will show the formula, but other data values are written out.

Even ignoring what I was attempting to do in the first place, and just approaching from a knowledge perspective, I am just trying to use the QueueAsMacro feature (using Visual Studio 2013 and C#) and cannot get that to work. All that the above code is doing is to save a copy of the original function, and then return.

If I am correct, Excel remains locked unless the line of code below is called, but will be in an endless loop if it is. I am attempting to understand why I cannot get the QueueAsMacro to work.
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, szFormulaR1C1, refToSelect);
Coordinator
Dec 5, 2013 at 6:21 PM
Hi Lee,

If you have a range of data and want to use it via a function-style interface, I can help you do something like the array formula approach. Otherwise you leave Excel in a state where it is very hard to figure out what is going on, as you see. As far as I understand it, BDH is not a good model for how to deal with this, and I keep discouraging others from trying to emulate it.

Anyway, I don't think your problem is that QueueAsMacro does not work, but you are not leaving Excel in a state where it will stop evaluating your function. I suspect your function is just being called again and again. It's tricky stuff ...

-Govert
Jun 13, 2014 at 2:03 PM
Hi Lee,

did you get anywhere with this? I've noticed that if you have volatile functions as parameters (e.g. TODAY()) you could easily end up with an infinite loop.

did anyone come up with any good way of catching and preventing this?