Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
135
How to retrieve a WorksheetRegion from an ExcelCalcValue
posted

Hello,

I've defined a custom function in Excel CalcEngine.

Some of the function parameters are Regions defined in Excel

=MyFunction(125,A1:A23,D5:D56)

In my code, when I receive the parameters, they are ExcelCalcValue.

How can I get the WorksheetRegion from the ExcelCalcValue ?

The Value property returns a Infragistics.Documents.Excel.FormulaUtilities.CalcEngine.RegionCalcReference type, which is not accessible, so I can't cast the result and access its Region property.

The GetResolvedValue() or ToArrayProxy returns a Infragistics.Documents.Excel.FormulaUtilities.CalcEngine.RegionCalcReference.RegionArrayProxy which is not accessible.

ToString() returns the class Type

Is there any way to retrieve the region ?

Thank you

  • 30750
    Verified Answer
    Offline posted

    Hello Arnaud,

    I have been investigating into the behavior you are looking to achieve to get a WorksheetRegion from an ExcelCalcValue, and while you cannot really get it directly from the ExcelCalcValue, you can get it from the ExcelCalcNumberStack in the Evaluate() method of your custom function.

    The Excel Engine is smart enough to know when a region is placed within the parameters of the function used, and so you can get the parameter corresponding to a Region by using the following code:

            protected override ExcelCalcValue Evaluate(ExcelCalcNumberStack numberStack, int argumentCount)
            {
                for(int i=0; i<numberStack.Count(); i++)
                {
                    var argument = numberStack.Pop();
                    if (argument.IsReference)
                    {
                        var argReference = argument.ToReference();
                        var region = argReference.Context;
                    }
                }
    
                return new ExcelCalcValue(1000);
            }        

    I hope this helps. Please let me know if you have any other questions or concerns on this matter.