Ok so I admit it, Mike got me spooked.
I tried VB6 automation addins a (good) few years ago in a vain attempt to rescue a big project that was going to get canned because we couldn’t meet the performance bar. Automation add-ins failed to help at all, the project got binned and Automation add-ins got binned in my eyes too.
Then I tried them again in C#, loved the theory did not love the slooow ‘performance’.
So I bit the bullet and went the xll route, and have never regretted that investment.
Anyway I thought I had better test my view formed all those years ago still held true.
I used a simple function that takes 2 double and adds them and returns the answer as a double. (20k calculated 10 times, averages of 10 runs)(measured with GetTickCount)
| xll | VB6 | VBA | |
| Average | 580 | 3164 | 3556 |
| Std Dev | 89 | 222 | 476 |
| Std Dev % | 15 | 7 | 13 |
| % of xll | 100 | 546 | 613 |
| % of VBA | 16 | 89 | 100 |
I am both relieved, and disappointed, relieved that things havent changed much so I wasn’t far out. And disappointed that the boost from VB6 isn’t better.
Moving from VBA to VB6 will save just over 10%, moving to an xll will save almost 85% calculations time.
(I compiled the VBA and closed the IDE)
Bear in mind this test makes no use of any part of the object model. Using range object (and C API equivalent) for example would likely improve the relative performance of xlls and VBA. Intensive maths would likely improve xlls and VB6 relative to VBA.
For me the effort of needing to distribute a dll that the user needs to register would rarely be worth a 10% speed up. What about you?
There are other benefits of using VB6 like improved code security, nicer forms, more powerful IDE. Weighed against its supposed imminent demise of course. The official alternative .net is nice, I havent tested that performance for a while, or in a recent version, but last time I did it was way worse than the worst of VB6/VBA.
Does anyone here regularly write UDFs in stuff to distribute?
What tools/technologies do you use?
Do you turn calc to manual?
cheers
Simon
(my answers:
Yes I distribute UDFs
xlls and xlm, very very rarely I might do something in VBA if its just for a handful of cells.
No I much prefer automatic calc, and a ‘process’ button if needed to trigger a VBA routine)
Smurf on Spreadsheets 
Wednesday, 3rd September, 2008 at 12:19 am |
Sorry I spooked you!
I’ll test this myself again at some point — because now you have ME spooked!
> “For me the effort of needing to distribute a dll that the user needs to register would rarely be worth a 10% speed up. What about you?”
Clearly not, I couldn’t agree more.
The only thing I can think of left is that I assume that you tested your results only AFTER the VB6 add-in was forced to load? That is, create one cell with the UDF in force before starting the testing… otherwise the load-time for the automation add-in (which is always demand loaded unless you force it in some way) is included.
My guess, though is that your results are solid… Now I have to wonder what I could have done wrong way back when, when I tested this stuff myself. Hmm…
Sorry to have taken up so much of your time! Great posts though.
Wednesday, 3rd September, 2008 at 12:53 am |
Leaving the IDE open will hammer the VBA figures, Charles has the details here:
http://www.decisionmodels.com/calcsecretsj.htm#performance
(about half way down – comparison of IDE open and closed – 91 seconds open v 2 seconds closed – that would about cover it I reckon!)
This VBAIDE issue of course won’t impact VB6 stuff so that could be the difference we are seeing?
Sorry about spooking you
Wednesday, 3rd September, 2008 at 9:01 am |
Hi Simon,
I’ve mentioned previously that I was involved in a project to migrate a Monte Carlo model from Excel / Access / VBA to SQL Server / Informatica / VB6 (DLL).
The model had circa 40 KLOC. The main body of calculations was run 100,000 times. This process was repeated another 30,000 during which specific variables were randomised for the next 100,000 iteration.
I can’t recall the exact numbers but trials (300 x 10,000 iterations) showed less than a 5% difference between VBA and VB6. The model included both basic calculations as well as calls to a statistical functions library (a Fortran DLL).
Running full throttle (30k x 100k iterations) the model took 23 hours to run. Based on your metrics (XLL running at 16% of VBA) it could have come down to 3 hours and 40 mins. (Although really it should have been migrated to a fit for purpose platform such as Algorithmics.
While I’ve developed enough XLA and COM DLL’s, I don’t really use these for UDFs. Most of the work is displaying forms with options, interacting with databases and transmitting data between Excel and a data store (Oracle, SQL Server, Access). I wonder what kind of performance difference these types of apps would display in different dev environments. Between XLA and COM DLL’s I’ve not seen any performance difference at all. The primary benefit of the COM DLL is code security.
For UDF’s I can appreciate the performance gain XLL’s provide but for the kind of work I tend to do (MIS) I don’t think the learning curve would justify the return. What do you think?
Cheers – Marcus
Wednesday, 3rd September, 2008 at 9:41 am |
Hi,
If any ones gets too spooked i will hold their hand!!!
I have written Xlls, C DLL and com DLL (VB6), and .Net DLL, for udfs, all have there pros and cons. – I havn’t done any automation addin though.
There are a couple of thing i would like to bring up about the last 2 posts.
1. We should also consider the time it takes to write the code, something C can be a time saver for this – randon number bitwise twist for example, as there is loads of code out there in c for math functions. Also .Net can be cool beasuce its very quick and easy to write.
2. XLL dont need a hard coded path, which calling a DLL from VBA does, so XLL are more portable.
All very intresting and good stuff cheers guys
Ross
Wednesday, 3rd September, 2008 at 2:23 pm |
Marcus
Thanks for the info
I agree on the MIS side its probably not worth the pain of learning C/C++, personally I find C# tip top for that. In fact C# is my glue code of choice (After VBA) where clients have the framework deployed. (You can do command line apps that take parameters, thats a challenge in VB6)
I reckon xlls and the C/C++ learning curve are only really worthwhile if you want worksheet functions that need to be called fast. ie potentially lots of them. If you only expect to use the UDF a handful of times, or if it will be slow because it connects to an external datasource etc, then C/C++ isn’t going to help much, I’d probably go for VBA in those cases.
Friday, 5th September, 2008 at 11:32 pm |
I maked (compiled someone elses code) a delhpi XLL to day, i also looked at com addins. The XLL looked simple enough, but the com stuff seem a bit harder, although i think a product called addin express makes it alot easier. I thought i was kinda neat that you could do the 2 things with the one langue though.
Food for thought!
Saturday, 6th September, 2008 at 10:47 pm |
Ross
Thats why I went the C/C++ route, because you can do pretty much anything to anything with it, no limits. Of course most of the stuff you do is either a bit or a lot harder than it would be in VB or .net, but every now and then you get something that just can’t really be done in anything else.
I’m trying hard to not look at delphi as I have enough learning projects on the go, but where did you get the xll course code from?
Monday, 8th September, 2008 at 4:26 pm |
Years of hard work, given away!!!
com stuff:
http://www.delphi3000.com/articles/article_2719.asp?SK=
XLL
http://www.aspfree.com/c/a/Windows-Scripting/Writing-Excel-Addons/7/
Monday, 8th September, 2008 at 5:10 pm |
Cheers Ross, (that would be source code as opposed to course code (whatever the chuff that is)
Monday, 8th September, 2008 at 6:24 pm |
Hi Simon, I’d love to see you do the same speed test with ExcelDna. Give me a shout if you need any help. — Govert
Monday, 8th September, 2008 at 7:21 pm |
Govert
I have been meaning to try ExcelDNA for ages (I’ve heard very good reports). There was a reason I was holding onto .net 1.1 rather than move to 2.0, and that prevented me from trying on the same machine as these tests. As I can’t remember why I was stuck on 1.1 and all my recent .net clients seem to have 2.0+ I should probably move.