The Future of VBA Development

Remember nine years ago when I posted about the future of vba? Neither did I, but I just re-read it. I think if we keep talking about how VBA is dead, it might actually die someday. Nah, probably not.

John at Global Electronic Trading has the latest VBA eulogy. He asked several VBA community members (including me) to answer four questions about the future of VBA. Here is my response to what killed VBA

[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.

Go read the rest of the answers. You won’t be surprised by any of the answers, I’ll bet.

I draw two conclusions from this experience:

  1. I need to proof read my emails before I send them.
  2. I don’t care if VBA is dead. It still works for me now, I’m very effective with it, and I’m still solving real problems using it every day. If it’s dead, it’s the best damn corpse in the office.
Posted in VBA

19 thoughts on “The Future of VBA Development

  1. “2.I don’t care if VBA is dead. It still works for me now, I’m very effective with it, and I’m still solving real problems using it every day. If it’s dead, it’s the best damn corpse in the office.”

    Same here.

    Also Dick Moffat’s final comment at the link says much the same (and is very well put).

    It may well be that Excel + VBA has had its day for full time developers, but for those of us who develop applications in their spare time, whilst doing another job I don’t know of another application that comes close in terms of combined power and ease of use.

  2. VBA won’t go away any time soon: too many millions of lines of code out there.
    MSoft could have produced a .NET replacement for VBA but its now too late.
    VSTO and Interop is a niche alternative used by IT and fails as a VBA replacement because:
    – lousy performance
    – no UDF support
    – no Macro recorder
    – requires visual studio
    – there are better .NET based alternatives with XLDNA and Addin Express

    But the real killer is BYOD and Excel on multiple end-points – Server, Cloud, Mac, IPad, IPhone, Android devices.
    Neither VBA or .NET will solve the problem of extensibility/automation/programmability for that.

    Either MS produce a solution within the next 2/3 years or someone else (Google?) will produce an alternative that replaces not just VBA but also Excel.

  3. Very interesting post. HS conflated spreadsheets themselves disappearing as well as VBA, which caught my eye.

    Because the contributors make their living from sophisticated spreadsheet work, I believe they are rightly sensitive to the encroachment of various tools (Tableau, Python, other products or languages) that snip away at the one-size-fits-all approach of Excel and VBA. But to the “unsophisticated” business users, spreadsheets are not going away. I’ll try not to clog this post with too many of the Excel-based reporting tools just our organization uses: Kaufman-Hall (budgeting), Hyperion, Vista (reporting), PeopleSoft Financials (exports to spreadsheets), and so on. Right now we’re looking at SharePoint and PowerViewer to replace a custom BI app.

    VBA succeeded not because it gave tech professionals a better tool, but because it gave tech amateurs (but business pros) a better tool. The business pros know when they’ve outgrown a tool (though they can’t always do something about it in a timely fashion because they’re prisoners of legacy uses just as much as IT. Business pros will NOT start coding in Python or .Net. They may buy an app that does that coding on top of Excel, but I don’t think there’s a finance department in the world that will give up Excel-based analysis.

    Does Power Pivot give enough drag-and-drop analysis to replace amateur VBA? Depends on the circumstance and the sophistication of the user. But you can’t leave a finance director without *any* customization capability. Dick makes that point in his post… “because it [VSTO] is for programmers not spreadsheet users..” But he uses the past tense, “… that was a large part of the success of the products.” I don’t think that success is in the past. If we can adapt PowerViewer to replace the expensive and convoluted BI product at our organization, Excel will continue to dominate. And the ability to adapt PowerViewer isn’t a MSFT problem, it’s our organization’s problem to get the data into our data warehouse in the first place, not to generate the reports afterward.

    Personally I like VBA, as much as I can like any code. Yes, the object model can be inconsistent, and moving between apps is a pain. (Have you tried to get task- and resource-level data from Project into Excel?) But anyone who consistently reads, let alone contributes, to these forums is probably ahead of MSFT in being able to generate cross-product reports using VBA despite its shortcomings. This isn’t necessarily a slam at MSFT – there are just too many real-world needs that MSFT can’t envision, let alone jam into OOTB reporting. And PowerPivot is fine when you don’t know what you’re looking for, or need to slice ‘n’ dice until you find it. But if you know what you need, VBA and formulas pick out the exact answer and put it in the exact place where other apps or users can find it.

    (With a shout out to SUMPRODUCT, my SQL query equivalent in just about every spreadsheet I use.)

    There will almost certainly be rate erosion for developers, as in every other field in the 21st century, but that doesn’t translate to Excel itself going away.

  4. Although the question is about VBA, the answers seem to be restricted to Excel.
    The future of Excel isn’t identical to the future of VBA.
    In my experience VBA is much wider applicable than to Excel or even Office alone.

  5. I agree with snb. I write 100-200 lines of new VBA code each week that has nothing to do with any Office product.

  6. Old isn’t so bad. I’m old ;) Working mostly in Linux these days, and the fresh grads are just fine with vi-or-emacs in a console. Nope, no IDEs for this set. These things are older than VBA, but none of them wants to use anything any newer. Go figure.

    Excel/VBA is similar. Not the shiniest pebble on the beach, maybe, but the combo of an OOTB IDE, Object browser, macro recorder, and code-completing REPL (er, “Immediate Window”) is still a swell toolset. I wish MS kept improving this stuff, but they got it basically right 20 years ago. I mean, I like C# well enough, but VSTO? meh. It doesn’t offer enough to justify the extra working part.

    And if you want to drive Excel from the outside try that other old/new thing Javascript, via WSH. Personally I like the Javascript language (closures, fp, etc) better than VB, and it works via the same old COM (YMMV!).

  7. VBA died in my mind when I learned VB.NET+C# and VSTO.
    Sure, VSTO requires a little more work at first, but once you’ve learned the basics, it’s rocketing way past VBA and the IDE’s limits.

  8. Indeed, MSFT is way, way behind in providing automation capability for their Office365 suite.
    And then one asks why Google hasn’t done this yet….I mean they are purported to have the brightest minds in software. Well, the reason is: rewriting the VBA interpreter and the Excel object model to support JavaScript is a huge endeavor…I would guess in the 10+ man-year range.

    The huge issue going forward if MSFT can get Office365 automated in the cloud: Are they going to provide a CONVERTER to take existing XLSM workbooks and convert them to JavaScript (or whatever scripting language) ?
    If not, that’s good news for us consultants…..there will be tons of conversion work out there.

  9. I find that my use of VBA is WAY down over the last few years. The functionality of pivot tables, sliceers, queries (not new, just new to me) and more recently PowerQuery has allowed for use of standard Excel functionality instead of VBA for many data tasks. I now use VBA for automating data update tasks (instead of ETL tasks), and to automate user interface, report generation, and repetitive tasks (for the most-part).

    Having said that, I would not want to be without it.

  10. I find VBA extremely useful in terms of standardising data processing, which saves time and effort (and my memory).

    I am still amazed at what I can do with my somewhat limited knowledge. I have a number of small but essentail items that are in my ribbon for daily use, and the amount of time they save is extraordinary.

    I just hope it continues as it is invaluable to amateurs (both gifted and like me, less gifted but mildly proficient).

  11. Throw KISS to MS

    Excel needs an improved VBA version that can still be used by novice programmers and spreadsheet users, as well as IT level programmers. These enhancements should include support for Unicode Strings, Arrayed Constants, a fast math package for UDF development and an editor with formatting and spell checking capabilities for source code maintenance and presentation. Support for In-line “C-Code” would be nice. These are just a few features that come to mind. The current Excel GUI is a disorganized, unintuitive mess that lacks a comprehensive, well organized command structure that can be used to find, invoke and complete commands from the keyboard without switching back and forth to the mouse. Documentation, while present, at best represents a perfunctory effort, exemplified by the provision trivial examples. While the number of users of this product can be counted in the millions, those that have successfully penetrated program functionality has a count several magnitudes smaller. This fact may be attributed to these shortcomings. Yet another daunting development platform (with steep learning curve) in not what is needed here.

  12. In my opinion, Power Pivot with DAX is not a replacement for VBA. DAX is very unorthodox, difficult to learn and fragile relative to hosting pivot table. My employer used Power Pivot for a couple of years and build several complex apps with it. We have been replacing them with VBA code, with standard pivot tables for the UI.

    VBA is unlikely to ever disappear until MS replaces it with something comparable, which they do not seem inclined to do. But we are unlikely to get MS support for new things (OAuth, JSON, MongoDB …)

  13. Just came across the original post, and with the wisdom of 15 years hindsight you were spot on! It is now all JavaScript, .Net is very old news.

    And writing a simple add-in for JavaScript is even more difficult than .Net.

    Microsoft desperately want to drop the unfashionable VBA, but their users will not let them. They will never upgrade if their existing apps fail. Might as well use Google Sheets once VBA is gone.

    But here is the rub. .Net still used COM. So while Microsoft has been diligent about not making a single improvement to VBA proper over the decades, VBA uses COM so gets access to most new Excel features.

    But JavaScript does not use COM. It uses something far more obtuse. And we will start so see new features only added to that new interface. And that is how VBA will, eventually, die.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.