MakeArray Sample

Apr 8, 2013 at 3:08 PM
Hi

I tried to test the single-cell array function.

"MakeArray" (present in the sample "Async" project with Excel DNA)
public static object MakeArray(int rows, int columns)
        {
            object[,] result = new string[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i, j] = string.Format("({0},{1})", i, j);
                }
            }

            return result;
        }
This should return a whole array.

I tired it with CTR+SHIFT+ENTER and without. It only returns the first cell result : (0,0)

Am I missing something ?
Coordinator
Apr 8, 2013 at 3:15 PM
Hi,

You need to select a range of the right size, then type in the formula, then press Ctrl+Shift+Enter.
Is that how you're trying it?

-Govert
Apr 8, 2013 at 3:22 PM
No. I didn't select a range. I was mistaken.
Now It works.

this leads us to this Question :

Can I make a function in a single cell =GenerateArray( ) which will create a whole table (first value of the array will be in the cell where I write a function) ? =>(Bloomberg style)

I've read many threads but As I'm new to Excel I don't get it.

I don't want to make and Add-in neither a macro and I don't know my array size.
Apr 8, 2013 at 3:27 PM
I tried this :
public static string TryCreateAcell()
        {
            var oXL = (Excel.Application)ExcelDnaUtil.Application;

            Excel.Workbook oWB;
            Excel.Worksheet oSheet;

            oWB = (Excel.Workbook)oXL.ActiveWorkbook;
            var activeSheet = (Excel.Worksheet)oWB.ActiveSheet;
            var activeSheetName = activeSheet.Name;

            Excel.Range rng = (Excel.Range)oXL.ActiveCell;

            //get the row and column details
            int row = rng.Row;
            int column = rng.Column;

            activeSheet.Cells[10, 10] = "hi";            

            return "done";
        }
But I think i'm not allowed to modify cells like this after reading ( This discussion

I Hope you have understood my goal.

Thank you in advance for your help.
Coordinator
Apr 8, 2013 at 6:24 PM
Edited Apr 8, 2013 at 6:25 PM
Hi,

Excel does not allow a worksheet function to alter the sheet.

With Excel-DNA you can implement a workaround. The example Distribution\Samples\ArrayResizer.dna shows how this can be done. Basically it schedules the work to run as a macro after the calculation is completed. This queueing of work to run as a macro later is exposed by the ExcelAsyncUtil.QueueAsMacro helper method. (Which needs a call to ExcelAsyncUtil.Initialize() in your AutoOpen() implementation.)

The ArrayResizer sample doesn't work exactly like Bloomberg - I prefer to put an array formula of the right size into the cells, rather than just pasting the data as the Bloomberg add-in does. This respects the calculation dependency tree of Excel. One could make other implementations using the Excel-DNA helper though.

To try it, put a copy of ExcelDna.xll next to the ArrayResizer.dna sample, and rename it to ArrayResizer.xll. Then open in Excel and run the =MakeArrayAndResize(...) function, or wrap your own function as =Resize(MyArrayFunction(...)).

I hope this helps.

Regards,
Govert