Since the late 1990s I've worked with Excel. Typically I've coded up huge VBA code modules, win32 api calls, DDE, RTD, WinForms, interoperability with VB6 and C#, database access, webservice access and integration with financial toolkits such as Reuters, Bloomberg, Factset, S+ and R. I’ve also pushed the user interface and VBA IDE using undocumented functionality and the VBE extensibility library. A very simple observation is that the pace of change within Excel VBA has been pretty slow since 2000. Let’s analyse that assertion…
My baseline is that I currently run Excel 2013(64 bit) on Windows 8.1 on 64 bit hardware. I also run Excel 2011 on a 64bit MacBook Pro. So those are the most recent versions I know about. In the past I have worked with Excel 95, Excel 97, Excel 2002, Excel 2003 and Excel 2007. There’s a good document describing these versions on Chip Pearson’s website here.
Excel 2010 introduced a 64 bit compatible version of Excel, so that Excel could run on a 64 bit version of Windows – more details on this MSDN link here and this blog post.
The Microsoft links clearly show that there are no real changes to the VBA code base in functional terms between VBA6 and VBA7 as the changes are all made to enable underlying operating system changes. So we have to look at VBA6 as the last time there were functional changes in Excel VBA. VBA6 was introduced in Excel 2000 which was released in 1999. So it’s reasonable to assert that there is not a rapid tempo for change. It’s important to note at this juncture that change is not always needed – if something works, why fix it? My experience is that the lack of change in VBA has meant that it’s viewed as a legacy technology and not one with a bright future. In the broader world of IT a lack of change in that sort of timeframe typically indicates that there is an issue with a reduction or removal of support for the platform from the vendor.
Since Excel VBA has at times paid my mortgage, I thought it worthwhile to investigate some more and gather some opinions from folks within the Excel community. I put together a list of questions to ask a number of people:
(1) Is Excel VBA development dead as a viable consulting business model? Please expand - what makes you think that?
(2) If the answer to question one is yes then who or what killed it? By this I mean (as examples): the move to dot.net, open source software, lack of meaningful development of VBA
(3) Where will the VBA logic in the countless line-of-business spreadsheets around the world go in future?
(4) Was Excel VBA a product of that era which is going out of use in the same way as black and white TV or video recorders? Was it a dead-end? Or a missed opportunity?
The people who have contributed to this blog post are:
- Harlan Grove [HG]. Well known Excel based application developer - just have a look at the Excel newsgroups.
- Armin Sadeghi [AS]. A former Microsoft employee www.arminsadeghi.com
- Dick Moffat [DM] Another well known Excel based application developer.
- Simon Murphy [SG]. Self confessed “recovering Excel developer”. Have a look at http://www.codematic.net
- John Greenan [JG]
- Dick Kuslieka [DK] Writer and editor of http://www.dailydoseofExcel.com, author of Excel and Access books, Excel consultant and long time Microsoft MVP for Excel.
All of these folks were asked the same four questions:
(1) Is Excel VBA development dead as a viable consulting business model? Please expand - what makes you think that?
[HG] It was never particularly viable. Only a few made any money selling VBA-based utilities. It's still used for in-house enterprise development, but where I work it's being phased out. All I have is my own experience as basis for this opinion.
[SM] Yes.
[AS] As soon as .NET was introduced, along with the Office PIA (Primary Interop Assemblies), allowing for easy and independent programming of the Office API, VBA pretty much took a back seat for serious Excel solution development. The single fact that users could maintain their solution code independently of the Excel file pushed many solutions into .NET. VBA is still around, especially with the release of VBA 7, but sticking strong to its primary function of introducing non-developers to application automation (primary through the wonderful macro recorder), and proving a quick way for advanced functions to be done on a case by case basis.
Is VBA development dead? No way. It's still widely used.
Is VBA development a viable consulting business? Unsure.
[JG] Just have a look at the online job boards and search for Excel VBA. There are few opportunities out there. Many firms I have worked with have imposed new security and compliance models that have been interpreted as “No VBA”.
[DK] Yes and No. It's definitely in decline and that means that fewer VBA developers will be doing work. But for those that are left, it will be viable. I would never encourage someone to get into the business, but I would necessarily encourage someone to get out either. If you can't market yourself, then you need to switch to a platform where there's so much demand that you don't have to. If you're willing and able to do the marketing work, I think you can still do well in VBA development.
(2) If the answer to question one is yes then who or what killed it? By this I mean (as examples): the move to dot.net, open source software, lack of meaningful development of VBA
[HG] .Net did a lot to kill it off, along with VSTA/VSTO. However what really killed it was the difficulty of maintaining VBA modules in existing workbooks. It was possible to script the VBA object model, but it was fragile. Also, multiple-person development in spreadsheets is extremely difficult.
[AS] .NET provided a much better alternative. VBA's primary weakness was the fact that you couldn't easily keep the code in separate source files an in source control which resulted in low maintainability. Additionally .NET provides a much stronger library and ecosystem of code to draw upon.
[SM] Disconnect between customer (usually business) and who sources/pays (IT department). Shift in power from business to IT through use of security scare stories
[DK] Time killed it. Nothing last forever. Cobol developers were once in high demand. Now Cobol developers are in very high demand - both of them. Microsoft killed it by not updating the IDE or supporting VBA as a viable development platform. Had they invested in VBA, say by integrating .Net into Office the way they did with VB, then it still may have been a viable platform today. But even if that were true, time would kill it eventually.
The internet killed it by adopting Ajax. A lot of developer resources went to web apps and away from COM based development.
Apple killed it by inventing the App Store. None of those developer resources came back to COM, they're all developing mobile apps now.
So a bunch of stuff killed VBA, but all that means is that evolution killed it. MS evolved their development platform away from VBA just like they evolved away from ANSI C before that.
[JG] In many firms I have worked with the tactical developer is viewed as the (over)paid representative of the traders and as such is not trusted by IT. So there is a lot of effort put into working around policies, restrictions and roadblocks in order to get the job done. So I think the move to a far more compliance and legal driven world in finance has had a severe adverse impact.
(3) Where will the VBA logic in the countless line-of-business spreadsheets around the world go in future?
[HG] Where have they gone already? To some extent nowhere - there are a lot of workbooks with macros still in use. They're usually not major. As for the major/important stuff, lots is becoming browser-based.
[AS] Businesses I have run into are moving away from VBA because of the reduced security and transparency. To the point where many are banning it outright. Where will it all go? Little personal things will stick around. The rest will be done in .NET, along with the help of VSTO or similar over time.
[SM] VBA development work is still around, under the IT radar directly in the business or operations department, but rates are poor and quality is hard to achieve, due to IT restrictions and unrealistic deadlines, and the rates are rubbish. I think there has been limited migration to .net etc, many systems are now just for the user to look after, others just came to the end of their useful life, some got rolled up into big systems like SAP implementations etc. All that stuff live now will tick along for years, or until MS pull the plug on VBA.
[DK] Now that's a good question. As long as MS allows it, it won't go anywhere. I can't imagine MS breaking existing VBA code (said the VB6 developer right before .Net was introduced). It would be a disaster if support for VBA was discontinued and there were no alternatives. A disaster for Office sales, I mean. If MS didn't provide an alternative, someone would. But it would utter chaos for a while. I imagine in the short term people would flock to Google or Open Office because they have some scripting capabilities. If you think VBA is going away, I encourage you to be an expert at javascript and you'll have all the business you want.
I think the more likely scenario is that MS offers an alternative to VBA at the same time they announce a 10 year, or so, sunset period. I don't know what that alternative will look like, but it's going to have to have app-IDE integration. Hopefully it will also include a choice of languages - languages that are actively being improved. If they let me automate Excel using python or ruby, I would switch tomorrow.
(4) Was Excel VBA a product of that era which is going out of use in the same way as black and white TV or video recorders? Was it a dead-end? Or a missed opportunity?
[HG] Yes, spreadsheets are fading away generally. VBA with spreadsheets is fading away faster.
[AS] VBA is a nice introductory language that sits on top of the strongest OLE Automation implementation I've ever worked with (I.e. the Office Automation API). VBA is as strong now as it was in the past but there are now simply more options available. It's still used for those being introduced to application automation and does incredibly well in that space.
[SM] VBA was a product of a different era, when what users wanted was commercially important (to clients and vendors)
[JG] An opportunity of that time. Look back to the late 1990s when Microsoft was pushing VBA. The IT world looked very different and VBA was of that time. Nearly twenty years later the world has moved on, IT has moved on and VBA has not.
[DK] Yes. But it's nothing to lament. Everything dies - it's the cycle of life. Do you pine for the days before the internet? Or smartphones? I sure don't. There was something special about VBA, though. MS took a first class, high level language and embedded it into a suite of applications. It was pure genius and I think it contributed to Office's dominance in the business suite market. The missed opportunity is that they didn't let the embedded development platform evolve with programming languages like they did with their other development platforms. But it's only been a missed opportunity so far - they can still evolve the platform and catch up. But I'm quite sure they won't do it with the VB7 dll, that ship has sailed. COM development is gone. .Net may go away, but it's not going back to COM.
[DM] VBA is in BIG trouble and only because Microsoft wishes it would go away and will not offer a replacement. "Why?" is the big question that requires a detailed answer ..
I have always tried to look at the bigger picture and while I could spend my time writing external add-ins or I could move to VSTO and .NET I have stayed with VBA simply because I believe that all that is needed in excel is a “macro” language for automation of spreadsheets. I know that isn't "sexy" and doesn't seem like a way to build a person's career as a REAL developer but frankly I've never given a shit about stuff like that - which is why I'm probably not as well off as I might be otherwise ;-)..
Simply I think that spreadsheets should NEVER go away as there will ALWAYS be a need for them. I also believe that without an ability to automate them so you can do complex tasks reliably and repetitively then the spreadsheet will gradually lose its influence and the business world will be worse for it.
By "automation" I mean literally that, a way to automate what you do in a spreadsheet in such a way that a file (or files) can be used by anyone with little or no knowledge of spreadsheets or of the inner workings of the particular file or files. That is why VBA has been so successful and why it has lasted far longer than one would think it should. That is also why VSTO hasn't caught on ... because it is for programmers not for spreadsheet users and developers. VBA and XLM before it and Lotus 1-2-3 macros before that allowed for this kind of automation ... That was a large part of the success of the products.
I remember talking to a reporter from InfoWorld or some such mag when VBA came out and I expressed my concerns that VBA might just be too powerful and too complex that it might hurt spreadsheet development. I believe that was true for the most part. Decent VBA development is not easy but it is just on the edge so it has been used extensively anyway. But products like VSTO just push the envelope a little too far and can really only used by "REAL" developers coming from the Visual Studio camp. The fact is that VS developers don't give a damn about Excel development :-) ... What a stupid plan.
What's really needed is a true automation replacement for VBA. Ironically the most obvious one I can think of would be a SERIOUS upgrade of XLM. The idea that making the macro language in Excel based on VB so that it could merge in with the other MS technologies was a good one at the time. As always with Microsoft the delivery and especially the messaging was all wrong and then when they swung to .NET and canned VB Excel was left an automation orphan. The fact is that being an Excel developer is NEVER (or at least seldom) going to be a gateway to becoming a "REAL" developer using .NET or the various new Java-based languages they want us to jump on now.
So give us just a plain old Automation tool to replace VBA that can do EVERYTHING you might need to do "IN EXCEL" and the world will be a better place. And Microsoft will actually make money too!
In conclusion, I am not sure we reached a conclusion except for noting that time has moved on and the use cases for Excel VBA are reducing in number.
Comments
Post a Comment