The End Of VBA

The title was just to get your attention, it really should be entitled something along the lines of “What Made VBA The Success It Is Today?’, as this post is more about what VBA is and isn’t, albeit somewhat precipitated by the thought that VBA must be close to its end game.

I recently saw a thread in a forum where one of the responses was along the lines that “VBA was not designed for programmers – i.e. MS dumbed down requirements …”. I found this to be a very facile argument and said so in my response. I won’t bother retracing that thread here, but I thought it raises an interesting question.

When MicroSoft introduced VBA into Office, just what was their objective, how did they envisage its use, and what concessions did they make in their implementation? BTW, although it was introduced into Office, I will concentrate solely on Excel, for obvious reasons.

Although Excel already had a macro language, these were not the easiest to use, and so, for whatever reason that it was done, introducing VBA into Excel 97 was a masterstroke, it opened all sorts of possibilities to Excel users. Like all projects, I am sure that MS’ implementation of VBA had some key must-do objectives, some secondary should do objectives, and some nice to haves; some of those objectives would be customer focussed (such as giving users a simpler development tool), some would be MS focussed (for example, as it was a superset of VB, they would probably want to keep it as closely aligned to VB as is possible, controlling development and maintenance costs). And like all projects, the timeframes and the budgets would be balanced, large enough to suggest that a great product was achievable within the projects  goals, but not so large that everything was easy and achievable – that is just the way of the world.

I have no inside knowledge, but I would think that when MS decided to give the world VBA, they were not looking at IT shops, they were specifically looking to give real users extra capability. They probably anticipated macros that formatted worksheets, shunted data around a sheet/book, all of the standard, simple one-off macro solutions. I very much doubt that they anticipated quite how its usage would take-off, how Excel especially has morphed into a development platform capable of running highly complex applications.

The sheer creativity and inventiveness displayed in VBA is staggering. In Excel alone, there have been some amazing development, from the simple to the hugely complex. We have seen  a whole gamut of solutions, for example

  • simple, but highly effective, tools that make using Excel functions simpler, such as Rob Bovey’s Chart Labeller
  • tools that extend Excel’s native functionality and help spreadsheet users to use Excel more efficiently, such as Jan Karel Pieterse’s NameManager
  • true developer tools for spreadsheet users that help better spreadsheet development, such as Charles Williams FastExcel
  • some incredible tools that do things previously thought not possible, many such examples but a classic is Stephen Bullen’s PastePicture
  • giving us back functions that we used to have, such as Andy Pope’s Pattern Filler utility in Excel 2007
  • database applications where Excel is providing a presentation and data management layer, and a database, Access or SQL Server, maintains the data and provides the richness of a relational database
  • ETL applications where Excel harvests data from disparate sources to provide a composite picture, using tables, charts, dashboards etc.
  • end-to-end data capture, data management, and reporting applications using multiple technologies and multiple layers.

What is even more impressive is that these solution have often been produced by one man outfits, no big IT departments to support the developer here, and often by developers that are mainly business users, people who would never claim to be coders (one of the best Sparklines implementations I have seen was by a guy who made no claim to being a coder, indeed he said that he built this implementation to help develop his VBA skills).

Did MS envisage all of this when they were looking to add VBA? Maybe, but somehow I doubt it. As I said previously, users building macros to automate a few tasks, formatting, capturing data, and so on, is a far more realistically envisaged deployment, presumably they felt that just bringing VB into the Office apps would be a lot simpler for users to get to grips with. If they had envisaged all of this use, I would have hoped that they would have been far more ambitious in their objectives, but I don’t believe this so it is a sterile argument for me.

As I mentioned above, VBA would have been a project, and as such, it would have been subject to typical project constraints. This must have meant that, throughout the course of the project, implementation decisions would have had to be made. Some of these decisions probably resulted in cutting or constraining some of the functionality that was originally envisaged. I am sure this would have been done for all of the right reasons, and that the VBA developers and project leaders would have justified it in their minds, always taking into consideration what they knew or thought at that time. Sitting here in 2009, we might argue that some of those constraints were ill-advised, but that is hindsight, and it is certainly worlds away from saying that MS dumbed down VBA.

Sure there are some things that are better in VB than they are in VB6.

  • forms are better in VB, you have a built-in menu facility, and control arrays, but both of these can be emulated in VBA, with varying degrees of success and completeness
  • you can create stand-alone executables in VB6, but not in VBA – this is true but when VBA was introduced I am sure this was not even a consideration, without being able to foresee the types of addins deployed today and the security issues that have manifested, it seems impossible to think back then that this was necessary or even desirable
  • packaging and deploying – again, VBA is by default packaged within its host container application, targeted at individual users, how could MS have envisaged this need, and anyway, there are many good third party tools that provide this capability

Some things are simpler in VBA, working with Excel for example in Excel, by virtue of its implementation, just as some things are harder by virtue of its implementation.

Acknowledging some things are easier in VB is a completely different matter from saying that MS dumbed down VBA. I no more believe that MS deliberately dumbed down VBA than I believe that many of the techniques that have been developed over the years to make Excel such a versatile development platform were deliberately introduced by MS, it is just consequences of the original implementation.

To me, this is a relevant topic today as we stand on the brink of moving, either willingly or not so willingly, to the .Net world. It originally struck me that MS were not prepared to make any concessions to the world of VBA and the millions of users that have built solutions using VBA. My impression was that they were trying to dig their heels in and say that it is .Net in the future or nothing. I believe that attitude has changed somewhat, not enough yet, but at least they concede that these users have to be accommodated or else they just will not, or cannot, upgrade. The interesting thing will be to look back in 5 years time and reflect on what we think that MS have and have not done well in introducing .Net into Office. I for one feel that we are on the brink of a much better experience with .Net, and am cautiously looking forward to it.

In the meantime, let us all celebrate VBA, the way it has been stretched, bent and moulded over the years by the thousands of real users who just wanted to be able to do something, and do it today; by talented Excel developers like those mentioned above, and by all of the ‘novice users’ who have dared to try and do something in Excel and VBA that helped them to work faster, less, and/or better. Of course, there have been some horror stories along the way, I have seen a few myself, but there have been many successes. Excel has not become the de-facto application development platform that it is today (what other desktop application could have a book like PED written about it?) because MS dumbed down VBA, but because Excel has an incredible function set, and VBA has exposed that function set to all manner of users, and it is they that have shown the world just what is possible.

5 thoughts on “The End Of VBA

  1. Bob,

    Joel Spolsky mentions his role in the creation of VBA in several essays at Bill Gates himself makes an appearance in one of them.

  2. Thanks. That is one of those sites I feel I should read regulalrly, but somehow never get around to. I will have to look it up.

  3. First off, you’re wrong about when VBA was introduced. It was Excel 5, actually pre-Office, though VBA and the VBE weren’t as tightly knit with Excel as they became in Excel 97.

    Try the 3 main alternatives: LotusScript in Lotus 123, WordPerfect Script with Quattro Pro, or StarBasic with OpenOffice, and 2 things become obvious: (1) Excel’s OM is much more logical and efficient than that of other spreadsheets (OOo’s is something only an unthinking FOSS proponent could love – I have to dig out my old SO 5.0 CD to see if it was as crappy before Sun got ahold of it), and (2) the VBE *and* especially stepping through VBA code is much better done than in the others.

    Neither of these things is specific to VBA the language.

    VBA is just another dialect of an awkward progrogramming language. E.g., while you may bemoan the much of the rest of C syntax, defining same type variables as

    double x, y, z;

    is one heck of a lot more convenient than

    Dim x As Double, y As Double, z As Double

    I’ve never liked VBA, but I use it because there’s no other practical alternative for working with Excel.

  4. Very late, but dont forget Microsoft Word. I am a lawyer by trade, but I have created some 10,000+ lines of VBA code to help me process my word documents in a variety of ways. For instance, I can take a PDF, save it as text, and have it converted into a well formatted, numbered, automatic clause referenced document with a few clicks of a button. Without VBA, you could not leverage a 10th of the power Word has under the hood.

Leave a Reply

Your email address will not be published. Required fields are marked *