Fabz,
Unfortunately the performance of c# addins is extremely poor with Excel.
Much but not all of this is due to the COM Interop Layer.
I am not a .NET expert but here are the results of my investigations so far
As with VBA/VB6 you need to transfer data into and out of Excel in as large
a block as possible,
this line is the fastest .NET data transfer method I have found so far:
vArr = DirectCast(vntTheList.Value2, Object(,))
Last Nonzero function timing comparison
VBA 2.2
VB6 1.6
c# 13.6
Vb.net 8.6
here is the Last non-zero
VB .NET function
Public Function LNZVBNet(ByVal vntTheList As XL.Range) As Double
Dim lCounter As Long
Dim vArr As Object(,)
LNZVBNet = 0
vArr = DirectCast(vntTheList.Value2, Object(,))
For lCounter = UBound(vArr, 1) To LBound(vArr, 1) Step -1
If vArr(lCounter, 1) < 0.0 Then
LNZVBNet = vArr(lCounter, 1)
Exit For
Else
End If
Next lCounter
End Function
You will get much better performance (but still a lot slower than VBA/VB6)
if you use one of the .Net Excel interface tools that use the C API/XLL
interface
(Addin Express, ExcelDNA ...)
AverageTol function timing
VBA 109
VB6 63
C XLL 37
Addin Express Automation
VB.net 170
Addin Express XLL
VB.net 100
ExcelDNA XLL CVB.Net 81
In VBA if you want to use Excel native worksheet functions it is much faster
to keep the range as a range object rather than transfer it into an array.
I would imagine that the same is true with .Net (but more so with .Net
because the transfer is slower)
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Fabz" wrote in message
...
Hi there,
I am writing COMAddIns and Automation AddIns for Excel 2007 (with C#).
However, except a few sites, I cannot find any very helpful resources on
performance.
1. Does anyone know where I can find a precise description what accessing
the Excel Object Model through a COM interface really does? Am I accessing
"proxy objects" thus in fact marshalling calls from my C# code to Excel?
2. When calling myRange.Value2 do I in fact marshal a call through the COM
interface or not?
3. Question 2 leads to another question: Is is correct that using
Worksheetfunction.sum(myRange, ...) (1 marshalled call for all cells in
the range together) is much faster than using a loop and summing the cell
values in my own C# code (1 marshalled call for every single cell in the
range)?
4. When providing an Excel Range to a Worksheetfunction in my C# code,
does Excel actually remarshal the Excel Range and send it back to the
Excel application (e.g. copying all the values in the range) or does Excel
only send the cell reference (e.g. "A1:B10") to Excel and let Excel then
access the Range? Is there any way of doing something in my C# code like
Worksheetfunction.sum("A1:B10", System.Reflection.Missing.Value, ...)?
5. Does shimming my COMAddIn/Automation AddIn actually increase or
decrease performance (or doesn't it matter)?
Thanks for your answers.
Fabz