Stop Caching Excel dna Function results

[rajinit...@gmail.com]

Hi Govert, 

I have a excel dna function function like below. it is caching results some times. I want caching to be stopped. As the data set will be changing for seconds in the data base i want that to be reflected in the function as well. Please suggest the work around for this problem 

 [ExcelFunction(Name = "MyTest", Description = "MyTest",

            Category = "MyTest",  IsMacroType = true)]

        public static object[,] MyTestFunction([ExcelArgument(AllowReference = false, Name = columnName1, Description = columnName1)] object columnName1,

            [ExcelArgument(AllowReference = true,Name = columnName1, Description = columnName1)] object columnName2, [ExcelArgument(Name = columnName3, Description = columnName3)] bool columnName3, [ExcelArgument(Name = columnName4, Description = columnName4)] bool columnName4)

        {

 

 object[,] array = new object[1, 1];

            var asyncoutput = ExcelAsyncUtil.Run("GetSampleData", new[] { columnName1,columnName2,columnName3,columnName4  }, () => GetSampleData(columnName1,columnName2,columnName3,columnName4 ));

            if (result.Equals(ExcelError.ExcelErrorNA))

            {

       array[0, 0] = "Single Set Result"

                return array;

            }

  }

 [MethodImpl(MethodImplOptions.Synchronized)]

        public static OutPutParser GetSampleData(object columnName1, object columnName2, bool columnName3, bool columnName4)

        {

}

[Govert van Drimmelen]

What version of Excel are you using?

 

If it is Excel 2010, then what you call ‘caching’ might be a consequence of an Excel bug, where the RTD topic is not properly disconnected.

 

The bug is fixed in Excel 2010 SP1 and there is a workaround in the upcoming Excel-DNA version 0.33.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[rajinit...@gmail.com]

Yes it is excel 2010. As Excel- Dna Version 0.33 is released. Can use it with no code change. Or should I change anything in the code as well.

[Govert van Drimmelen]

Excel-DNA v 0.33 should not break anything.

 

You can either update to the pre-release NuGet package, or download the release candidate from GitHub: https://github.com/Excel-DNA/ExcelDna/releases/tag/v0.33.7-rc1.

[Kumar K]

Only this class is throwing error after migrating from 0.31 to 0.33.7. Cannot Inherit from sealed class XlCall. 

Can you suggest on this please

using System;

using ExcelDna.Integration;

namespace MyTest

{

    public class FunctionArrayResizer : XlCall

    {

        // This function will run in the UDF context.

        // Needs extra protection to allow multithreaded use.

        public static object Resize(object[,] array)

        {

            var caller = Excel(xlfCaller) as ExcelReference;

            if (caller == null)

                return array;

            int rows = array.GetLength(0);

            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0)

                return array;

            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&

                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))

            {

                // Size is already OK - just return result

                return array;

            }

            var rowLast = caller.RowFirst + rows - 1;

            var columnLast = caller.ColumnFirst + columns - 1;

            // Check for the sheet limits

            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||

                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)

            {

                // Can't resize - goes beyond the end of the sheet - just return #VALUE

                // (Can't give message here, or change cells)

                return ExcelError.ExcelErrorValue;

            }

            // TODO: Add some kind of guard for ever-changing result?

            ExcelAsyncUtil.QueueAsMacro(() =>

            {

                // Create a reference of the right size

                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);

                DoResize(target); // Will trigger a recalc by writing formula

            });

            // Return what we have - to prevent flashing #N/A

            return array;

        }

        //public static object ListResize(List<CedrickEWindow> array)

        //{

        //    var caller = Excel(xlfCaller) as ExcelReference;

        //    if (caller == null)

        //        return array;

        //    int rows = array.Count;

        //    int columns = 50;

        //    if (rows == 0 || columns == 0)

        //        return array;

        //    if ((caller.RowLast - caller.RowFirst + 1 == rows) &&

        //        (caller.ColumnLast - caller.ColumnFirst + 1 == columns))

        //    {

        //        // Size is already OK - just return result

        //        return array;

        //    }

        //    var rowLast = caller.RowFirst + rows - 1;

        //    var columnLast = caller.ColumnFirst + columns - 1;

        //    // Check for the sheet limits

        //    if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||

        //        columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)

        //    {

        //        // Can't resize - goes beyond the end of the sheet - just return #VALUE

        //        // (Can't give message here, or change cells)

        //        return ExcelError.ExcelErrorValue;

        //    }

        //    // TODO: Add some kind of guard for ever-changing result?

        //    ExcelAsyncUtil.QueueAsMacro(() =>

        //    {

        //        // Create a reference of the right size

        //        var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);

        //        DoResize(target); // Will trigger a recalc by writing formula

        //    });

        //    // Return what we have - to prevent flashing #N/A

        //    return array;

        //}

        public static double[,] ResizeDoubles(double[,] array)

        {

            var caller = Excel(xlfCaller) as ExcelReference;

            if (caller == null)

                return array;

            int rows = array.GetLength(0);

            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0)

                return array;

            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&

                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))

            {

                // Size is already OK - just return result

                return array;

            }

            var rowLast = caller.RowFirst + rows - 1;

            var columnLast = caller.ColumnFirst + columns - 1;

            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||

                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)

            {

                // Can't resize - goes beyond the end of the sheet - just return null (for #NUM!)

                // (Can't give message here, or change cells)

                return null;

            }

            // TODO: Add guard for ever-changing result?

            ExcelAsyncUtil.QueueAsMacro(() =>

            {

                // Create a reference of the right size

                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);

                DoResize(target); // Will trigger a recalc by writing formula

            });

            // Return what we have - to prevent flashing #N/A

            return array;

        }

        static void DoResize(ExcelReference target)

        {

            // Get the current state for reset later

            using (new ExcelEchoOffHelper())

            using (new ExcelCalculationManualHelper())

            {

                ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

                // Get the formula in the first cell of the target

                string formula = (string)Excel(xlfGetCell, 41, firstCell);

                bool isFormulaArray = (bool)Excel(xlfGetCell, 49, firstCell);

                if (isFormulaArray)

                {

                    // Select the sheet and firstCell - needed because we want to use SelectSpecial.

                    using (new ExcelSelectionHelper(firstCell))

                    {

                        // Extend the selection to the whole array and clear

                        Excel(xlcSelectSpecial, 6);

                        ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);

                        oldArray.SetValue(ExcelEmpty.Value);

                    }

                }

                // Get the formula and convert to R1C1 mode

                bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);

                string formulaR1C1 = formula;

                if (!isR1C1Mode)

                {

                    object formulaR1C1Obj;

                    XlReturn formulaR1C1Return = TryExcel(xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell);

                    if (formulaR1C1Return != XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError)

                    {

                        string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);

                        Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");

                        firstCell.SetValue("'" + formula);

                        return;

                    }

                    formulaR1C1 = (string)formulaR1C1Obj;

                }

                // Must be R1C1-style references

                object ignoredResult;

                //Debug.Print("Resizing START: " + target.RowLast);

                XlReturn formulaArrayReturn = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);

                //Debug.Print("Resizing FINISH");

                // TODO: Find some dummy macro to clear the undo stack

                if (formulaArrayReturn != XlReturn.XlReturnSuccess)

                {

                    string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);

                    Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array.");

                    // Might have failed due to array in the way.

                    firstCell.SetValue("'" + formula);

                }

            }

        }

    }

    // RIIA-style helpers to deal with Excel selections    

    // Don't use if you agree with Eric Lippert here: http://stackoverflow.com/a/1757344/44264

    public class ExcelEchoOffHelper : XlCall, IDisposable

    {

        object oldEcho;

        public ExcelEchoOffHelper()

        {

            oldEcho = Excel(xlfGetWorkspace, 40);

            Excel(xlcEcho, false);

        }

        public void Dispose()

        {

            Excel(xlcEcho, oldEcho);

        }

    }

    public class ExcelCalculationManualHelper : XlCall, IDisposable

    {

        object oldCalculationMode;

        public ExcelCalculationManualHelper()

        {

            oldCalculationMode = Excel(xlfGetDocument, 14);

            Excel(xlcOptionsCalculation, 3);

        }

        public void Dispose()

        {

            Excel(xlcOptionsCalculation, oldCalculationMode);

        }

    }

    // Select an ExcelReference (perhaps on another sheet) allowing changes to be made there.

    // On clean-up, resets all the selections and the active sheet.

    // Should not be used if the work you are going to do will switch sheets, amke new sheets etc.

    public class ExcelSelectionHelper : XlCall, IDisposable

    {

        object oldSelectionOnActiveSheet;

        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;

        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)

        {

            // Remember old selection state on the active sheet

            oldSelectionOnActiveSheet = Excel(xlfSelection);

            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select

            string refSheet = (string)Excel(xlSheetNm, refToSelect);

            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select

            oldSelectionOnRefSheet = Excel(xlfSelection);

            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection

            Excel(xlcFormulaGoto, refToSelect);

        }

        public void Dispose()

        {

            // Reset the selection on the target sheet

            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            // Reset the sheet originally selected

            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);

            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            // Reset the selection in the active sheet (some bugs make this change sometimes too)

            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);

[Govert van Drimmelen]

That’s an interesting breaking change that I did not know about.

 

In the past I used the trick of deriving from XlCall to get rid of the class qualifiers on the static calls (like Excel(xlfCaller)).

 

I’ve marked the XlCall class as “public static” to enable the new C# 6.0 using static feature.

So if you’re using Visual Studio 2015, you’ll be able to say:

 

    using static ExcelDna.Integration.XlCall;

 

and then remove the derivation from XlCall, and everything should compile.

 

If you’re not using Visual Studio 2015, you have to change the Excel calls to XlCall.Excel, and xlfCaller to XlCall.xlfCeller etc.

 

I’ll have to think about this a bit – it’s not a breaking change I know about or would like to leave in for the release, so something will have to change.

[Govert van Drimmelen]

OK – I see what happened.

 

The preview version of C# 6.0 required that the class be declared as “static” for the new “using static” feature to be used. That’s why I changed the XlCall class to “static”.

 

But in the release version of C# 6.0, the class is no longer required to be “static”, so I will remove this modifier from the XlCall class again.

 

So the release version of Excel-DNA v 0.33 will not have the problem you report.

 

Thank you for finding this!

[Kumar K]

Hi Govert,

Thanks for the advice. Any dates for the next release of the 0.33. I see another peice of error coming in after the XLcall.excel got resolved : 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'ToString' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'DisplayErrorTemplateMessage' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'Check' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'Check' - previous registration will be overwritten. 

Registration [Error] Repeated function name: 'Check' - previous registration will be overwritten. 

Regards,

Manoj

...

[Govert van Drimmelen]

Hi Manoj,

 

Can you explain a bit more about your configuration? (In particular, what does your .dna file look like?)

 

I think what you see is ‘by design’.

 

By default, Excel-DNA looks at all the <ExternalLibrary …/> tags in the .dna file, and in each of those assemblies, it exports all public static methods with compatible signatures. (Note that assemblies added as <Reference ../> assemblies are not exported).

 

The messages you see indicate that you have different classes declaring a public static method called ToString / DisplayErrorTemplateMessage / Check.

In the past they’d all be registered with Excel, but only the last one registered would ‘win’ and be used.

This is unlikely to be what you wanted - probably these methods should all be ‘internal’.

 

Does that agree with what you see in your code?

 

You can also control exactly what methods get registered by setting the ExplicitExports tag in the .dna file:

<DnaLibrary Name="Test Add-in" RuntimeVersion="v4.0">

    <ExternalLibrary Path="TheLibrary.dll" ExplicitExports="true" />

  </DnaLibrary>

 

Then only functions explicitly marked with an [ExcelFunction] attribute will be exported.

 

I think showing the Error in this case is an improvement, because it highlights a confusion. Also, it appears immediately and consistently for your add-in, so you can deal with it when upgrading – it’s not a change that will only appear at runtime.

 

What do you think?

--

[Kumar K]

I agree to the points which you have mentioned. i ahve chanegd them 

previously it used to be like this

<DnaLibrary Name="Myaddinname" RuntimeVersion="v4.0">

  

  <ExternalLibrary Path="AddinUDF.dll" LoadFromBytes="true" Pack="true" />

  <ExternalLibrary Path="MyService.dll" LoadFromBytes="true" Pack="true" />

  <ExternalLibrary Path="Myentiites.dll" LoadFromBytes="true" Pack="true" />

  <ExternalLibrary Path="Shared.dll" LoadFromBytes="true" Pack="true" />// the methods which are shown in the error dialog are from this library

</DnaLibrary>

I changed this to the below

<DnaLibrary Name="Myaddinname" RuntimeVersion="v4.0">

   

  <ExternalLibrary Path="AddinUDF.dll" ExplicitExports="true" />

   <ExternalLibrary Path="MyService.dll" ExplicitExports="false" />

  <ExternalLibrary Path="Myentiites.dll" ExplicitExports="false" />

  <ExternalLibrary Path="Shared.dll" ExplicitExports="false" />

</DnaLibrary>

After changing to this. I can see them still same error messages and i can fx category they are  registered. is there any way that i can compleltley remove the preious registrations i hav med 

...

[Govert van Drimmelen]

I think you actually want the .dna file to look like this:

 

<DnaLibrary Name="Myaddinname" RuntimeVersion="v4.0">

 

  <ExternalLibrary Path="AddinUDF.dll" LoadFromBytes="true" Pack="true" ExplicitExports="true” />

  <Reference Path="MyService.dll" LoadFromBytes="true" Pack="true" />

  <Reference Path="Myentiites.dll" LoadFromBytes="true" Pack="true" />

  <Reference Path="Shared.dll" LoadFromBytes="true" Pack="true" />

</DnaLibrary>

 

Only the library that has the UDFs should be an ExternalLibrary, all the others are just Reference libraries.

--

[Kumar K]

It Worked like a charm,

I have another issue. The dna  function is getting recalculated even something is changed in the other sheet. Function syntax is as same ranjith posted

...

[Govert van Drimmelen]

I presume you mean this function:

 

[ExcelFunction(Name = "MyTest", Description = "MyTest", Category = "MyTest",  IsMacroType = true)]

public static object[,] MyTestFunction(

        [ExcelArgument(AllowReference = false, Name = columnName1, Description = columnName1)] object columnName1,

        [ExcelArgument(AllowReference = true, Name = columnName1, Description = columnName1)] object columnName2,

        [ExcelArgument(Name = columnName3, Description = columnName3)] bool columnName3,

        [ExcelArgument(Name = columnName4, Description = columnName4)] bool columnName4)

        { …}

 

Excel considers the function as volatile by default, since you have both marked the function with IsMacroType=true and have an argument marked AllowReference=true.

 

Do you need the reference for columnName2, or can you replace with an object[,] parameter to just get the values?

--

[Kumar K]

Hi Govert, 

Still the caching issue exists even after upgrading to 33.7 pre version.

I am able to see the Function is getting hitted. But from the below line of code it skips and shows the cached result. I am using it mainly to run the code in thread.

     var asyncoutput = ExcelAsyncUtil.Run("GetSampleData", new[] { columnName1,columnName2,columnName3,columnName4  }, () => GetSampleData(columnName1,columnName2,columnName3,columnName4 )); 

 [MethodImpl(MethodImplOptions.Synchronized)]

        public static OutPutParser GetSampleData(object columnName1, object columnName2, bool columnName3, bool columnName4)

        {

}

Any suggestions on this please 

Regards,

Manoj

...

[Govert van Drimmelen]

Hi Manoj,

Can you explain what you mean by this: "I am using it mainly to run the code in thread." ?

Would it be possible to make a small alone-standing example with detailed instructions so that I can reproduce what you see on my machine?

-Govert

[Kumar K]

Hi Govert,

Created a sample solution. you can download from the below link : 

https://drive.google.com/file/d/0B4l7LmNW9qxvRTRsamJ4SU1RZkk/view?usp=sharing

Instructions to run: 

1. Run the solution. you should be able to see SampleDataTest function like below 

 [ExcelFunction(Name = "SampleDataTest", Description = "SampleDataFunctionDescription",

          Category = "FunctionCategrory", HelpTopic = "SampleDataHelpTopic", IsMacroType = true)]

        public static double SampleDataTest([ExcelArgument(AllowReference = true, Name = "SampleDataParamColumnName", Description = "SampleDataParamColumnNameDesc")] object columnName,

            [ExcelArgument(AllowReference = true, Name = "SampleDataParamFilters", Description = "SampleDataParamFiltersDesc")] object filters, [ExcelArgument(Name = "SampleDataParamHeaders", Description = "SampleDataParamHeadersDesc")] bool headers, [ExcelArgument(Name = "SampleDataParamDistinct", Description = "SampleDataParamDistinctDesc")] bool distinct)

        {

            Logger.Debug("Entered into Class1.cs - SampleDataTest");

            var result = ExcelAsyncUtil.Run("GetFunctionSampleData", new[] { columnName, headers, distinct }, () => GetFunctionSampleData(columnName, headers, distinct));

            Logger.Debug("Exiting From Class1.cs - SampleDataTest");

            return (double)result;

        }

 

2. Pass the parameters and you can see the result which the function is getting from 

[MethodImpl(MethodImplOptions.Synchronized)]

        public static double GetFunctionSampleData(object headerNames, bool hideHeaderinfo, bool distinct)

        {

            Logger.Debug("Entered Class1.cs - GetFunctionSampleData");

            Logger.Debug("Exiting from Class1.cs - GetFunctionSampleData");

            return 100;

        }

 

example : = 1. first time =SmapleTestData(1,2) it returns 100 by calling GetFunction sampledata method

                   2. Second time when i call SmapleTestData(1,2) it returns 100 without calling method. 

I my case i need this method needs to be called everytime

[Kumar K]

I am using Excel 2010 sp2 

[Govert van Drimmelen]

Thank you - I wan able to download, compile and run the sample.

It works the way I expect, but I think I know what you mean by 'caching' the value.

Here's what I see:

* I enter =SampleTestData(1,2) into a cell

* SampleTestData gets called, and then on another thread GetFunctionSampleData is called.

* The cells displays #VALUE, because the call to ExcelAsyncUtil.Run(...) in SampleTestData has returned #N/A, and you are casting to a double which throws an InvalidCastException, caught be Excel-DNA and returned as #VALUE.

* The thread calling GetFunctionSampleData is meanwhile sleeping for the 10 seconds.

**** Here is the time where the situation might be confusing, discussed below.

* Then the thread sleep is complete and GetFunctionSampleData returns the value 100, which Excel-DNA stores, which telling Excel the cell should be updated.

* Then Excel calls SampleTestData again, and it returns the internally stored value (100).

* The cell now displays 100.

If after this complete sequence you put =SampleTestData(1,2) into another cell, or recalculate that cell, the whole sequence will run again, including the call the GetFunctionSampleData.

Now in the time ***** between the first call to ExcelAsyncUtil.Run and the final call to ExcelAsyncUtil.Run (when the cells shows #VALUE) if you enter the formula =SampleTestData(1,2) into another cell, it will call SampleTestData but not GetFunctionSamplesData. This is because the parameters are the same (checked according to the 'topic' determined by the array you send in to ExcelAsyncUtil.Run) and there is an outstanding call. This cell will immediately return #VALUE. When the Sleep completes the SampleTestData call for both cells will immediately return the result value (since they have the same 'topic'). Now the topic is complete, so entering =SampleTestData(1,2) again will again run GetFunctionSampleData.

So in your terms, the function is 'cached' while the async call is outstanding, but you should not find that it is 'cached' after the async call has completed and the cells have the result values in.

Can you check whether the behaviour you see agrees with my description above, and describe what behaviour you desire?

-Govert

[Kumar K]

Hi Govert,

Sorry to say you were on the other side. I have made some changes to get clear understanding of the bug.

Now i made the function to return datetime so that you can get clear understanding of the bug. And I have removed the sleep as well. Which was confusing.

Please see the attached picture. 

1. I have First executed the function at 10:48 pm. which is at A0

2. Second call i made in different cell i.e is B3 at 10:51 but it is still showing 10:48 it self.

Update solution link : https://drive.google.com/file/d/0B4l7LmNW9qxvX0NWNDNGVjBwdTQ/view?usp=sharing

[Govert van Drimmelen]

OK - I think I see a problem on my side.

It only goes wrong when you use it as an array formula (with Ctrl+Shift+Enter giving you the {...} formula).

Is that what you find too?

-Govert

[Kumar K]

Yes Govert exactly

Any work around for this.or any alternative for excelaysncutil.run()

[Govert van Drimmelen]

I see the problem with RTD functions called as array formulae not being disconnected properly was discussed here: https://groups.google.com/d/topic/exceldna/62cgmRMVtfQ/discussion

In that thread Laary suggests that setting IsMacroType=false makes the problem go away.

However, in my test now I don't see that the array problem goes away with IsMacroType=false.

The RTD mechanism of Excel, through which the async works, does not work well with array formulas (even apart from this problem).

You might try the native async, though the behaviour is very different and I can't remember whether arrays are supported.

The alternative I suggest would be to split those functions into two - one function is a single-cell async function that does that async call. The async call populates in internal data structure, and then returns a handle of some sort.

Then you can make an array access function that takes the handle and returns the internal results. It's a bit more clumsy to use on the sheet, but should work well.

I'll try to check if I find any other references to this particular Excel bug.

-Govert

[Kumar K]

Hi Govert, 

I had been playing with this issue for the past couple of days and I now understood Why the ExcelAsyncutil.Run is made to hit only once with array type. 

These are my observations : 

1. After returning the result to excel. Excel is sending one more call back to the function. Then the ExcelAsyncUtil.run is preventing the call back to the server.

Workaround not the idle but  currently i am working is: 

1. added a parameter which takes minute as the input parameter. So that if the  Excel call comes back with in the same minute ignoring that call. 

Problem in this approach. As the function is volatile. change in sheet after x some time is triggering all the functions once again.

Any inputs of your would be helpful. 

Regards,

Manoj 

[Gopi Annathambu]

Hi,

I was actually web developer, this is my first project in Excel Plugin. And I have decided to use Excel-DNA instead of Macros which was used earlier for the same purpose.

We are using ExcelDNA for last 6 months for our project UDF Development,  Now we have struck with same issue which Kumar K have discussed below.  "Excel-DNA UDF Array formula is not behaving Volatile even though isVolatile=True" or we can say "Excel-DNA Caching the Array Formula results". 

We have IsVolatile=True, and Ex Array Formula  is

 =GetData(C5)  - And if we change the C5 Value like below

C5 = 10 ( Loads new real time data by calling AsyncUtil.Run())

C5 = 20 ( Loads new real time data by calling AsyncUtil.Run())

C5 = 10 ( Loads new real time data by calling AsyncUtil.Run() - For the First Time)

C5 = 30 ( Loads new real time data by calling AsyncUtil.Run())

C5 = 10 ( Its not invoking AsyncUtil.Run() , its just loading from Excel Cache)

C5 = 20 ( Its not invoking AsyncUtil.Run() , its just loading from Excel Cache)

As this our project core functionality, Our Project Management says, if we are not fixings this issue, they are asking to rollback entire 6 months development effort try with other plugin (AddInExpress).  So, Now we have to prove this is an Excel Issues with Array Formula.  If you also this think its an Excel Issue and you have any reference for the same please let us know.

Otherwise , If you have noticed the same as part of Excel-DNA please let us know if you have any solution for the same. or any work around for this issue.

Thanks for your help.

Regards

Gopi

[Govert van Drimmelen]

Hi Gopi,

An Excel-DNA async function that you make with ExcelAsyncUtil.Run(...) is based on the Excel RTD functionality, and will have the same restrictions and bugs as Excel RTD functions.

Excel has an alternative async function model since Excel 2010, which Excel-DNA fully supports. This works through the ExcelAsyncHandle type. The behaviour is quite different, though.

According to Microsoft, an Excel RTD function cannot return an array. See: https://support.microsoft.com/en-us/kb/286258

When a wrapper around an RTD function returns an array, we know the behaviour of Excel is quite strange. The wrapper function will get called multiple times, once for every cell in the array, and once for the overall array.

From the earlier discussions in this topic, it seems like there is another issue with RTD functions called via array formulas - the RTD topic Disconnect is not called correctly be Excel. This reminds me a bit of a bug that was in the first release version of Excel 2010, where Disconnect was not properly called in some circumstances.

We could make a reproduction of the issue without using Excel-DNA - do you have some support channel to Microsoft to get some feedback from them if we do this?

The workaround is as I've discussed before - split the async array function into two parts, one that is async and returns a handle, and the other that is the array function that unpacks the data pointed at by the handle. You'd have to use a separate cell to hold the handle, in this case.

-Govert

[Gopi Annathambu]

Hi Govert,

Thanks for your reply and guideline. We will try with what ever work around you have suggested.  Yes , We have support Channel with Microsoft. If we can reproduce the issue that can be submitted to Microsoft. We can do the same.

Regards

Gopi

[Govert van Drimmelen]

Hi Gopi,

OK - I will investigate further, and see if the issue is in Excel-DNA or whether we can make a reproduction without Excel-DNA involved, that you could report to Microsoft.

It's a tricky interaction, but maybe we can do something on the Excel-DNA side.

-Govert

[Govert van Drimmelen]

Hi Gopi,

I've put together an overview and instructions for a reproduction that does not depend on Excel-DNA at all - it just makes an RTD server in C# and the wrapper function in VBA.

I would appreciate if you could have a look, and hopefully use it to open a support ticket with Microsoft on this issue.

I'd be very curious to know what they say about this.

You're welcome to contact me directly about this too, should I need to add any details or background.

Regards,

Govert

On Monday, 30 November 2015 20:53:12 UTC+2, Gopi Annathambu wrote:

[Govert van Drimmelen]

Ah - you can find it here: https://gist.github.com/govert/03df749f38b9582b1217

-Govert

[Gopi Annathambu]

Hi Govert,

Thanks for your detail, will update you after submitting to Microsoft.

[Gopi Annathambu]

Hi Govert,

I am sure you have already provided your thought on this. Still if you could help on this, Is there any other way, we trigger some value change that will force the AsyncUtil.Run() every time.  

I have followed this thread 

https://groups.google.com/forum/#!msg/ExcelDna/fCeH8wtGhFM/P-4-kRHg0acJ

Have tried using "reference" to force call every time . But its running on Infinite Loop. Is there any other approach that I can trigger AsyncUtl.Run() call every time?.

Regards

Gopi