Passing an array of data to MInverse function and reviewing data using CalcEngine only without Spread

Posted by: fubar166 on 8 September 2017, 3:08 pm EST

  • Posted 8 September 2017, 3:08 pm EST

    Hello I'm trying to perform MInverse on a 3x3 matrix.  I had been
    doing this in Excel but having a hard time trying to do this with Spread
    within VB.NET.

    There is some example data from my project below.  Within VBA I would pass an array of data and it would return an array of data.  I have been looking around for an example in the documentation and on this forum but I can't find one.

    I am able to get MInverse to calculate correctly within spread and reviewing the result with Index function but was hoping I could be shown an example of how it can be done within the code.

    This is how I'm currently attempting to load into CalcEngine.Minverse function:

    Dim spIndex As FarPoint.CalcEngine.IndexFunctionInfo

    Dim spMInverse As FarPoint.CalcEngine.MInverseFunctionInfo

    Dim oArgs(,) As Object

                oArgs(0, 0) = 5186098731.6
                oArgs(0, 1) = 727853662.52
                oArgs(0, 2) = -587910789.75
                oArgs(1, 0) = 727853662.52
                oArgs(1, 1) = 2883789301.73
                oArgs(1, 2) = -422415346.07
                oArgs(2, 0) = -587910789.75
                oArgs(2, 1) = -422415346.07
                oArgs(2, 2) = 117239889.35

     spMInverse.Evaluate(oArgs) '  It doesn't like a 2 dimensional array, this is where I am stuck  if I make it one dimension not sure how it will know it is 3x3??


    This link has an image showing how I was able to get it to work in Spread http://postimg.org/image/4ld0l745v/ 

    The input values and the result within Excel











































    The values for the 3x3 matrix  
    5186098731.6 727853662.5 -587910789.8
    727853662.5 2883789301.7 -422415346.1
    -587910789.8 -422415346.1 117239889.4
    The Minverse result
     
    0.00000000122187744963 0.00000000124750453544 0.00000001062198201786
    0.00000000124750453544 0.00000000200797692405 0.00000001349047370044
    0.00000001062198201786 0.00000001349047370044 0.00000011040065821805


    Thank you for your help

    Tim

     

  • Replied 8 September 2017, 3:08 pm EST

    Tim,

    The syntax for the Excel's MINVERSE and INDEX functions is...

        MINVERSE(array)
        INDEX(array,row_num,column_num);

    In Spread, the function's argument list is passed to the function's Evaluate method as array of objects. The first argument (to MINVERSE and INDEX functions) can be passed as an instance of either CalcArray or CalcReference. The CalcArray and CalcReference classes are abstract classes. You need to create your own concrete implementation of the abstract class. For example, the following class is an implementation of CalcArray that stores the values as a two dimensional array of objects.

    class MyCalcArray : CalcArray
    {
        private object[,] values;
        public MyCalcArray(object[,] values)
        {
            this.values = values;
        }
        public override int RowCount
        {
            get { return values.GetLength(0); }
        }
        public override int ColumnCount
        {
            get { return values.GetLength(1); }
        }
        public override object GetValue(int row, int column)
        {
            return values.GetValue(row, column);
        }
    }

    You could then use the following C# code to call the MINVERSE and INDEX functions. The code stores the array values in a two dimensional array of objects which is then wrapped by the MyCalcArray object which is then passed as the first (and only) argument to the MINVERSE function. The MINVERSE result is then passed as the first argument to the INDEX function. The INDEX results are display to the debug output console.

    object[,] values = new object[3,3];
    values[0, 0] = 5186098731.6;
    values[0, 1] = 727853662.52;
    values[0, 2] = -587910789.75;
    values[1, 0] = 727853662.52;
    values[1, 1] = 2883789301.73;
    values[1, 2] = -422415346.07;
    values[2, 0] = -587910789.75;
    values[2, 1] = -422415346.07;
    values[2, 2] = 117239889.35;

    MyCalcArray array = new MyCalcArray(values);

    FunctionInfo spMInverse = FunctionInfo.MInverseFunction;

    object[] inverseArgs = new object[1];
    inverseArgs[0] = array;

    object inverseResult = spMInverse.Evaluate(inverseArgs);

    FunctionInfo spIndex = FunctionInfo.IndexFunction;

    object[] indexArgs = new object[3];
    indexArgs[0] = inverseResult;

    object indexResult;

    for (int r = 0; r < 3; r++)
    {
        indexArgs[1] = r + 1;
        for (int c = 0; c < 3; c++)
        {
            indexArgs[2] = c + 1;
            indexResult = spIndex.Evaluate(indexArgs);
            System.Diagnostics.Debug.WriteLine(indexResult.ToString());
        }
    }

    Note that the C# code is using zero based indices where as Excel's INDEX function uses one based indices. Thus, 1 is added to r and c before calling INDEX function's Evaluate method.

  • Replied 8 September 2017, 3:08 pm EST

    Thank you very much that worked!  If there is a spot somewhere in the documentation that has information about passing data to CalcEngine functions I am sorry that I couldn't find it but if there isn't I think it would be worth adding.

    Thank you again for prompt reply.

    Tim

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels