I did some more tests, summing up 20'000 doubles:

public double SumExample1(Excel.Range range)
{
DateTime before = DateTime.Now;
Excel.WorksheetFunction wsFunc = range.Application.WorksheetFunction;
double d = wsFunc.Sum(range,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
DateTime after = DateTime.Now;
TimeSpan duration = after - before;
return duration.TotalMilliseconds;
}

public double SumExample2(Excel.Range range)
{
DateTime before = DateTime.Now;
Object[,] rng = range.get_Value(System.Reflection.Missing.Value) as
Object[,];
double d = 0;
foreach (Object obj in rng)
{
d += (double)obj;
}
DateTime after = DateTime.Now;
TimeSpan duration = after - before;
return duration.TotalMilliseconds;
}


The first eaample finished in 47 milliseconds, the second in 16
milliseconds. However, one should be careful with interpreting these
results: Worksheetfunctions that compute complicated mathematical results
might possibly end up being computed faster than computing the same results
in C# code (because of various internal optimizations). However, I have not
tried this myself.

Fabz


"Fabz" wrote in message
...
What is very interesting is the comparison of the following two UDFs
(written as Automation AddIn in C#), iterating through a cell range of
10'000 cells:

public double LoopExample1(Excel.Range range)
{
DateTime before = DateTime.Now;
foreach (Excel.Range r in range)
{
double d = (double)r.Value2 + 1;
}
DateTime after = DateTime.Now;
TimeSpan duration = after - before;
return duration.TotalMilliseconds;
}

public double LoopExample2(Excel.Range range)
{
DateTime before = DateTime.Now;
Object[,] rng = range.get_Value(System.Reflection.Missing.Value) as
Object[,];
foreach (Object obj in rng)
{
double d = (double)obj + 1;
}
DateTime after = DateTime.Now;
TimeSpan duration = after - before;
return duration.TotalMilliseconds;
}

public double LoopExample3(Excel.Range range)
{
DateTime before = DateTime.Now;
foreach (Excel.Range r in range.Cells)
{
double d = (double)r.Value2 + 1;
}
DateTime after = DateTime.Now;
TimeSpan duration = after - before;
return duration.TotalMilliseconds;
}

On my machine, the first UDF (LoopExample1) requires around 375
milliseconds to finish, the (LoopExample2) around 16 milliseconds and the
third (LoopExample3) around 359 milliseconds! Thus, it seems to be much
faster to actually use a 2D-Object array instead of going through the
implicit enumerator inside the foreach loop. (This website seems to
confirm these assumptions: http://dotnetperls.com/excel-interop.)

Be aware that range.get_Value does not belong to the official API specs
published for the Range interface by MS.