Returning an unknown array size - and returning a new worksheet

[SmeeZee]

Hi there

I am looking for some advise. I want to write two UDFs as follows:

1 - It is passed a parameter and then returns a single dimension array
of values, however the size of the array is unknown. I have written a
simple function:

public static double[] gettest()
{
double[] r = new double[] {1,2,3};
return r;
}

Which populates the cells, when I select them. But if I don't know the
size of the array I don't know how many cells to select. Is there
another way to do this? I can't find any? If you know of any
documentation on this, I would really like to read it. My only thought
as a work around for this is to write another function which returns
the size of the array, not the best though.

2 - I want a Ribbon / File menu which creates a new worksheet and
populates the sheet based on the selection and the multi-dimensional
array returnsed. Again, I won't know how big the array is (a similar
question to the first).

Interested to get your thoughts on the best way to accomplish this?

Thanks

[Govert van Drimmelen]

Hi,

Your two questions are very different, since the first concerns a
worksheet function and the second a macro.

For the worksheet function, your function will return values into the
calling cell - or into the cells of the calling range if you enter it
as an array formula. In Excel's calculation model your worksheet
function is not supposed to change other parts of the sheet, and Excel
tried pretty hard to prevent it. If you really want to populate parts
of the sheet that did not call your function, there are some hacks to
do this, but it is not recommended, and is certainly not something I
would recommend unless you have a lot of experience building add-ins
for Excel.

For the macro case, where you want to create some output (maybe based
on the current selection) and then put stuff into a new worksheet, you
are on much safer ground. When you click the menu, Excel is ready for
you to change stuff. There are two APIs available in this context:
1. The Automation COM interfaces, as you might know from VBA. The only
issue is how to get hold of the root Application object which is
conveniently always around in VBA. In your Excel-Dna code you call
ExcelDna.Integration.ExcelDnaUtil.Application to get the right
Application object. From there you can call
xlApp.ActiveWorkbook.Worksheets.Add(...) etc. This will be easier if
you are familiar with VBA. You can use VB.NET or the dynamic keyword
in C# 4 for late binding, or do early binding if you reference the
Excel interop assembly.
2. The C SDK interface, which you access through the
ExcelDna.Integration.XlCall.Excel(...) helper function. This will be
easier if you are familiar with the C API - only you don't have to
deal with XLOPERs and funny data type wrappers. To insert a new sheet
you can try XlCall.Excel(XlCall.xlcWorkbookInsert, 1). Then to
populate the data use the ExcelReference type to set up a reference,
and SetValue(myValues) to set the contents. I put an example of
reading data from the selection and pasting into another sheet here:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-objects

I hope this gives you some ideas.

Govert

[SmeeZee]

Hi Govert

Thank you for your feedback, it is most helpful (especially the
stackoverflow example). I am exploring that code and am now looking to
understand the function XlCall.xlfGetWorkbook (as I want to find the
name of a new sheet I have just added). I found this posting:
http://groups.google.co.uk/group/exceldna/browse_thread/thread/8db8cda3b8187be8
but I still a little confused.

Just to explain, I want to create a new sheet:

XlCall.Excel(XlCall.xlcWorkbookInsert, 1);

And then I want to populate that sheet with the data held in my multi-
dimensional array, but I need to work out the sheetId I have just
added (hence looking at XlCall.xlfGetWorkbook).

Could you advise how I resolve the new sheet name that has been added?

Thank you.

Anthony

> reading data from the selection and pasting into another sheet here:http://stackoverflow.com/questions/3840270/fastest-way-to-interface-b...

[Govert van Drimmelen]

Hi Anthony,

xlcWokbookInsert will add the new sheet to the left of the current
sheer, and make it the active sheet.

So you could make a new sheet and get it's name like this:
XlCall.Excel(XlCall.xlcWorkbookInsert);
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
XlCall.Excel(XlCall.xlSheetId));

The xlSheetId call normally takes a sheet name, but uses the current
sheet by default. It returns an ExcelReference with the correct
SheetId filled in, which you can pass to xlSheetNm to get the name.

As discussed in the previous thread you point to, if multiple sheets
are selected, the xlcWorkbookInsert call will add as many new sheets.
Maybe you could do xlcWorkbookSelect to select only a particular
sheet, before you call WorkbookInsert. xlcGetWorkbook will allow you
to get a list of the sheets and the current sheet selection.

Going down this road, you will certainly need to get used to the Excel
4 macro help file, which can be downloaded from here:
http://support.microsoft.com/kb/128185/EN-US/

Regards,
Govert

On Oct 28, 7:42 pm, SmeeZee <smee....@googlemail.com> wrote:
> Hi Govert
>
> Thank you for your feedback, it is most helpful (especially the
> stackoverflow example). I am exploring that code and am now looking to
> understand the function XlCall.xlfGetWorkbook (as I want to find the

> name of a new sheet I have just added). I found this posting:http://groups.google.co.uk/group/exceldna/browse_thread/thread/8db8cd...

[Anthony Smee]

Hi Govert

Perfect!!!! I am creating a new sheet and populating it!!!

Not sure if this would useful to others, the XlCall commands are:

To create a new sheet within the workbook - XlCall.Excel(XlCall.xlcWorkbookInsert);

To populate a range of cells within the active sheet :

object[,] values = new object[,] { {1,2}, {3,4}, {5,6}};

int rows = values.GetLength(0);
int cols = values.GetLength(1);

ExcelReference target = new ExcelReference(0,rows-1,0,cols-1);

target.SetValue(values);

And finally, a nice to have, you can autofit the resulting sheet with:

//Select the populated cells
XlCall.Excel(XlCall.xlcSelectEnd, 2);
XlCall.Excel(XlCall.xlcSelectEnd, 4);

object[] colparams = new object[] { 10, target, false, 3};

XlCall.Excel(XlCall.xlcColumnWidth, colparams);

//Finally, select the starting cell
XlCall.Excel(XlCall.xlcColumnWidth, colparams);
XlCall.Excel(XlCall.xlcSelectEnd,1);
XlCall.Excel(XlCall.xlcSelectEnd,3);

I hope others find this useful.

Cheers

--
You received this message because you are subscribed to the Google Groups "Excel-Dna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.