Returning bigger matrix formula

Jan 19, 2015 at 1:31 PM
Hi Govert/Excel DNA users,
I would like to ask a question:
Is it possible to return bigger matrix than selected area/cells for given matrix formula.

Use case:
I have Excel DNA UDF that in runtime will resolve how many row will be returned, for instance:
        [ExcelFunction(IsVolatile=false)]
        public static object[,] GetSurnames(string firstName)
        {
            string[] surnames = Database.GetSurnames(firstName);

            object[,] resultArray = new object[surnames.Length,1];

            for (int i = 0; i < surnames.Length; i++)
            {
                resultArray[i,0] = surnames[i];
            }

            return resultArray;
        }
So lets say, that user selected 3 cells in 3 rows (3x1), entered formula and pressed ctrl + shift + enter.

UDF will process parameter and get from DB lets say 5 results which I want to return as result of matrix fuction as 5x1 matrix.

So is it possible to change size of return matrix so it wasnt be limited by selected area by user ?
Or is it possible to change selected area before returning object array by UDF ?

Thanks for any reply
Best regards
Bittmann
Coordinator
Jan 19, 2015 at 1:54 PM
Hi Bittmann,

Have a look at the ArrayResizer.dna sample in the distribution.
It represents my best suggestion for how to deal with this. It checks whether the formula is called from an array of the same size as the function result, and if not, automatically resizes the calling array formula.

-Govert
Feb 12, 2015 at 5:40 AM
Edited Feb 12, 2015 at 5:41 AM
Is there a way to turn off the formula array after the resize is complete? Users seem to hate these formula arrays and want to replicate the bloomberg type functionality.
Feb 17, 2015 at 12:02 AM
I think i was able to figure this out as i am getting more familiar with ExcelDNA. In my case i wouldn't use a formula array at all and instead simply get a reference to the the target cell and set its value to my array, then set the formula for the first cell. The catch here is that setting the formula will trigger a never ending loop..so i use a HashSet to maintain an internal set of callers that are currently being resized and exit if the caller exists already in the set.