Yes, as we said, getting the data out of excel into an array in as large a
block a possible is the way to go.

But .Net is still very slow compared to VBA and VB6 and C++.
Although you can speed up the .Net performance by going through the XLL/C
API interface

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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.