Why VBA is so rubbish

The last VBA post may have given the impression I’m some kind of VBA fanboi. I think it has its place, but it’s far from perfect. I put my definition in that post so I wont repeat it here.

I think its important to keep in context of what is out there live in the real world now, so I’m not going to suggest another language is better. In pure terms python (or your personal favourite language) maybe a better language for users to do basic automation etc. The reality is there is a lot of VBA code around, and more gets written everyday. I think the language would benefit from a few modern features, perhaps to support OO more fully, but not at the cost of backwards compatibility.

I’m also going to skirt around the issue of user competence. You get crappy code in every language, some languages may encourage it more, or may be more attractive, or more accessible, to developers of limited skill and experience.

It has been suggested that giving something as powerful as Excel/VBA to an unskilled user is the equivalent of giving a loaded rifle to a child. The reality, I would suggest, is a little less dramatic, as far as I know, no one has ever been killed by a poor quality spreadsheet, and very few companies have gone bust because of one. Yes SocGen just lost a stack of cash, but a person did that by trading badly, not a spreadsheet.

VBAs biggest fault?

I’d say its too easy to go beyond its design envelope.

For simple automating Excel and standalone complex worksheet functions (eg: the stuff Mike talks about) I think it is great. Its easy to slip from there to 10/12/14 KLOC systems, at which point I think VBA can work against you.

The other side to that argument is perhaps there is no realistic migration path, or no realistic alternative. That may or may not be true, but either way is not a fault of VBA.

Second – The editor. Frankly its so arcane I think Integrated Development Environment is a bit rich. If this just got updated to the VB6 one (which has more visible object model) add-in devs would fix everything else. Ideally though I’d like a VSTA style editor that writes proper VBA, or something completely compatible, in Excel 14.

That would also fix my third biggest fault – Microsofts lack of public love for VBA. Many devs are worried about its future, and hollow verbal assurances don’t cut it. A significant investment in upgrading the IDE, or replacing it with the VSTA one would settle the uncertainties for many.

Speeding up the call interface would be excellent too. As would allowing VBA UDFs to participate in multi-threaded calculation.

VBA’s reputation as a meddlers monster creation system (/toy language) hardly makes it the sort of thing you want to own up to at a party. The political correctness bureau solution to that is the change the name (eg ‘disabled’ went to ‘people with disabilities’ now moving to ‘differently abled people’) in general the ‘name’ gets longer as they use more circuitous language. So perhaps renaming VBA to something like AAS (Application Automation System) would fix any stigma? (of course it would have to be MAAS or MOCAAS (Microsoft Office Client AAS)). You get the idea.

Any other suggestions? the ruder the better.

What would be your top 2 or 3 weaknesses of VBA?

cheers

Simon

Advertisements

23 Responses to “Why VBA is so rubbish”

  1. jonpeltier Says:

    “the ruder the better.” LOL

    Aside from VBA’s reputation, and the perceived low level of support (e.g., the archaic IDE), I don’t see a lot of shortcomings of VBA. I came at it without a formal programming background (though I’ve been programming for over 30 years now), but as an engineer who needed to get things done.

    Considering the major use of VBA as a macro language for Microsoft Office, it is remarkably powerful. Used in conjunction with VB6 and the Windows API, you can do some powerful stuff with Excel as your main platform. Whether you should is a rant for another post, in fact I think we’ve had that rant on this blog over the past couple of months. Given the low level of support most people who need solutions (business, engineering, whatever) get from their solution providers (IT), the platform of Excel/VBA/VB is as reasonable as anything.

  2. Elizabeth P Says:

    I agree with jonpeltier that VBA as the driving force of Macros for Excel is very powerful. I work at a company that uses some of the most complex and convoluted Macros I’ve ever seen, created by the Directors of the company and now in my care. The system is powerful but it’s very complex and very hard to maintain. The alternative we’re currently looking at is a .NET application but I’m not even convinced this will satisfactorily replace everything Excel has been forced to do for the last 8 years or more. VBA is a very powerful thing and hard to replace. So I say yeah, let’s upgrade the interface and give us something much better to work with please!

  3. Marcus Says:

    The largest project I’m had to maintain was a 40 KLOC monster across an Access database and several spreadsheets. There was a huge amount of code duplication. The model collected data from various business units, crunched numbers and generated reports.

    The migration projects was driven by budget and time constraints (a.k.a compromises) and I ended up migrating all the code to a VB6 DLL which interacted with Informatica and SQL Server.

    VBA makes it hard to manage sound code reuse practices. Then there’s the performance issues (BTW the 40 KLOC Access/Excel model ran just as slow compiled in VB6).

    However, there’s hardly a faster way to whip up small scale solutions and functional prototypes.

    Regards –

  4. Simon Says:

    I’ve never seen a speed up from moving from VBA to VB6, I actually think VBA executes pretty fast, its just the call interface into Excel that seems slow.

  5. Stephane Rodriguez Says:

    Wrt VBA,

    – international data handling not native, almost grotesque. Example : manipulation of dates, sorting strings, ….

    – single threaded. Impossible to push in the future without a rewrite. Impossible to use in server code (as is so blatantly problematic with Excel services)

    – a good measure for a programming language is how many bugs are caught at compile time (or while typing the code) versus run-time. VBA does not score well here.

    – simplicity. deployment. VBA scores well here.

  6. Johan Nordberg Says:

    Simon, I couldn’t agree more.

    My wish list for VBA:

    * A better editor! Atleast let me nudge form controls with arrow keys…
    * More native controls in MS Forms, like a calendar control, progress bar, multi column grid that actually works with (sortable) headers. Sure, you can reference ocx controls, but then the whole point of a single file copy paste deployment is gone unless you KNOW the control is on the users machine…
    * Inheritance!!!!!!!!! There so much code duplication is drives me mad. And when you try to create somthing smart to avoid code duplication it get’s even worse.
    * Interfaces (see above)
    * At least a simple serializer/deserializer

    “However, there’s hardly a faster way to whip up small scale solutions and functional prototypes.”

    I totally agree.

  7. Charles Says:

    I suppose VBA/VB6 is not really a heavy-duty language (you certainly would not want to write large-scale software using it), and we could happily discuss how large is too large over several pints.

    Weakest points for me:
    – VBA insecurity
    – string handling
    – ease of writing code that performs like a one-legged dog
    – inability to fix known bugs
    – VBA gets slower with each Excel version.

    @Simon,

    I just did some speed comparisons for UDFs between VBA and VB6 using Excel 2003 on my laptop, and VB6 is coming out 10-25% faster!
    C# is still very slow, and of course C is king. Must try XLM …
    (suggestion for post subject: Will XLM stage a comeback now that VBA is dead on the Mac?)

  8. Rob Bruce Says:

    “I think the language would benefit from a few modern features, perhaps to support OO more fully, but not at the cost of backwards compatibility.”

    COM got as far as it could go with OO. It became impossible to take it further (e.g. implementation inheritance) without a full rewrite. That was one of the (if not the) main reasons for .NET.

    I would settle for VBA being frozen and VSTA becoming the ‘bundled’ MSOffice programming paradigm. But that would require MS to sort out the issues that have been well discussed here recently – the code would have to reside in the document, the runtime (sorry, framework)would have to stabilise, and, above all, the interop performance penalty would have to be seriously addressed. TBH, I can’t see any of this happening, sadly.

  9. Ross Says:

    >>A better editor! Atleast let me nudge form controls with arrow keys…
    You know Steven Bullen has a addin for this

    1. Better Forms
    2. Better IDE
    3. Compile to com – like old dev eds

    4. C/C# snytax offering?
    5. free pet monkey with each copy.

    Maybe call it

    Automated Resoruce Systems Enviroment?
    Cheap Resoruce Automation Programing

  10. Johan Nordberg Says:

    And better array handling!

  11. Dennis Wallentin Says:

    The security paradigm on Vista is supported with .NET but not with VBA.

    It’s difficult to compare VB6 and VB.NET since the former is no longer available via Microsoft and is no longer supported by MSFT. In other words, VB6 is ‘dead’ while VB.NET is the future (whether we like it or not.)

    Lately I have started to reconsider how we actually view .NET. Instead of single out .NET shortcoming in its communication with COM applications like Excel we could say:

    “Give us a completely rewritten new modern version of Excel!”

    As we all know, the world is not ‘black or white’ so therefore VBA has its benefits as well as .NET has to provide good custom solutions with.

    @Charles,
    Can You elaborate why VBA gets slower with each new Excel version.

    Kind regards,
    Dennis

  12. Simon Says:

    Stephane
    do you know much about VBAMT the multithreaded version? I’ve skimmed some of the doco, but then my tea was ready…
    Charles
    I’d be interested to get more info on that VBA/VB6 speedup (can combine it with my promise to implement the xluc UDF in c.
    Rob ExcelDNA goes throught the C API and is .net 2.0 based and is fast (not that I have tried it, keep meaning to …)
    Ross, thats what I was on about, it was the R that was holding me up.
    Anyone think of a H?

  13. Harlan Grove Says:

    I’d agree on better string handling and better array handling.

    Plus
    – accept 3D references
    – build regular expressions into VBA if not into the Office application OM’s
    – add real logical operators which provide short-circuit boolean evaluation
    – add a Const qualifier for procedure arguments like in C

  14. Simon Says:

    Dennis
    Spot on, Office client apps need a native .net interface, instead of blundering around COM interop or the C API.
    Having that, investing in that, would send a strong message to the market that MS are serious about their client apps, and serious about .net. And would I believe cause a major move away from VBA. I would almost certainly adopt .net and leave VBA for trivial stuff.

  15. Marcus Says:

    “…I actually think VBA executes pretty fast…”

    Enter the “fit for purpose” argument again. This was a Stochastic Monte Carlo simulation model: randomly modify a set of variable 30 to 50 thousand times, and for each iteration run a series of calculations in blocks of 100,000 units. Process ran (on the server) 23 hours. There are dedicated calculation engines built for this stuff. The last place it should have been is Excel/VBA (or a VB6 DLL).

    Regards – Marcus

  16. Simon Says:

    23 hours? you wouldn’t want to get an input parameter wrong would you?

  17. sam Says:

    Once they bring back the ability (from the Office XP Developer edition) to save VBA code as DLL (XLL) ….It solves all problems relating to speed and Security….

  18. Stephane Rodriguez Says:

    Simon said “Stephane
    do you know much about VBAMT the multithreaded version?”

    If you are talking about the MT hosting sample part of VBA SDK, i think it’s a POC, not something serious.

  19. Charles Says:

    @Dennis,

    I don’t know why VBA gets slower with each Excel version , you would have to ask Microsoft .

    You can test read/write performance using the Variant Benchmark on my download page.
    Excel 2007 VBA probably has the most significant slowdown compared to previous versions. IIRC Excel 2000 is the sweet spot for VBA performance.

  20. Simon Says:

    Stephane
    POC pretending to be production code – that would be a first! ;-) ;-)

  21. Julian Says:

    Late entry:-

    I have been looking at the option of using something other than VBA for office automation (.NET Interop) but the solutions require a whole new level of knowledge concerning COM and .NET garbage collection, way beyond the basic user. The price of power / flexibility is discrimination against the casual programmer. See the following:-

    http://www.xtremevbtalk.com/showthread.php?p=1326018#

    In effect, VBA is simple and gets you where you want to be quickly and now.

    .NET on the other hand creates a huge code / knowledge barrier between you and your application, and is worse, apparently, in terms of performance according to the above link.

    Clearly VBA needs replacing, as it is too good and too accessible for the non-professional programmer.

    I thought software was supposed to make our lives easier?

  22. Harlan Grove Says:

    ‘I thought software was supposed to make our lives easier?’

    No.

    Microsoft software is supposed to make Microsoft more money. Whether it does anything useful for anyone else is just an unfortunate necessity due to pesky buyers expecting something for the money they pay. [Said by someone who hasn’t paid for an upgrade of a Windows version of Office since Office 2000, so either a Luddite or someone with an awkward sense of value from Microsoft’s perspective.]

  23. Mike Eustace Says:

    What’s wrong with VBA? Answer – everything. Absolutely everything. Learn the Open Office format, learn DOM, learn to code in a decent interpreted language (PHP, Python, etc) and manipulate the OO files via DOM (all modern office docs are XML zip archives)

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: