Question about speed and performance

[Marco Born]

Hello Govert,
hello Forum,


at several webpages I could read that a XLL should be faster than VBA as it uses a Compiler instead of an Interpreter. But I don't see any difference in the speed of my project. Can you confirm that there is no performance advantage, or did I do soemthing wrong how I implemented ExcelDNA in my solution?

Thanks,
M. Born

[Govert van Drimmelen]

Hi Marco,

Performance differences between .NET-based add-ins with Excel-DNA and VBA macros depend a lot on what you're doing. If you're using the COM Automation interfaces to interact with Excel, e.g. to automate creation of workbooks, writing data to sheets and doing formatting, the runtime costs is overwhelmed by the cost of the COM calls and Excel doing the work, so performance should be similar for any technology you choose to implement the macro code, VBA, C++, .NET or Python. VBA compiles to P-Code, which runs quite fast in most cases.

On the other hand, for compute intensive UDF functions, add-ins based on Excel-DNA will have some per-function overhead, but the actual computation code should out-perform VBA by a good margin. So functions that do quite a bit of work, like a Monte-Carlo simulation being run on each call, should outperform the VBA code by a good margin. In addition, Excel-DNA allows you to use some of the advanced Excel features like making thread-safe functions that can be called in parallel from multiple calculation threads. Finally, Excel-DNA gives you access to the full Excel C API, which can in some cases allow you to exchange bulk data more easily, like dumping values into a large range.

What kind of add-in are you making, and how are you testing the performance?

Regards,

Govert

[Marco Born]

Hello Govert,
I converted a VBA macro to .NET usinf ExcelDNA.
I do the following:

Imports Excel = NetOffice.ExcelApi
...
Dim appxl As Excel.Application
appxl = ExcelDnaUtil.Application
With appxl
...
End With

Within the With-Statement I use the normal VBA-Syntax (only in a few cases I need to declare the Excel, Office or VBA variables different). I don't use any of the other things which might be possible with ExcelDNA. I don't understand C++ or C# so I can't benefit from something that is related with those languages. Also, my code now is almost the same as it was before in VBA which makes it easier for me to maintain.
The speed tests I make that I count the timespan between macro-start and -end. As my macro is usually is running several minutes it's easy to use this method for a speed test.

Would there be a better way to combine ExcelDNA, NetOffice and Excel?


Have a nice day,
Marco

[Jiri Pik]

The key advantage of exceldna for performance is that you can use multithreading within your code – no way to do so in VBA. So if you could use something like Parallel.For you would see the results dramatically

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Marco Born]

Thanks, Jiri. In both versions I have activated Multhithreading calculation in the options but I don't use it in my macros. I need to runs the code line by line as later commandos need the results of earlier commandos so Multhithreading will not work in my case.

[Govert van Drimmelen]

Sadly, multithreading does not help when talking to Excel from a macro.

If most of your macro is spent talking to Excel via the COM interfaces, performance with Excel-DNA should be similar to VBA.

 

-Govert

 

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Marco Born
Sent: 22 April 2014 14:19
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: Question about speed and performance

 

Thanks, Jiri. In both versions I have activated Multhithreading calculation in the options but I don't use it in my macros. I need to runs the code line by line as later commandos need the results of earlier commandos so Multhithreading will not work in my case.

--

[Marco Born]

Thanks to Jiri and you for the explanation.

[Barent Brouwers]

Hello Govert,


I am working on an AddIn for Excel coded in C#.
To speed up things and to deal with different Office versions we use http://www.add-in-express.com/

Next thing I want to accomplish is filling sheets in an Excel Workbook with data simultaneously.
Therefore, I use  in C#

Parallel.For(SheetpointersStart, (SheetPointersEnd + 1), i =>

         {WriteValues wv = new WriteValues();

Results of the writing process are showed in a Debugview window.
My conclusion is that the sheets are filled one after another and not simultaneously.

Do you have experience with Parallel Programming in C# for Excel ?
Should it work ?

Can Excel-DNA be helpful ?

Greetings,

Barent

The Netherlands

 



Op dinsdag 22 april 2014 15:15:58 UTC+2 schreef Govert van Drimmelen:

[Govert van Drimmelen]

Hi Barent,

Excel is essentially single-threaded (apart from some limited multi-threaded recalculation support). Any manipulation of the Excel sheet will go through the main thread eventually, so trying to access the Excel object model from parallel threads is counter-productive. The COM apartment threading ensures that calls from other threads are marshalled to the main thread.

If you want to put large amounts of data onto a sheet, the most important thing is to dump as a single array value to a large Range, rather than trying to write cell-by-cell.

Excel-DNA also supports communicating with Excel via the C API, giving you a good approach to set a large array at once - I posted an example on this StackOverflow discussion: http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects

Other ways to get data into Excel quickly: 

* Great a spreadsheet file directly, without going through Excel, by using the ClosedXML library.

* Use the Clipboard

For user-defined functions you can expect Excel-DNA and Add-In Express to have similar performance - their .xll marshaling was based on an early Excel version.

Regards,

Govert