A
algorimancer
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
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