COM components, VBA and performance – part 3

I am going to shut up about this soon, I promise.

This whole performance thing was driven by being told many times to use early binding rather than late, as EB is faster.

So I did some fairly significant testing, and yes early binding is faster. But language choice can have just as big an impact, yet I don’t hear people saying use ATL it’s faster than VB (which it is).

How much you care about performance depends what you are doing of course. And if performance is critical then a spreadsheet may not be the best choice to start with.

Having a reasonable knowledge of the facts means that should I hit performance bottlenecks in the future, I have some ideas of where to start. Moving one or two key classes or functions to a different language could solve 90% of performance problems.

Professional Excel Development by Bullen, Bovey and Green includes an extremely useful performance monitoring tool. Using something like this is critical to ensure you are optimising the right thing. There is no point writing a function in assembler to get ultimate performance if its only called a few times. Generally targeting those areas that get called most often will get the best performance boost for your effort.

Would I still use late binding? Yes I would, in some cases I would take the performance hit to gain the easier deployment.

Will I move all my VB stuff to  (C++)? No, but I may consider moving some core elements.

Would I prioritise code performance over code clarity? In very limited cases, in well defined snippets yes I would.

What about you?



6 Responses to “COM components, VBA and performance – part 3”

  1. Marcus Says:

    “code performance over code clarity?”
    Sheesh! That’s a tough call Simon. My sentiment No. Source code was made for programmers not compilers. I much prefer that source is blatant, obvious and if necessary, verbose. Because when I come back to it 6 months hence (or whenever); I’m going to have a harder time trying to figure out the method behind my madness than the compiler is. And in 6 months time, CPU speeds should have absorbed any performance drains, a code migration may have offered.

    “performance depends what you are doing”
    Key point. The nature of the task will dictate the caliber of the tool. Most of my work is in MIS (reporting) solutions. Want a quick tweak? Migrate it to SQL. I’ve even used DOS batch files to accelerate certain processes (don’t laugh – they work).
    OTOH, for some of quants I’ve dealt with, speed is everything; especially dealing with simulations which may be run 1M+ times. For this C++ libraries proved the fastest solution. However, the bottleneck then became Excel/VBA, and as you stated Simon, then you’ve got to question whether you should be using Excel at all.

    Great series, keep it up.


  2. Ross Says:

    Yeah, good set of posts Simon.

    I think your approach is sensible. Building a few “core” functions that you use a lot in C, makes sense to me – chopping up the code for performance benefits, why not – black box it and keep a dev version with lots of comments? I think people rely to much on what is “best practice”, and end up doing stuff because it’s seen to be the correct way to programme. Now sure a lot of those things make sense, but hell it’s a continuum not an absolute – which comes back to should it be in a SS at all debate!

    One much heralded chorus in Excel debates is that one should always look to use built in functions as they are quicker – it’s not the case. Really you must consider lots of things, dev cost, performance, flexibility, nature of deployment, audit-ability and so on.

    Like you say, now that you are in possession of the facts, you can make an informed choice.

    “I much prefer that source is blatant, obvious and if necessary, verbose. Because when I come back to it 6 months hence (or whenever);”

    I see what your saying – but for a function? I don’t need to understand how it works – just need to know that it does, so in this case, I’m cool no?

    Good stuff Simon!

  3. MikeC Says:

    Would I prioritise code performance over code clarity? In very limited cases, in well defined snippets yes I would.

    What about you?

    Well, it depends on usage of the code in question.

    1. I have lumps of code that I use as a “template” which I tweak and cut, slice and dice, to accomlish a wide range of tasks. Because these are purely for my own use, I prefer performance over clarity by about 1000% – but then I’m using elements of these every day, and they’re dealing with hundreds of thousands of records being manipulated on each occasion. If “clarifying” the code results in a performance hit of 5%, then in the end that will result in a large amount of idle time for me. And there are only so many times I can slope off for a smoke on the basis that I’m waiting for a process to complete…

    2. If, on the other hand, I’m constructing something that will be used by others, and I’m not going to look at it all that often to remind myself of method, and even, Gods Forbid, someone else might try and make sense of the code, then I have to prefer clarity. Besides, in these instances it’s generally tying up someone else’s machine rather than mine…. ;-) but seriously, they’re usually much faster processes dealing with less “data wading”, and therefore a 5% performance hit is less hurtful in these instances.

    I guess what I’m saying is “right tools for the job” – and seeing as I’m creating the tools, I don’t have any excuse to use the wrong ones!

    Very interesting series – makes me think about why I do things the way I do, and thankfully (thus far anyway!) I haven’t yet had reason to change, and anything that provokes thought whilst at work has to be useful ;-)

  4. Simon Says:

    One PM once suggested we just bought all the users new PCs, I thought he was mad. There were 20 or so users, and actually he had a good point, wasting a few weeks dev time shaving 5 or 10% off the time v 10-15k for probably a 50% improvement. It would have been a good return. And like you mention no worries about code clarity.

  5. Marcus Says:

    Hi Ross,

    This was more a statement about maintenance than anything else. I find understanding how it works important when I’m the one who has to support or maintain the code. I find it actually becomes more important when working in a team – no one likes working with someone else’s esoteric code.

    Regards – Marcus

  6. Dennis Wallentin Says:


    This is yet another area where I simplify things:

    Depending on the premises customized solutions face I use either early or late binding.

    But, during the development phase I always use early binding.

    Kind regards,

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: