VB6 and xlls

Mike asked a couple of interesting questions:

———————————————————-

Hey Simon,

What did you mean exactly with respect to:

(1) “VB6 and .net can produce something they call dlls, but aren’t the usual native code ones. They are activex ones that don’t export symbols in a way Excel can understand.”

(2) “An xll is just a dll with some registration info excel can use to call it directly from a cell.”

Other than a little bit of COM doing the marshalling, shouldn’t a VB6 automation add-in be pretty darn close functionally to a XLL? I’m not a C++/XLL guy at all though, so I’d love to understand what you mean here better…

———————————————————-

Quick summary – (IMHO) the only way to get performant UDFs in Excel is the use the C API, C/C++ and Delphi are about the only tools that can use that API. VB6 and .net languages can’t use the API so they can write fast worksheet functions. (But see ExceDNA for .net)

Detail – My view is there is not that much of an execution speed difference between VBA, VB6 and C/C++, and probably C#/.net. Note I said execution speed. Now I’m happy to accept that in certain corner cases any one of those will be materially faster than the others. But to save a religious war lets say all devs favour their preferred language.

Where there is a monumental difference is in how you trigger the execution from Excel.

Simply put: if you use COM you pay a significant cost per call.

All the previous work I did trying to quantify that can be found here (took ages to find!):

If you don’t want to use COM the only other realistic choice for cell based worksheet functions is the C API. (note automation add-ins (of all flavours) are using COM underneath, as is VBA, XLM isn’t and can be fast as a consequence)

To use the C API your chosen programming language needs to export each function you want to call in a way Excel can understand. And it needs to tell Excel what functions it contains. To do that your chosen language must export using the C calling convention. VB dlls only export those functions required by the COM runtimes, as do C/C++ COM dlls (see following depends screenshots).

Here is depends on 2 COM dlls one in VB6 the other in C++ (ATL). If you look in the bottom right hand corner box you can see they only export a bunch of register unregister type stuff, no recognisable functions:

Depends on VB6 COM addin

Depends on VB6 COM addin

Depends on C++ ATL COM dll

Depends on C++ ATL COM dll

.net dlls dont seem to export anything and connect up to mscoree to sort everything out for them. That makes sense, although I could be wrong as my .net skillz are a bit rusty these days as I find it so irrelevant for the Excel based stuff I do.

Here are the exports for a win32 dll (note how those 5 callable function are exported and visible (these could be called from VBA with a Declare Function…)):

Depends on win32 dll

Depends on win32 dll

Here are the exports for the same dll converted to an xll (note the 3 additional exports xlAddInManagerInfo, xlAutoClose and xlAutoOpen):

Depends for an xll

Depends for an xll, I temporarily changed the extension to dll

In the xlAutoOpen the xll tells Excel about speedtest 1-5, what arguments they take, what data types they return by using the REGISTER() function. You could write that same xlAutoOpen routine in VBA or XLM to register speedtest1-2 from the win32 dll above. To do that in XLM –

=REGISTER(“path to dll”, “SpeedTest1″,”JJN”, “spdTstDemo”, , 1)

You could then call the SpeedTest1 dll function directly from a worksheet cell by using =spdTstDemo(2,3).

Hence my comment that an xll is just just a dll + some registration stuff (a set of =REGISTER()s to be precise)

Thats the direct answer to number 2.

My answer for 1. is that as neither VB or .net can talk to Excel through this fast interface you can’t write fast functions with them. Its nothing to do with how fast they run, just being unable to utilise this fast interface. You could argue, and I wouldn’t disagree, that the real issue is the poor performance of the COM interface, and stacked on that the even poorer performance of the .net interop layer.

ExcelDNA passes .net functions through the C interface, if a future version of Excel had something like this functionality then xlls and C/C++ could finally be laid to rest.

Now its just possible you could contort VB6 in a even more unsupported way than normal to export its functions and an xlAutoOpen in a C stylee, And .net could probably do it at a push. Personally I prefer to use C, the language that the C API was designed for right?

The final point, isn’t a VB6 automation addin pretty similar to an xll? Answer: maybe but its different in that critical way of not using the fast interface.

Hope that makes sense, and helps, leave a comment either way, with further questions or comments especially if I’ve got anything wrong.

cheers

Simon

Advertisements

20 Responses to “VB6 and xlls”

  1. dougaj4 Says:

    I have just posted on my blog a fairly detailed description of how to create a simple function in C, complile it as a dll, and link to it from Excel (here: http://newtonexcelbach.wordpress.com/2008/09/02/linking-excel-to-c/). The next post will cover the same operations for a rather more useful function that will solve cubic polynomial equations.

    The interesting thing from the context of this discussion is the performance of this function, compared with the same thing in VBA. I set up 1000 cubic equations in Excel, and recalculated 10 times. The timings were:

    Read data one row at a time with VBA, pass to the dll, and send back the results: 29 seconds
    All VBA code one row at a time: 16 seconds
    All VBA code operating on an array of all 1000 rows: 6 seconds
    Pass array of all 1000 lines to VBA, then solve 1 line at a time with C dll, write array back to spreadsheet: 0.4 seconds.

    This suggests that there are huge performance improvements to be had so long as data transfer operations from the spreadsheet to the dll are kept to a minimum. Data transfers between VBA and the dll don’t seem to be a problem at all.

  2. Simon Says:

    Yep, its COM that kils performance, and its x milliseconds per cell call, so the less functions/calls or whatever the better.

    Try calling your dll/xll from XLM you’ll find that way faster than the COM route too.

  3. Mike Rosenblum Says:

    Hey Simon,

    Thank you for that incredibly detailed reply! Holy cow, I’m going to have to read this three times at least in order to be able to understand it…

    But my question really was what you hit on at the end: “Shouldn’t a VB6 automation add-in be pretty darn close functionally to a XLL?”

    What I meant here was exactly that: a C++ XLL versus a VB6 automation add-in, not versus VBA or .NET.

    Clearly .NET has the Interop to deal with, so I have no doubt that it will run slower.

    And VBA, unfortunately, has a HUGE amount of overhead when called, so I’m not surprised at the kind of numbers that Doug is showing his results. You will find this kind of dramatic improvement when moving your code from VBA to a VB6 DLL. And as you point out, it is not the internal execution speed — although running fully compiled is a little bit of the help versus the p-code implementation of VBA — but it isn’t the initial overhead for the worksheet function itself to be called.

    My guess is that simply copying the ones VBA UDF’s into a VB6 automation add-in should get you virtually the same performance as a C++ XLL, with far less complexity.

    I could be wrong about this, however, which is why I was asking you and the other C++ XLL guys… But might gut feeling is that a VB6 automation add-in should give a C++ XLL an extremely close run for its money.

    By the way, as for the registration procedure, a VB6 automation add-in doesn’t really have one. It needs to be registered, but that’s it: you don’t directly tell Excel the names of your user-defined functions at all. Instead, Excel must be using reflection upon your registered class in order to find the UDFs. Otherwise, when you entered the UDF into the worksheet, you would get a #NAME? error, because Excel would not know that the name existed… but Excel does and it all works fine.

    Now COM might at a tiny bit of overhead when calling the VB6 UDFs, but I would think that this is negligible. But maybe I’m wrong on this assumption — have you guys time tested a C++ XLL against a VB6 automation add-in? My guess is that the VB6 automation add-in will be very, very competitive.

    But this is just my guess! I’ve never made a C++ XLL, so I don’t know…

    Ok, just read your previous article you listed where you wrote “All our experience suggests there is NO significant performance gain in moving from VBA to VB6, for example for COM/Automation add-ins, but it is sure to depend on exactly what you are doing.”

    Actually, I have found a MASSIVE difference between a VB6 automation add-in and an equivalent VBA implementation. I don’t know the difference in Time testing, but I will say that it was actually VISIBLE. For the most part, what would not really notice the difference, but I was once making my own version of a random number generator, for use in Monte Carlo simulations, in which case I was putting hundreds of thousands of these formulas in the spreadsheet. In this case, the VBA version of this ran like molasses, but the VB6 automation add-in version ran virtually instantly. The difference was dramatic. But, unless you are putting tens of thousands or more spreadsheet formulas on a spreadsheet, I’m not sure that one would generally notice.

  4. Simon Says:

    Mike
    In all my testing, a VB6 automation add-in has never been materially faster than VBA. Both are poor compared to xlls. VBA is a few cycles faster to initiate, VB6 runs a few cycles faster in intensive calcs.

    VB6 automation addins are using COM underneath with all that IDispatch lookup slowness. They do not come near xlls at all, by a mile (from what I have seen anyway). It would be nice if they did, but I’ve never seen it. Try implementing the donothing functions from here as an automation add-in:
    http://www.spreadsheethell.com/codematic/files/donothingfunctions.zip

    VBA is compiled beyond p-code – there are 3 version of VBA code – the binary representation of the text, a version neutral p-code and a highly optimised compiled version. If you compile your VBA (alt dl) then close the IDE and compare your VBA v VB6 stuff I would be interested to know how material the difference is. Or if you like send me the VBA and VB6 project to play with. (I do tend to test with 10k plus formulas because thats the usage I see)

    I’d be very interested to compare notes on automation add-ins as I gave them up as a waste of time, having failed to get any worthwhile performance improvement that would justify the deployment pain over VBA.

    In fact I’ll go back and do the automation add-in version of the COM timings stuff to see.

  5. Ross Says:

    I’m gonna read this in the morning!!!!!!!!!!!!!!!!!!!!!!!!!!

  6. Mike Rosenblum Says:

    Hi Simon,

    Unfortunately I just don’t have the time at all to be kicking this around right now. But I definitely found a very large differential in the past.

    Generally, my testing for VBA shows that it runs about 2.5x slower than fully-compiled code for basic operations like arithmetic, looping through an array, etc. For calls to the Excel object model, however, there is no difference b/c the Excel object model library is fully compiled. Therefore, for a UDF that has no calls to the Excel object model, a VB6 automation add-in should run a good deal faster.

    But the real difference I found was in the overhead to the call. I guess your “do nothing” UDFs show no noticeable differences by your testing? I guess I’ll have to believe you for now, but I do recall in my testing that the calling overhead was the key.

    I’m not quite sure why the IDispatch late-bound issue is a big problem. Sure, it must do this the first time it is called, but the function pointer locations are static, so I would think that they would be kept in a hash table so that future calls are basically instantaneous, without requiring the IDispatch lookup again. But I can’t know what’s going on behind the scenes.

    Overall, a fully-compiled VB6 automation add-in really should run faster than VBA, but, in addition, I was pretty sure that I had noticed a per-call overhead that was significant for VBA, and not present for the VB6 add-in. I could be wrong about this, but I had also thought that I had seen comments from Charles Williams about this somewhere as well.

    Anyway, if you guys are willing to go to the lengths of a C++ XLL, which looks painful to me compared to a VB6 automation add-in, then I would hope that it would be faster! (Although just the idea of doing a C++ XLL looks pretty cool. Thanks for your posts on this Simon and Doug, maybe some day I’ll try it.)

    You mention VB6 deployment as an issue, which it is b/c of registration. Is the deployment for an XLL different, or easier, than for a VB 6 DLL? I assume that an XLL has to be registered in some way, or no?

  7. Mike Rosenblum Says:

    Ok, here’s a Charles Williams source that backs up what you are saying:

    “User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally perform faster than user-defined functions that are developed using VBA or Automation (XLA or Automation add-ins). For more information, see Developing Add-ins (XLLs) in Excel 2007.”

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    In your blog post, Simon, “Conference Report Day 3”, you wrote:

    “After dinner Charles went through UDF performance
    Charles had an example of performance improvement step by step from someone he had helped from a newsgroup posting. Sadly I can’t remember the numbers, but it was big from 20 second to 0.04 or something. He also took us through some of the underlying issues like the VBA call overhead, and ways to minimise that.”

    http://xlconf.wordpress.com/2007/12/10/conference-report-day-3/

    You were there, was this a VBA-specific overhead, or were they issues that would apply to VB6 automation add-ins as well? (If you remember!)

    Anyway, there is no doubt in my mind that a C++ XLL should be the fastest, but C/C++ is only negligibly faster than VB6 in most circumstances so unless there really is a per-call overhead here, I don’t see why a VB6 UDF shouldn’t come very close… but maybe it doesn’t!

  8. Rob Bruce Says:

    I’m dragging this out of long-term memory, but I’m sure there was some Bruce McKinney or Matt Curland trick to get a VB6 dll to export functions in a traditional fashion.

    […]

    OK, after a bit of Gwgling I come up with this: http://www.windowsdevcenter.com/pub/a/windows/2005/04/26/create_dll.html

    I don’t yet know if this means that xlls are possible.

  9. Simon Says:

    Mike
    Xlls are just like xlas, they need no registry access, they just connect to Excel and you are away.

    I think we are seeing similar things but probably operate at different ends – I use lots (10k+) of simple formulas, It sournds like you do much more complex stuff so you see more of an execution improvement than I do.

    xlls, pain – agreed

    see next post for automation v VBA v xlls.

    Rob
    Yes I’ve seen that wrapping the linker stuff, and read the comments about how it didn’t always work. I’d love to know if its realistic. Although honestly I’d be even more gutted that such a useful product was retired when and how it was.

    I’ve no plans to try it as it sounds too flaky to even investigate, but if someone else has tried it or wants to give it a go then let us know. And if you need a hand I’m sure a few of us would chip in where we could.

  10. Mike Rosenblum Says:

    [Hmm… for some reason this prior post did not come through before.]

    Ok, here’s a Charles Williams source that backs up what you are saying:

    “User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally perform faster than user-defined functions that are developed using VBA or Automation (XLA or Automation add-ins). For more information, see Developing Add-ins (XLLs) in Excel 2007.”

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    In your blog post, Simon, “Conference Report Day 3”, you wrote:

    “After dinner Charles went through UDF performance
    Charles had an example of performance improvement step by step from someone he had helped from a newsgroup posting. Sadly I can’t remember the numbers, but it was big from 20 second to 0.04 or something. He also took us through some of the underlying issues like the VBA call overhead, and ways to minimise that.”

    http://xlconf.wordpress.com/2007/12/10/conference-report-day-3/

    You were there, was this a VBA-specific overhead, or were they issues that would apply to VB6 automation add-ins as well? (If you remember!)

    Anyway, there is no doubt in my mind that a C++ XLL should be the fastest, but C/C++ is only negligibly faster than VB6 in most circumstances so unless there really is a per-call overhead here, I don’t see why a VB6 UDF shouldn’t come very close… but maybe it doesn’t!

  11. Mike Rosenblum Says:

    Hey Simon,

    > “It sournds like you do much more complex stuff so you see more of an execution
    improvement than I do.”

    No, actually, I was testing some trivial UDFs. More likely our testing methodology was different. Or flat out: I may have blown it, I don’t know.

    I wish I had the time to kick it around again, but I don’t right now…

  12. dougaj4 Says:

    Just for the record, there were a few isues with the code I was checking in my previous post, that slowed some some cases, but not others. Revised times with 100 recalcs (previously 10) of 1000 rows are:

    VBA row by row: 26.9 seconds
    VBA with array: 14.4 seconds
    dll row by row: 17.4 seconds
    dll with array: 4.6 seconds

    So conclusions are much the same, but the differences much less than before.

  13. dougaj4 Says:

    I have posted details of the benchmarks given in my previous comment here: http://newtonexcelbach.wordpress.com/2008/09/08/linking-excel-to-c-3-avoiding-bottlenecks/

    For my purposes (using generic C code for mathematical operations with minimum modification) I think the approach of compiling the code as a dll and using VBA to generate the input arrays, and extract the data from the output arrays, is probably the best way to go.

    For other purposes the othe approaches discussed in this thread no doubt have their advantages :)

  14. Simon Says:

    Doug
    Take a look at the REGISTER and CALL xlm functions as you’ll be able to call your dll directly from a cell without VBA which will boost performance.
    here is my piece on call
    https://smurfonspreadsheets.wordpress.com/2007/08/16/excel-call/

  15. dougaj4 Says:

    Simon – thanks for the link (and all the other recent posts on xll/dll stuff). One major problem I have with the route you suggest is that I will often (in fact usually) want to return an array, rather than a single value, and I don’t know if you can make the function return value an array in C, or if you can access arrays written by the C function using the REGISTER and CALL functions. Any ideas?

  16. Recent Links Tagged With "programming" - JabberTags Says:

    […] Saved by especkman on Sun 07-12-2008 Money.co.uk Ranks??? Saved by rainerhi on Sat 06-12-2008 VB6 and xlls Saved by toffer on Mon 17-11-2008 Programming Jobs in Physics Saved by noomys on Mon 10-11-2008 […]

  17. Xavier Says:

    Hi Simon…,
    Just didn’ find any answer to my question in forums so i try to get it on a VB6 subject. I know it’not really the place but the question may be of interest when developping with VB6
    I’v an addin (say myaddin.xla) who call procedures in a dll (say myaddin.dll used to hide and protect code). I’m passing Excel.Application as parameter from myaddin.xla to myaddin.dll…
    Part of the functionnality of the dll is specific to Excel 2007. So at first time i thought i needed to create 2 versions of my dll (myaddin9.dll with functionnality applying to Excel 2000-2003 and compiled with refererence to Excel 2000, myaddin12.dll with functionnality applying to Excel 2007 compiled with refererence to Excel 2007). I tried to call procedures in myaddin12.dll in from myaddin.xla in Excel 2000 and it worked ! I was a little bit astonised because it usualy say that you should make reference to the lowest version of Excel…
    My question is “Does it because i have Excel 2007 on my computer ?”… If not it would means developpers need to develop a unique dll compiled with the latest Excel version (12/14..) and in wich functionnality are proposed depending of “If (Application.Version)”…
    Thanks for any answer… Xavier

  18. Simon Says:

    Xavier did you try calling any of the 2007 specific functions from your xla in 2000?

    Anyway to answer your question, yes this works because you have 2007 on your pc, the vb dll with the 2007 ref will fail on a client pc without 2007.

    Your 2 dll approach makes sense, I would compile the v9 one on a machine that had never had 2007 on it to avoid picking up rogue references. You need to set up a non 2007 environment to check how this will work.

    • Xavier Says:

      Thanks Simon for your answer,
      I try and of course it fails if they are no version handling in the dll procedure (error message say there an error method in the calling procedure of the xla) but if i include a version handling in the dll (if Val(xlApp.version…) Message to say functionnality unavailable +Exit sub) Then it works well (even if objects are declared later in that dll procedure that don’t belong to Excel 2000 object model (eg: xlLstObj as Excel.ListObject)…
      At present i have two project (myaddin9.vbp with project reference to Office 2000, myaddin12.vbp with project reference to Office 2007). They are grouped under the myaddin.vbg group project and it works well – non rogue references apparently). But i’ll have to try a release under non 2007 environnement…
      Thanks. Xavier

  19. Xavier Says:

    Hi Simon
    I’v just tried the dll compiled with the lastest Office version (2007) On two PC without 2007 (one with Office 2000, the other with 2003) and it works (with version handling). Good news for who wants to develop for different Office version; a single DLL is sufficient…Xavier

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: