Is VBA a dead end ?

This topic came up on Excel-l, I got shouted down on there, so I thought I would elaborate on my views here. (its mainly an Excel view but I don’t think the other MS Office apps are much different.)

I see two sides to the VBA story – the technology and the career.

Technology wise a VBA solution is a dead end – it can’t be run on a server (officially), it can’t be run in a browser, it can’t be run in a high performance cluster, it can’t be run on mobile devices. This means it can’t be easily scaled if more users or geographies must be served. You can’t really even have multiple simultaneous users of an Excel VBA app. With the relentless march to cloud and mobile, VBA is left behind.

Of course MS won’t remove support for VBA, the world would fall apart, well the finance, pharma, and energy industries at a minimum. But the editor is pitiful by the standards of this century. It is testament to the work of those MS guys in the ’90’s that the debug Edit/continue is still better than nearly all mainstream IDEs. But its still out of date and hard to extend.

MS can’t invest in VBA because their strategy is .net/cloud/browser blah blah. They had the chance to fight on their strengths – rich client, but instead they tried to be google. And lost. Now we are all struggling with crap web sites in bloated browsers with slow Win3.11 style interactions from 1995.

VBA hasn’t suddenly become bad, its still very good at what it does, its just that we are often now looking for more from our solutions. More users, more device types, more threads, more whatever.

So its not dead but it is basically limited to single user apps on their individual workstations. But I am sure it will continue to work for a long time.

From a career point of view, what else is like VBA? nothing really, so expertise in VBA doesn’t easily lead onto anything else, hence its a bit of a dead end.

VBA is not really object oriented so it doesn’t lead to the sort of object based designs that C# and Java are suitable for. Its not functional so it doesn’t lead to F#/Scala.

Of course you can write VBA in classes, but lack of implementation inheritance hobbles your solution. By the same token you can write VB style ad-hoc procedural code in C#/VB.net, but you would be missing out on many of the elegant OO features of .net. You would struggle to implement most modern design patterns in VBA, and the latest CV must-have MVC is also all but impossible.

From a functional point of view (probably more useful than OO these days) VBA can’t treat functions as data smoothly (although CallByName does better than many languages). And a little toot of Application.Run can ease the friction too.

In Excel VBA especially we can get an awful long way without worrying about user interface/user experience or data structure. This is great for your current users (fast development/deployment) not so good for your career development – no other system is so well integrated as Excel/VBA. No worrying about Restful APIs, onClickListeners, Asynchronous callbacks, all very powerful, all generally avoided in VBA land.

If you love VBA, and why not, then continue on, you are probably adding untold business value with the work you are doing (and perhaps a little future maintenance pressure for someone). But I’m not sure it will take your development career too far, business career possibly but IMO it won’t help you much as a developer.

If you are doing VBA now and thinking of progressing to other development technologies then consider carefully how you will make your next steps. Adding Access and SQL to VBA is completely doable, adding modern scripting, web technologies or .net/Java is much tougher.

Getting into .net via VB.net is a possibility, but, I think it would be easier to go straight to C#, although the .net/Excel story is still, in 2017 very ugly.

ExcelDNA might be a good bet if you want to inject a bit of .net into your Excel life. I found this approach much more viable than VSTO infrastructure hell. Somebody somewhere is maintaining a lot of both that I developed. I bet the ExcelDNA maintainer is having more fun.

I also managed to blag my way into writing a load of custom Java on Essbase, that route probably isn’t open for many though.

Which led to my new focus on Android which is both challenging and rewarding. Although stay posted for news on that…

btw this is my 1,000th post on SOS, I assume WordPress will give me some superficial badge of honour, reward or ‘achievement unlocked’ bollocks, in the modern way.

cheers

simon

 

Advertisements

4 Responses to “Is VBA a dead end ?”

  1. Paul Christie Says:

    Simon, good to see someone else realising what dawned on me 2 years ago. What work i have done in the last couple of years has involed using Excel Tables and Power Query and only putting vba where either the user asks for it or doesn’t have the wit and imagination to realise that if the workbook is structured properly, and the user actual understands Excel you don’t need it.

  2. Biggus Dickus Says:

    Good points Simon as always.

    But since Excel Client is going to last FOREVER because there is absolutely no way (short of embracing running full Windows in a Cloud VM) that the capabilities of Excel Client and VBA will EVER be available as a Cloud app in itself. If MS could have done it they would have by now and I think they can’t (for many of the reasons you cited).

    Given that, if I accept that there will always be a need for Excel Client and thus for Excel automation (don’t get me started on their JScript efforts) then VBA (which in all practicality JUST a “Macro” language for inside Excel Client) has a great future if we want it.

    I have just completed and shipped an Add-In for Excel that is an ETL for Excel to power BI transformations. AND IT IS WRITTEN ENTIRELY USING VBA … It is at

    XLPublish.com

    In addition to showing what IS possible in VBA we’re hoping that it opens up Excel as a broader genuine source of content for production Power BI Dashboards and Reports – FOREVER.

    Good the hear you back here Simon ..

    Dick Moffat

  3. juuxjuux Says:

    As said, VBA will never die, there is just too much of it out there and it is deeply entwined in the most change-resistant and/or cash-strapped of sectors; banking, finance, research, health etc.

    I moved away from VBA about six years ago but was flung right back into it a couple of years back as it was used as the scripting language for some older versions of EMC Captiva and I snagged a job on the back the magic TLA in my CV.

    VBA I knew, but the obtuse object model of the IA implementation I was not aware of (and I said as much in my interview) so that was a fun 18 months figuring *that* out as sole dev, tester and support…

    It was surprising how much I’d forgotten in that 4 or 5 year gap. Still very easy to dick about in, I used it in preference to the C# environment provided for the QAD data cleansing etc. All the same, it was obviously harder than it used to be to find libraries that were supported under VBA/6 for some tasks.

    I occasionally get agency queries for quant stuff but they go cold when they realise I’m no first class mathematics graduate. Other than that, I reckon my VBA future will be limited to stuff like automating the Christmas card label printing, so in that sense I don’t expect it’ll be featuring in any future role.

    You never know though, maybe in 15 years we’ll all be getting silly day rates for VBA work, much as the COBOL guys have been getting since the bulk of the knowledge left the industry?

  4. Simon Says:

    jj – yeah cobol rates would be good…I won’t hold my breath though.

    PC & BD – I love it, you both agree with me, but seem to have opposite views. But I agree with both of you too!

    Dick – good luck with XLPublish – sounds like a great Idea

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: