Whither (Wither?) VBA?
28 September 2008 8 Comments
Considering the origins of the various Office components (and a light bulb may be flickering dimly in the minds of those who can remember back that far) we’ve come a long way in the application automation stakes. Can you remember Access Basic ? WordBasic? Excel macros pre-VBA? Did early versions of Outlook have macros?
Office application macro capabilities have come a long way but they’ve been pretty much stuck at the last version of the VB6 runtime. That’s about a decade with no significant change. In that time Microsoft have hammered their way up to version 3.5 of .NET, but with only half-arsed (my opinion) gestures made towards improving/extending/renewing the internal automation aspects of Office.
I say “half-arsed”, which, I dunno, might be a little harsh, but the whole VSTO thing just seems like a thin wrapper on COM Interop, which is itself a wrapper to permit communication between shiny new .NET code and skanky old legacy stuff. Why do I need to use VSTO at all? If I need complex, compiled high-performance extensions then I’m probably better off getting one of my C++ literate colleagues to write a “proper” XLL add-in that won’t have to deal with COM at all. If I don’t need high-performance then any scripting language that can talk to COM will do the job. Heck, I can use Ruby (and do) – David Mullett has a whole blog on the topic of Windows automation with Ruby.
Microsoft want to get away from VBA, I think that’s clear. They’re never, never, never going to get the current host of non-technical VBA users to switch to VSTO. Forget it, it’s not going to happen. Hell, I don’t want to have to use VSTO and I’m one who should benefit from raising the cost of entry to macro programming. Do MS want to get away from COM? Maybe. They wanted to get away from DDE too, but it’s still lurking somewhere not too deep inside Windows.
How to make an old programmer slightly less grumpy
But here we have the Dynamic Language Runtime, which sits on top of the .NET CLR and allows fun things such as IronPython, IronRuby and others. Snappy performance, ability to use .NET libraries, interoperability between languages, sounds like fun. According to Wikipedia, the plan is to move VB to run on it. Now there’s a thought: why shouldn’t Excel be rebuilt in sexy modern managed code, with VBA ported to the DLR and the old COM interfaces reduced to a shim to keep backwards compatibility? Then we’d have macro programming where it should be, in the application, with the billions of lines of legacy code still runnable, and I’d be able to hit Alt-F11 and work in Ruby.
Seems like a win-win scenario to me.
Grumpy Old Programmer 
The total lack of investment in VBA (or a real alternative) highlights a lack of understanding within MS of how one of their major cash cows is used (or is that abused) on the ground. I too have been looking around for VBA alternatives, in the the .NET arena ExcelDNA is worth a look. In the ‘modern scripting’ world I’ve found Python to be the more Windows friendly option, see
http://blog.gobansaor.com/2008/05/05/python-to-replace-vb6/
Tom (a fellow grumpy old programmer).
I lean toward Ruby but otherwise often do the same thing. But what about the people who never made it – and never want to make it – past Alt-F11? Apart from anything else, most users in most large financial institutions (my area of “expertise”) work on PCs that are “locked down” – the kind of thing we can do simply isn’t an option for them. I’ve seen something that indicates MS are committed to supporting VBA until Excel 14, so we have a couple of versions (3-4 years) at least. But I *want* to be rid of VBA as the sole option. I made a very good living out of VB/VBA for a decade, but it’s time to move on…
Tom: MSFT does still invest in VBA, they do update the object model with each new version. What they don’t do is update the IDE.
Mike: Locked down pretty oftem means you CAN give them Excel files with VBA, but CANNOT give them anything that requires any sort of setup routine.
This is one thing that makes an integrated programming environment like VBA so successful.
Everyone: I can’t say much because of a strict NDA, but very interesting times (reg. Office programmability) are ahead of us.
@Jan – yes, that’s why the whole VSTO thing is so inappropriate in the large corporate world and in-worksheet solutions (or ones that reference code in document-oriented add-ins) are so valuable.
And anyway, in-document macro programming has been very good to me and I’d miss it. I honestly don’t see VSTO as something I’d enjoy doing as much, even if the results were as easily deployable.
Pingback: A Third Way: DNA? « Grumpy Old Programmer
As a lawyer who also programs, VBA has enabled me to create a wide range of personal document production tools in a very short time with no involvment from our IT department (they were happy about that). I tried looking at VSTO (even bought a copy of VSPro), but discovered that, as well as lengthening development time, IT are not happy at all that I start editing the system registry to install my home-rolled .NET Add-Ins, so it is a total non-starter. Drop VBA, and microsoft will cut off a whole world of non-IT department programmer-users : is that progress?
@Julian – couldn’t agree more. These days I’m (defiantly) a banker who programs. Well, probably more like a programmer who, er, banks.
I’m entirely out of the Microsoft loop but I’d dearly love to know why the approach outlined in this post couldn’t work. It seems so teeth-clenchingly simple, dammit!
I’ve looked at VSTA (I wonder if that was part of what Jan was alluding to above) and the concept is part-way to being useful. Part. Not the best or most useful part, either.
@mikewoodhouse – “teeth-clenchingly simple” sums it up perfectly.
I understand the banker-programmer identity issue. For some types of work, the work itself has features that are “algorithmic” in nature, or which require you to think like a programmer to implement them – certainly consumer credit law is like that.
The final point in your post would be a dream world. In effect Word, Excel, etc become application development platforms almost in their own right, with the full power of .NET instantly at your fingertips. Certainly with VBA now I could in theory create a huge range of non-Word related applications, which Word conveniently hosts for me in a UserForm. A bit like the web browser identity issue, where the browser (run as .hta) with a few ActiveX controls, can easily stray from its core purpose – I have a whole load more Word macros written in JavaScript automating Word through ActiveX.
I see your point about VSTA. Looking at an old document they say “an object model they can expose in their application, a runtime engine, and an end-user IDE that communicates with the object model”. Sounds strangely familiar…