The Future of VBA

Alex commented:

Dick – Can you confirm that VBA still has its place in Excel 12?

Harald responded appropriately (it will), but it reminded me of something. I made a prediction in a Seattle bar that I need to get down on bits magnetic media paper. I don’t really remember the circumstances that led to this prediction, but someone (Harald?) posed an interesting question about VBA and .net. Usually when I don’t remember a lot of details AND I’m spouting off some unsolicited theory, it means I was drinking. Go figure. Here’s the prediction:

By the time VBA is depracated, the whole .net platform will already be yesterday’s news. By depracated, I mean that you won’t be able to write and run VBA code in Excel. It doesn’t mean that MS will quit updating the IDE, because they’ve already quit doing that. And by yesterday’s news, I mean that MS will no longer be pushing people on to .net. They will still be shipping the runtimes, but they will have moved on to something substantially different. In short, when you are forced to switch from VBA, you will not be forced to switch to .net, but some other platform/language. Book it.

Posted in Uncategorized

28 thoughts on “The Future of VBA

  1. Here is my prediction:

    Ever since .NET and VSTO, I’ve wondered about the non-Microsoft products which use VBA. It is not just Office which use VBA, there are 100s of other products which also use VBA.
    Visio (before MS gobbled it), Business Objects, PI ProcessBook and lots more.

    What was going to happen to their products. Keep using VBA SDK 6.1 indefinitely? Microsoft surely has a plan to move their products up and pull some $$ on upgrade costs.

    Then along came a post over at Automate Excel – the VSTA replaces VBA theory.

    Although Mark stepped back on his theory, I still reckon he was spot-on.

    It’s quite likely that a couple of versions away, VBA will not be installed by default (or at least, it’ll be invisible). VSTA will be recommended over VBA.

    More and more features of Excel’s object model will become available from .NET and not from VBA, leading programmers to choose the superior and more feature rich development environment.

    What is more, advanced options might even make it possible to program in your favourite programming language… J# for Excel anyone?

    .NET code will live in the (zip) compressed xlsx file, but as a separate file to the spreadsheet.

    What Dick wrote is quite right – the IDE has already had development stopped.
    What is quite interesting is that certain features in the VBA IDE are not yet available for Visual Studio… “Edit and Continue” comes to mind.
    It is simple features like this that could make/break user’s decision to migrate.

    Once VS.NET is the superior choice over VBA (in the end-users eye, not just MS), we’ll start to see the push. It wont be too long I think.

    If I’m wrong, please dont rub it in – but, if I’m right I take all the glory! ;)
    PS. I’m not MS MVP, so I dont know what VBA is coming in this version of Excel or not.

    Rob

  2. I agree with most of what You and Dick write.

    With the coming new version of VS.NET and especially VSTO 2.0 they will be much improved compared with present versions. Of course, the new versions will increase the attraction to use the platform, both for replacing present VBA-solutions and also to develop future solutions with it.

    An upgrade to Excel 12 when it hit the market will be a good time to review all present VBA-solutions and port some of them to VSTO as well.

    All in all, it will propably be a natural movement to move from VBA to new technologies over the time.

    Progress is progress ;)

    Kind regards,
    Dennis

  3. Please excuse my lack of knowledge but what is IDE, VSTO, and VS.net?

    I am interested in the future of VBA. I am having a hard enough time learning VBA that when the time comes to change I am going to be OL. Big time. I am scared enough about going to .net, so I believe that, and I don’t know if this possible, because of all the Excel/VBA experts putting up such a stink about changing, Microsoft may drag its feet in changing to .net and move to the next platform. The next platform may be an easier transition from VBA.

    I would like to stink with everyone else, and slow up the transition from VBA to the next platform. What ever that platform will be.

  4. VBA: Visual Basic for Applications
    VSTO: Visual Studio Tools for Office
    VSTA: Visual Studio Tools for Applications
    VS.NET: Visual Studio.NET – currently version 2003. 2005 due next month.
    IDE: Integrated Development Environment (in Excel, this is the visual basic editor)

    VBA and VB6 are pretty much the same language.

    The “stink” is/was that developers can’t move their VB6 code to VB.NET without significant rewrites.
    If I recall, about 1 out of 10 lines cannot be migrated automatically.

    A lot of MVPs and developers participated in a petition to convince MS to support a kind of VB6.NET, which I believe Microsoft considered and rejected.
    Not all of the Excel/VBA experts signed the petition.

    Charlie III: If you want to come up to speed quickly, participate on Newsgroups. Read every post – understand each line of code.
    microsoft.public.excel.programming is quite active.

    Rob

  5. I just started learning Excel for VBA, and I was very happy about it. Now I’m reading that it will be replaced in the future!? Is this right? Should I start learning VSTA now?? When do you guys think VBA will be extinct?

    Macro Man

  6. It’s worrying but sadly true.

    M$ seems to have completely disowned the concept of reusable code and has moved on to “reusable customers”.

  7. Macro Man : I don’t think anyone knows for sure when/if VBA will go, but it was meant to replace XLM in 95 and XLM can still be written and run in 2003. So I’d say 10 years or so maybe more.

    Mpemba : life is a compromise of progress v backwards compatibility. .net has significant advantages for large projects, and VBA isn’t going away anytime soon. Customers can choose which technology works best for them, which seems pretty good to me.

    I think VBA is still very relevant, and will be for a long time. Equally there is increasing interest in .net for office (VSTA will surely happen at some point), so its a good time to be an office developer.

    cheers
    Simon

  8. I was just about to throw myself into learning VBA in Excel because my agency switched from 1-2-3. I thought that it also might be a skill that would be transferable to some other position with another organization. I came to Walkenbach’s site to see whether there was anything that could tell me whether immersing myself in VBA would be a good idea. This discussion leaves me in a quandary. Just how likely is Microsoft to drop VBA from Excel? Will .NET become the next programming language to use with Excel? How much is .NET like VBA? Would knowledge of VBA help me if Microsoft does switch to .NET?

  9. I’ve been in the financial services industry for a while, I have an MBA in Finance. With my background I thought it would be a good idea to combine it with my new found knowledge in VBA for Excel. Would it be a good move to make a career change and be an Excel developer for a trading desk? Should this .Net thing affect my decision? Learn VSTA, or other languages? Or not make the change at all?

  10. As said by others in this thread of messages: VBA is here to stay for at least another 10 years.

    So if you’re thinking of learning VBA or expanding your knowledge in VBA: do so, you’re not wasting your time.

    If you’re considering trying .NET to work with Office, remember that you limit yourself to Excel 2003 and newer. Many potential customers are still on Office XP or even Office 2000. Heck, some are even on Office 97!

    But learning the .NET programming environment is a good thing for any developer I guess.

  11. As others have said, VBA will be around for many, many years yet. I’d say at least 15 years. If you’re thinking of learning .Net, though, I’d suggest waiting until VS2005 hits the shelves in a few months time, which (re)introduces many of the time-saving features we’re used to in VBA – such as edit-and-continue when debugging. It also includes a much better VSTO version (2.0), which is vastly superior to v1.0. I’m also assured that the rtm build of VS2005 will (finally) fix Excel’s very nasty ‘Locale issue’.

  12. The beta versions of VS 2005 / VSTO 2.0 looks very promising and we should not forget that it took nearly 20 years to develop Excel to what it is today ;)

    Jan-Karl,
    Are you using vmWare or Virtual PC to set up the virtual machines with?

    Kind regards,
    Dennis

  13. Dennis: I think the question to ask is at what point does it make sense to start adopting the VS/VSTO way of doing things? While some started with VSTO 1.0 and more might look at VSTO 2.0, I don’t think it will become ‘mainstream’ until sometime towards the end of 2007 – with the Office 12 / VSTO 3.0 combination. There are a number of factors that make me think that:
    1. VSTO 2 still only targets Office 2003 Pro, which isn’t exactly widespread. I’ve no idea if VSTO 2 will also work with Office 12 (but I doubt it).
    2. Office 12 won’t hit the streets until late 2006 and even if it becomes the new de-facto standard Office version, it’ll take until at least mid-2007 for that to be the case.
    3. VSTO is on a different ship schedule to Office, so v3 is unlikely to be released at the same time – perhaps mid 2007 (I’m guessing).
    4. By that time, the .Net 2 framework will probably have penetrated more/most desktops, so easing current distribution concerns.
    5. I doubt if there’ll be much Office 12 documentation showing how to do things using VB6 (e.g. Ribbon customisation, custom task panes etc), so if people follow the docs, we’ll end up in .Net!

  14. Stephen,

    Interesting as You clearly point out the gap in the development pace between the NET-platform and Office – thanks.

    But don’t You indirectly answer the question You raise?

    Personally I would put it as the following based on my experience (read failures) with VS 2003 / VSTO 1.0:

    “Not until Excel will be based on 100% managed code”

    I asked David Gainer at his blog if Excel 12 will support ADO.NET. In his reply he said no and at the same time referred to VSTO for database connection based on ADO.NET for Excel. It suggests that at least *one* version of Excel 12 will be supported.

    Despite all the issues You point out and the issues with VSTO 1.0 I believe we should, at least, evaluate the final version of 2005/2.0 in order to see if we can create real world applications or not with it and so far it looks promising.

    Despite the fact that MSFT have announced that Classic VB is “dead and gone” it still will be valid.

    Let us also see if Windows Vista will support Classic VB or not…

    Kind regards,
    Dennis

  15. JK,

    Virtual PC is great isn’t it?

    I maintain an application written in an older version of Oracle Forms… occasionally.
    The installation of Oracle Forms is straightforward, if you don’t already have a later version installed. If you mix newer and older versions it can give you headaches – kind of like mixing versions of Excel I guess (not that I’ve ever done that).

    Not long ago I discovered Virtual PC, and all my install problems were over.
    I dont have to worry about my new version clashing with the old. I just keep the old version in a Virtual PC.

    I’ve been very impressed with the features, screen resizing, performance, drive mappings, networking.

    Again, it’s great!

  16. >life is a compromise of progress v backwards >compatibility. .net has significant advantages
    It may well do.
    But there are significant advantages for customers in not being “forced” to rewrite code in order to just stand still.

    Many of the forced changes in VB.net for instance were unnecessary for progress. Others represent truly enhanced functionality. There is a difference.

    >Customers can choose which technology works best
    >for them, which seems pretty good to me.
    Yes, indeed, choose.
    To get a new licenced copy of VB6 were had the right to choose to switch to VB.net or go to ebay. Microsoft did not “allow” the old product to compete for long. They did allow the “downgrade route”.

    The irony of the “illusion of progress” paragigm is than in my field, numerical analysis, most code in use today is still written in either Fortran or ports of that Fortran to C. It works today as well as it did when it was originly written 25-y ago and gives correct results (unlike my spelling).

    Don’t get me wrong: I love Excel. I want Excel to expand (particularly in a positive direction: 1) more rows and colums is good. 2) 16-bit integer and double-double precision floats would be great. OTOH changing the design and arrangement of buttons is pap.

    M

  17. >2) 16-bit integer

    Doh!!!

    I meant 8-byte (64-bit) integer (without using currency type).

    You don’t need to completely re-structure a language to add funtionality

    M

  18. Mpemba: “changing the design and arrangement of buttons is pap.”

    I disagree, for the majority of the users it will be a blessing in disguise. So much functionality has been hidden from the user far too long. Not deliberately, but simply because the menu structure is far too complicated. Even I have to search menus when I need a specific feature in e.g. Word (which is not my area of expertise, but I use quite extensively). And remember: I often know some functionality is there and sometimes still have a hard time finding things. If you don’t know its there, things will be even harder to find!

    Dennis: 2 GB Ram? hmm. I have 512 MB. Time for an upgrade I guess .

  19. Mpemba don’t get me wrong, I think VB6 support was dropped far too early, and I take your point about VB6 no longer for sale. I was really thinking more about VBA v .net, as VB6 has been largely irrelevant for excel development.

    The .net language structure is developed to support full object orientation which leads to more understandable designs and code on larger projects, rather than just to add new functionality.

    In terms of when .net will be the ‘norm’, I would think around the time that most potential customers have the .net runtimes already installed, and a compatible version of Office. Stephen suggests tail end of 2007, which sounds reasonable, I certainly can’t see it before. Unless Windows XP sp3 installs the runtimes to make deployment straightforward (and Office 12 take up is much quicker than recent versions).

    I think there is plenty of life still in VBA for anyone considering a career move. If you are worried about change, and redundant skills, don’t go into (business) software development.

    2Gb RAM is about right for VPC too
    cheers
    Simon

  20. Comparison: Using VSTO with MS Office 2003 UI and Windows form based UI

    Using VSTO with MS Office 2003 UI

    ?VSTO is a new MS office developer tool by which we can upgrade old VBA based MS Office documents to .NET based managed code application. This provides complete functionality to implement managed code advantages and use of any .NET languages like C# instead of only VBA in macro modules. This approach provides an incredibly rich set of libraries and functions for managing the user interface, communicating across the network, publishing and consuming XML Web Services, accessing the file system and relational databases, security and more.
    ?Using VSTO one can develop UI using MS Office as front end. In this case VSTO code acts as code behind to Office Documents and templates.
    ?Using VSTO one can add custom Action pane, Add-In menu or Windows form having Menu strip.

    Using VSTO with Windows forms UI

    ?We can add VSTO class library in Widows form application. Using this approach we can use MS office (Word / Excel) as reporting tool without client side office installation requirement. But we can not use office as OLE object in VS 2005 forms to provide in-place editing with Word or Excel document. This is because MS Office is one MDI Application and windows form architecture does not support to make any other MDI UI to become its child form. In Visual Studio 2005 (Whidbey) there was one control named ActiveDocumentHost control which was aimed to act like OLE control but due unknown reason Microsoft did not include this control in final release of Visual Studio 2005.
    ?Currently we can open office document in Windows form using Microsoft explorer control but this has many limitations like we can not edit office document as it is read only at run time. We can not use any MS Office API or VSTO library function to perform activities like insert table, delete text or bookmark.
    ?We can add windows form in VSTO application (as a part of office document which can also act like data entry form for edited region) but we can not add Office as part of windows form
    ?If we use MS explorer to view office document then for every new event performed by windows forms events on office document like inserting a table, deleting selected text one has to recreate word documents on the fly and reload in explorer.

  21. I don’t envy Microsoft. IT is a constantly changing world, that is true. But right now, it’s a hard time for Microsoft. Bill Gates has got outa there. Ballmer is in charge. And with a new Chief Technologist, brainy, like Ray Ozzie and Chief Nerd like Anders Hjelsberg – none of them are Bill and Bill pushed the company in the early years and went through Microsoft Basic for CPM/80, via GWBASIC via VB/DOS via VB6/Windows etc… Sure he wasn’t the plumber for quite a while, but he rode shotgun for so long, championing BASIC.

    VB.NET or .NET is NOT BILL GATES BASIC. It is a new language, very C like, to the extent that the young kids ask why not C# instead? It tries to con you by using VB like commands like LEFT() but it is a different horse. It does think like VBA or VB6, you are advised to move on to new command equivalents and so on. It is not an end in itself, it is a scripting language to control that monster .NET framework from hell. I am migrating from .NET 1.1 to .NET 3.5 and the new tech is again one level different.

    Coming back to the Office dilemma – Office is an intewoven bunch of COM / ActiveX objects. It is not built on .NET. They don’t know how to build a blow by blow equivalent in .NET. They either lost their documentation or never wrote it. Over the years, COM based Office has been extended and extended and extended. Some team has to re-engineer the beast in .NET. But at what cost, resource and time handicap? Is it suitable use of $$$ and resources to re-engineer a new Office in .NET which only just makes it the same feature equivalent? Like Office 95/Win95 was compared to Office 4.3/Win16. No – if you look at Office 2007 – there are a raft of feature enhancements including the Ribbon, Quick Parts in Word, Smart Tables in Excel, New Masters and Layouts and in Powerpoint, a whole new SmartShapes, Smart Charts shared module – they have again extended Office/COM instead of re-engineering it as Office/.NET.

    And as long as you have Office/COM, VBA is sweet. It is not a programmers language, it is a business man’s language. It has superb interactive yet simple IDE and debugging. It has the minimum of conversion by Datatype Casting or esoteric PASS BY VALUE vs PASS BY REFERENCE concepts. Sure you can cobble on .NET in VSTO and satisfy the young hands who dream in C# or Java and feel they need a object oriented, leading edge language, but so far VSTO etc.. are EXTERNAL to Office, not built in. Can it be clothed to look as internal as VBA and stored inside the compound document object? Maybe, if the document is XML based.

    VBA is the upper hand Microsoft Office has over Star Office and Open Office. Essentially, a team of good programmers can make Open Office blow for blow equivalent to Microsoft Office, one version delayed. No big deal. But Open Office cannot equate to VBA because VBA is proprietary. They have a team translating VBA to some kludge of Open Office languages – a crude Basic, a Java like script etc… but it’s not 100% or even 50% VBA.

    VBA is the reason why people use Excel. Take it away and there is no need to use Excel. You could use Open Office Calc. Or Google Calc.

  22. hat tips to all the real experts in here – imho there is another non programmatic reason why it will be very difficult for MS to push VBA out entirely, though keeping it / adding in VSTA simultaneously hast seemed to be considered as an option (is that even technically feasible?)
    the other reason is purely financial. MS make their money licensing the software they develop – hence they need to do one of two things, sell more copies or force users to upgrade / buy new versions. in the last 2 upgrades especially (3 if you count office XP) there has been a massive slow down by large corporations in their uptake / upgrade speed of the new versions. I now of many large corporate clients now (including those on software assurance plans) that have slowed their upgrade speed to 3 and 4 years behind release dates. In other words they are still now only just upgrading from Office 2000 to office 2003. It will be 2010-2012 before many of these corporations start rolling out office 2007. – using the same logic – it will be 2014+ before they start rolling out the next version – and that still has VBA in it. Most large corporate customers will have numerous large VBA developments that are key LOB apps.
    I would predict that MS cannot and will not remove VBA for at least the next 2 versions simply because doing so would predictably slow that rollout lag even further. Put simply – they need the money.


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

Leave a Reply

Your email address will not be published.