Excel macro performance

The previous post was meant to discuss what sort of language we think might work best for programming Excel.

It got a little diverted onto performance, I’m happy with that, I’m extremely conscious of performance having had projects cancelled for being too slow in the past.

So here is a question about a VBA upgrade or replacement:

How important is performance?

I would split that into 2 areas, you may have others?

  1. Excel object model manipulation performance
  2. General code execution performance

I would say VBA is good enough at 2, but a bit slow at 1. And that seems to be the pattern, .net is even worse as it has to go through another layer of interop.

The only fast way for 1. is through the C API (AFAIK?) and that is incomplete and not being extended to include new features.

Ignoring for the time being whether you think the Excel COM interface is broken beyond repair, would poor performance (say compared to VBA) be an adoption blocker for you? Would an alternative need to be faster than VBA?

The reason I ask is that I don’t see that many signs that MS are too worried about performance. No one ever accused COM of being too fast, and .net doesn’t seem to be any faster. I’ll save my thoughts around that for another post.

For now: would you be willing to have a slower performing VBA replacement if it was easier to learn/work with?

Another point VBA is never going to participate in the new multithreading (even though it could), xlls can, should any new macro language?

I’m not sure one language/development environment can meet the wide variety of uses. So should high end devs move to .net/VSTO and users move to something simpler than VBA, and VBA just quietly disappear?

The first and last point I am pretty confident is exactly what MS want to happen, the middle point though I don’t know? That seems to be the target of the 2007 Access macros, but there is no equivalent for Excel.

What do you think?



11 Responses to “Excel macro performance”

  1. Jon Peltier Says:

    Would an alternative need to be faster than VBA?

    An alternative would have to be not noticeably slower than VBA.

    So should high end devs move to .net/VSTO and users move to something simpler than VBA, and VBA just quietly disappear?

    High end devs already have VSTO if they want, or VBA/COM/XLL. Low end, well, I can’t see much being easier than recording a macro or looking for something via Google. In this way, VBA satisfies a very broad range of requirements. I can’t see MS investing the kind of resources needed to develop an easier to use alternative to VBA: it would have to have a very graphic-intensive interface, and it would have to have at least as many hooks into Excel as VBA has (or at least as VBA should have, see my post in the other blog topic).

    One possibility suggested by Access macros, though, might be a subset of VBA that does not risk security problems, so you could write simple UDFs, event procedures, and other macros that would not set off the macro warning.

  2. gobansaor Says:

    Focusing too much on performance can be a big mistake (see the Lotus story in http://www.joelonsoftware.com/items/2007/09/18.html ). I once acted as an Oracle DBA and spent many a late night optimising developer SQL statements, eventually it dawned on me that a more cost effective solution to ‘bad’ developer SQL was to throw more memory, more CPUs or faster disks at the problem.

    The same applies to the PCs that will be standard in the corporate world that Office 2007 will inherit in 3 to 5 years time; 64bit, dual processor machines with ‘lashings’ of memory will solve a lot of .NET interop/marshalling bottlenecks.

    As for Access 2007 Macros. I think MS realised that Access had become a developer focused platform and are trying to refocus it as an end-user tool. End users tend to find the wide open plains of traditional VBA code intimidating but are happy enough to work with within the ‘constrained’ commands of Access Macros (and Excel cell formulae). The professional developer may sing “Don’t fence me in”, but the average user is more of the homesteader type.


  3. Simon Says:

    Jon – good point on google+vba (maybe all thats needed is an update to code librarian?)

    Tom – performance, yes server-wise more hardware is cheaper/easier than s/w. When its many many client pcs that argument loses strength though.
    Lotus/MS with their 3-5 year planning horizon are in a different situation than add-on vendors like us. I feel like I have to focus on the now, next week, next month time frame, and not years into the future.
    I totally agree on the blank VBA point.

  4. gobansaor Says:

    My example did apply to the reduction in prices on server technology in the mid 90s, but the same thing is happening on the client side today. As the WAN becomes the new LAN, i.e. the most productive and influential end-users work outside the LAN firewall, the idea of running five year old Windows 2000 PCs as the standard for an organisation’s knowledge workers will become untenable. The future for added-value (rather than add-on) vendors like ourselves is with those action-oriented knowledge workers, many of them free-agents, many mobile, but all looking to their laptop as their personal information store.

    An extra 1000 Euro over 3 years is the difference between a powerful PC and an OK one. So providing power-users with decent client-side hardware is relatively painless.


  5. Harlan Grove Says:

    Joel Spolsky doesn’t know what he’s talking about re the original Lotus Symphony. It came out in 1984. I know because I bought it with my first PC. It came out a years and a half after Lotus 123, and it was the FIRST of several ‘integrated’ packages that caused some excitement in the MID-1980s. Among the others were Framework, Enable and Ability.

    Symphony’s spreadsheet was actually better than 123. It provided all of 123R1’s built-in functions along with many additional functions (all of which were incorporated into 123R2). Symphony also provided multiple movable and resizable (though not overlapping) windows into documents. Character mode versions of 123 never came close to Symphony’s UI capabilities.

    Symphony’s database and charting capabilities were fair in the context of the time, but it’s word processor was abominable, and its communications application (read Hyperterminal equivalent) was worse. That said, version 1.1 was the first DOS application to make use of EMS (i.e., EXPANDED rather than Extended) memory cards, even beating out 123.

    As for 123, Joel spews more misinformation. 123R2 ran in just 256KB. That didn’t leave much memory for the spreadsheet, but it still provided for spreadsheets comparable to what could be crammed into VisiCalc, which loaded into less than 32KB but still only left 32KB left for spreadsheets on the 64KB machines on which it was originally targetted. Every high-end application through 1985 was hand-crafted in assembler because there was a very hard limit of 640KB for executable code on 8086/8 CPU PCs.

    123R3 used extended memory – it WAS directed at AT and other 286 or better PCs. What Lotus gave up on was 3D functionality for 8086/8 PCs, so they made the mistake of marketing both 123R2.2 (2D) and 123R3 (3D) at the same time (both released in Summer 1989, more than 5 years after they released the original Symphony).

    What killed Lotus was that they focused too closely on the character mode UI and their look & feel lawsuits. They underestimated the market for Windows, not the market for 286 and 386-based PCs. Their first Windows offering, 123 for Windows 1.0 was atrocious, and they took too long coming out with 123 Release 4 for Windows, which came out about the same time as Excel 5. But bundling into productivity suites killed Lotus. 123 and Freelance held their own against Excel and PowerPoint, but Word kicked the living sh*t out of AmiPro, and Access was a real database application development system while Approach was a toy.

    I know he’s trying to make a bigger point, but he thoroughly screwed up the Lotus details. As for his bigger point, it’s a fine explanation of why so many believe mid-1980s software running on mid-1980s hardware performs about as well as mid-2000 software running on mid-2000 hardware. It’s certainly true for all together too many spreadsheets.

    Some developer concern for economy in use of system resources would be a refreshing change from current standard development practices.

  6. Stephane Rodriguez Says:

    “Focusing too much on performance can be a big mistake (see the Lotus story in http://www.joelonsoftware.com/items/2007/09/18.html ).”

    Disagree. I run a business on improving performance.

  7. Dennis Wallentin Says:

    I recently upgraded to Notes 8 and in one version Symphoni is included. Symphoni only contains word processor, presentation and spreadsheeting. It’s also available as a free download and currently it’s only available as a beta (which makes me wonder how it could be shipped with Notes?). The new Symphoni is interesting but the slow performance actually makes it nearly impossible to work with.

    Harlan – I really like Your historical reviews especially when it comes to Lotus 1-2-3 and Notes.

    Kind regards,

  8. Nick Hebb Says:

    > How important is performance?
    > I would split that into 2 areas, you may have others?
    > 1. Excel object model manipulation performance
    > 2. General code execution performance

    I think Jon nailed it. Maybe I’m cynical, but I think there needs to be a 3rd category added:

    3. Marketing performance

    In the past, Microsoft has two things going for VBA:
    (1) Customer lock-in.
    (2) They could leverage off of the huge base of VB developers.

    With OOo adding VBA support and the bulk of Windows programmers migrating to .NET, neither of these are as true anymore. I don’t know if there’s a big financial incentive for Microsoft to improve the VBA IDE.

  9. Ross Says:

    Intresting back ground Harlan, Thanks for making the post.

  10. Stephane Rodriguez Says:

    “With OOo adding VBA support”

    Not anytime soon.

    Someone at Novell worked on it for a couple of months, but VBA has plenty of IP not covered by the covenant not to sue. And a full implementation requires Windows (since VBA wraps OLE, COM, …)

    As for Novell’s OpenOffice fork, it does not take an einstein to figure out why Novell (or should i call them Scovell ?) are doing that. Microsoft wants them to push an exclusive version of OpenOffice, to break the Linux interoperability apart.

  11. Simon Says:

    Oxygen Office already has what I would call VBA support, it may not be complete enough to meet your criteria, but its near enough for me. I do accept that its far from complete.
    So are you expecting to see ‘SCOvell Office’ with Windows only features? (Or Windows/SUSE only?)

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 )

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: