I have a C# utility add-in which is based upon
Microsoft.Office.Interop.Excel. The initial intent of the utility was
to provide some numerical functions which could be called from Excel
formulas (that part works fine), but now I need to process a couple of
ranges and output a chart which contains some derived ranges. That
didn't work, but led me to some simpler scenarios in an attempt to
explain it, and some questions.

Here is a simple function which simply returns the value 2.0:

//THIS WORKS AS EXPECTED
public double Test1()
{
return 2.0;
}

And here is another function which fills a 20X20 grid of the cells of
the source worksheet with 17, and is supposed to return 2.0 to the cell
in which it is called from. In fact, what happens is that the 20X20
grid is filled, and it immediately returns and indicates an error in
the value of the originating cell - it does not return 2.0.

//DOESN'T RETURN 2.0, DOES FILL RANGE OF CELLS
public double Test2()
{
Excel.Applicationexcel=(Excel.Application)Marshal. GetActiveObject("Excel.Application");
Excel.Worksheet ws = (Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet;

Excel.Workbook wb = (Excel.Workbook)excel.ActiveWorkbook;


double[,] foo = new double[20, 20];
int i,j;
for (i = 0; i < 20; ++i)
for (j = 0; j < 20; ++j)
foo[i, j] = 17;
Microsoft.Office.Interop.Excel.Range bar =
ws.get_Range("A1:T20",Type.Missing);
bar.Value2 = foo;


//NEVER REACHES HERE, YET FILLS CELLS IN RANGE W/17

return 2.0;
}

And finally, here is nearly the same function, which returns
immediately following (or during) the line where it attempts to assign
the chart variable, again indicating an error in value at the
originating cell.

//DOESN'T RETURN 2.0, MAYBE ADDS EMPTY CHART TO WORKBOOK
public double Test3()
{
Excel.Applicationexcel=(Excel.Application)Marshal. GetActiveObject("Excel.Application");
Excel.Worksheet ws = (Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet;

Excel.Workbook wb = (Excel.Workbook)excel.ActiveWorkbook;


Excel.Chart chart =
(Excel.Chart)wb.Charts.Add(System.Reflection.Missi ng.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value);

//NEVER REACHES HERE

//ADDITIONAL CHART FORMATTING WOULD HAPPEN HERE

return 2.0;
}


So... after banging my head against a wall all day I've developed a
hypothesis that these addin functions are only capable of returning a
single datum to the calling worksheet, whether that is the value which
the function returns, or a filled range, or an empty chart. As soon as
SOMETHING is sent back (returned/assigned) to the calling worksheet,
the function terminates.

I hope I'm doing something obviously wrong here, because if my
hypothesis is correct, then addins built upon the Excel Interop are
only useful as functions returning simple data, and lacking capability
to make any other changes to an Excel spreadsheet. Yet I find examples
all over the place which purport to demonstrate the use of the Excel
Interop to fill charts and process cells ...

So what am I doing wrong here? Helpful suggestions would be
appreciated :)