Returning Arrays, emulating standard function behavior
 

I'm developing an add-in for Excel (in C#) to provide vector (numeric,
not stl)
and quaternion functions within spreadsheets. Typical standard Excel
function behavior is such that, you select a cell where you'd like the
results of a function, press the "=" button, select the desired
function, select the cell range(s) needed by the function, click OK,
and the single result appears in the initially selected cell. I can
emulate this behavior with a function like:

//Returns magnitude of vector selected as Range
public double GetMagnitude(object Range)
{
Excel.Range rangeThis = Range as Excel.Range;

double dS = 0.0;
Object[,] arrayThis;
arrayThis = (Object[,])rangeThis.Value2;
//this is overkill, but handles vectors arranged as rows or
columns, or even matrices
for (int i = arrayThis.GetLowerBound(0); i <=
arrayThis.GetUpperBound(0); i++)
{
for (int j = arrayThis.GetLowerBound(1); j <=
arrayThis.GetUpperBound(1); j++)
{
dS += ((double)arrayThis[i, j]) *
((double)arrayThis[i, j]);
}
}
return Math.Sqrt(dS);
}

This works great. However, sometimes (mostly) the result of a vector
or quaternion operation is not a single scalar value, but an array of
values. I have tried returning an array of doubles, but all that shows
up in Excel is the first element of the array. For example, here's a
(pointless) function to return a given range, doubled:

public double[] DoubleCells(object Range)
{
Excel.Range rangeThis = Range as Excel.Range;

Object[,] arrayThis;
arrayThis = (Object[,])rangeThis.Value2;
double[] dResult = new double[rangeThis.Count];
int iIndex=0;
for (int i = arrayThis.GetLowerBound(0); i <=
arrayThis.GetUpperBound(0); i++)
{
for (int j = arrayThis.GetLowerBound(1); j <=
arrayThis.GetUpperBound(1); j++)
{
dResult[iIndex] = ((double)arrayThis[i, j]) * 2.0;
++iIndex;
}
}
return dResult;
}

Problem is, it just fills a single cell with the initial array value
upon return. Even if I select a range of cells to be filled with the
result, all that appears is the first element of the arrray.

I know that one possible solution would be to define a specific range
in the worksheet and write the resulting array into that range, but
this is very inflexible. I gather that it is feasible to do things
like offsets to a range, but I don't want the result in a fixed offset
to the input range, I want it to appear as an offset to the cell where
I pushed the "=" button.

Any helpful suggestions would be appreciated; I'm predominantly a
C++/OpenGL/numerics programmer, and am new to this Excel/C# stuff.
Thanks :)

Returning Arrays, emulating standard function behavior
 

When testing, are you returning the results to a vertical or horizontal range?
I seem to recall it makes a difference...

--
Tim Williams
Palo Alto, CA


"algorimancer" wrote in message ups.com...
I'm developing an add-in for Excel (in C#) to provide vector (numeric,
not stl)
and quaternion functions within spreadsheets. Typical standard Excel
function behavior is such that, you select a cell where you'd like the
results of a function, press the "=" button, select the desired
function, select the cell range(s) needed by the function, click OK,
and the single result appears in the initially selected cell. I can
emulate this behavior with a function like:

//Returns magnitude of vector selected as Range
public double GetMagnitude(object Range)
{
Excel.Range rangeThis = Range as Excel.Range;

double dS = 0.0;
Object[,] arrayThis;
arrayThis = (Object[,])rangeThis.Value2;
//this is overkill, but handles vectors arranged as rows or
columns, or even matrices
for (int i = arrayThis.GetLowerBound(0); i <=
arrayThis.GetUpperBound(0); i++)
{
for (int j = arrayThis.GetLowerBound(1); j <=
arrayThis.GetUpperBound(1); j++)
{
dS += ((double)arrayThis[i, j]) *
((double)arrayThis[i, j]);
}
}
return Math.Sqrt(dS);
}

This works great. However, sometimes (mostly) the result of a vector
or quaternion operation is not a single scalar value, but an array of
values. I have tried returning an array of doubles, but all that shows
up in Excel is the first element of the array. For example, here's a
(pointless) function to return a given range, doubled:

public double[] DoubleCells(object Range)
{
Excel.Range rangeThis = Range as Excel.Range;

Object[,] arrayThis;
arrayThis = (Object[,])rangeThis.Value2;
double[] dResult = new double[rangeThis.Count];
int iIndex=0;
for (int i = arrayThis.GetLowerBound(0); i <=
arrayThis.GetUpperBound(0); i++)
{
for (int j = arrayThis.GetLowerBound(1); j <=
arrayThis.GetUpperBound(1); j++)
{
dResult[iIndex] = ((double)arrayThis[i, j]) * 2.0;
++iIndex;
}
}
return dResult;
}

Problem is, it just fills a single cell with the initial array value
upon return. Even if I select a range of cells to be filled with the
result, all that appears is the first element of the arrray.

I know that one possible solution would be to define a specific range
in the worksheet and write the resulting array into that range, but
this is very inflexible. I gather that it is feasible to do things
like offsets to a range, but I don't want the result in a fixed offset
to the input range, I want it to appear as an offset to the cell where
I pushed the "=" button.

Any helpful suggestions would be appreciated; I'm predominantly a
C++/OpenGL/numerics programmer, and am new to this Excel/C# stuff.
Thanks :)

Returning Arrays, emulating standard function behavior
 

I've tried it both ways. Neither works.

Returning Arrays, emulating standard function behavior
 

Even if I select a range of cells to be filled with the result, all that appears is the first element of the arrray. <<

Just to clarify -- you are array-entering the formula, right? That is,
using Ctrl-Shift-Enter instead of just Enter.

Returning Arrays, emulating standard function behavior
 

Yes.

Returning Arrays, emulating standard function behavior
 

Yes what?

Please *quote* what you're replying to. Not everyone is using web "forums" to view this newsgroup, and it gets tedious having to
switch back and forth between views to see what non-quoting post are referring to.

--
Tim Williams
Palo Alto, CA


"algorimancer" wrote in message ups.com...
Yes.

Returning Arrays, emulating standard function behavior
 

Uh, sir? ;) Sorry about that, and anyway I was incorrect.

You had asked,

Quote...
Even if I select a range of cells to be filled with the result, all that appears is the first element of the arrray. <<

Just to clarify -- you are array-entering the formula, right? That is,
using Ctrl-Shift-Enter instead of just Enter.
....Quote

And the correct answer is, no, I'd never heard of using
Ctrl-Shift-Enter in Excel. After trying it (it was also suggested by
Stephen Bullen in the ms.p.vsnet.vstools.office group), that seems to
have solved that part of the problem. Thank you :)