… is the name of a Stackoverflow thread that sends a steady trickle of visitors here (thanks to a link posted by Charles Williams of FastExcel). On a similar theme, Smurf on Spreadsheets has a post entitled “What’s so good about VBA?” that has just celebrated its fifth birthday, and has also recently attracted a comment reading “don’t learn VBA”, which links to a blog post entitled “Losing touch… or why Excel and VBA won’t cut it any more“.
Since all these discussions have generated many comments that are at best misleading and often outright wrong, I thought I would add my perspective.
The first comment from Stackoverflow says that VBA is a “legacy language”. No, it isn’t. A legacy language is one that has been superseded by others which provide better functionality, but is still used because applications written in the old language are still in use. This isn’t the case with VBA. Spreadsheets remain by far the most common application for number processing of all types, Excel remains by far the most widely used spreadsheet, and VBA is the only language tightly bound into the Excel object model, that allows both expert and non-expert programmers (and those in between) to greatly extend the scope and power of Excel with the minimum of fuss.
Further down the page at Stackoverflow there are some good comments about the sort of thing that VBA is good for, but my favourite summary was from Harlan Grove at the Smurf discussion:
Poor analogy, trowel vs backhoe.
Better analogy would be people who use a pickup truck for their job and choose to maintain the truck’s engine themselves. VBA is a 1958 Ford engine for which you need a full set of socket wrenches and maybe a hammer. VSTO is a 2009 Ford engine for which you need a year’s pay to buy all the electronic diagnostic gear.
If you use the additional stuff for the 2009 Ford, your engine will run more efficiently. But if your day job isn’t being a mechanic, is it worth the time & effort learning to use it?
Actually this is where my analogy breaks down. VSTO solutions even when used well aren’t necessarily more efficient than pure VBA. And the analogy breaks down further because truck engines are a lot more standardized than the different .Net versions.
So that’s what VBA is good for. It’s the ute of computer programs (a ute is a pick-up truck, for non-Australians). It might not always be the best vehicle for the job (although often it is), but it gets the job done, and you don’t need a special licence to drive it.
What do you think? Is Excel+VBA still worth learning, or has it had its day?
Newton Excel Bach, not (just) an Excel Blog 
Thanks for the link! I wrote the post you link to above, “Losing touch… or why Excel and VBA won’t cut it any more”.
I’d agree with most of your analysis and really like the old pickup analogy. It may not be modern but there are a few jobs VBA is very, very good at and as you point out (and I did in my own post), it has the huge advantage that you’ve already got a copy.
Why I say it won’t cut it any more (and wouldn’t recommend to a fresh junior analyst to learn it in detail) is that a lot of what VBA can do is in the process of being superseded, or already has been. The object model was massively useful when there wasn’t a better way to deliver automated reports, or store larger datasets, but now there are several ways which are much better than Excel. Not all are free, but progressive companies will be investing in the new tech. As a junior analyst embarking on your career, you want to future-proof yourself and work for one of those companies, not a slow, bureaucratic one where IT won’t let you have anything except Word and Excel.
It’s good to learn a bit of VBA – like learning to maintain your pickup – but don’t bother learning to tune up your old pickup so that it will do 120mph. Use it for what it’s good at, and with open source and/or an understanding IT department, you can have a sports car for free, to go in your garage too.
LikeLike
“An understanding IT department” is crucial, but when you work in a large, massively silo’d organisation where the IT department is effectively a bunch of jobsworths’ who might as well be an extension of the Facilities department, it’s easier said than done. IT Helpdesk staff at my company think that the little exclamation mark on an Excel file means that the file is corrupt.
As such some of us have to make-do with the very limited resources available. VBA is a God-send under such circumstances.
LikeLike
You’re right and that’s a blog rant I’ve been saving for a rainy day. Something about IT’s job is to help everybody else do their job, not to tell them how to do it.
You might enjoy this one… VBA as an IT Trojan Horse
http://www.wallpaperingfog.co.uk/2012/02/why-vba-macros-got-everywhere.html
Things are changing slowly, but I’m having a fair bit of success at the moment, marketing products based on Python, Tableau and SQL. Mostly because analyst teams at our clients simply don’t have access to those tools.
LikeLike
Neil: “You might enjoy this one… VBA as an IT Trojan Horse
http://www.wallpaperingfog.co.uk/2012/02/why-vba-macros-got-everywhere.html”
You’re right I did enjoy that one. From my perspective, anything that makes programming Excel harder to do is a bad thing. The Microsoft view of the World seems to be that you have users, who don’t need a programming language, and IT people, who will use .Net. Those of us whose prime responsibility is not programming, but who need to be able to develop reasonably complex applications quickly don’t seem to be on the radar.
LikeLike
I just wish they would update it, and stop messing around with .net.
Or, do dot net in office properly
LikeLike
Or even both!
LikeLike
If Application.WorksheetFunction.Min(dblEffort) = Cdbl(Val(“EXCEL + VBA”)) Then strPayback = “MAX”
LikeLike
Public Sub VBAisBifrost()
Dim strTmp as String
Dim varBifrost as Variant
strTmp=”Word,Excel,PowerPoint,AutoCAD,SAP_OAPI,OpenStaad,Microstation,ADO_SQL,PDFCreater_API,Mathcad_API,AnyOtherAPI”
varBifrost = Split(strTmp,”,”)
Msgbox “You Can Be THOR!!”, vbInformation + vbOKOnly, “Engineer with Little Effort”
End Sub
LikeLike
Taiwan boss always use mininum manpower to execute the project. Engineers must do every thing by himself. If a rookie was without VBA, he almost die. For example, he must use OAPI to build the railway viaduct model and use VBA’s Shell function to run spColumn for pier design. Generating the WSD’s load combination for checking the each pile reaction under its’ capacity via Excel VBA, and making calculation of bdg, pier, pile cap and piles in batch processing. At the end, making draft drawings by using VBA to output ASCII DXF files of bdg profile, sections, pier sections to drawers.
Why we do this? because we just have 2.5 structural engineers, 2 geotechnical engineers, 1 alignment engineer and 3 drawers to do 6.6 kilometers railway viaduct in one year, and client always change his mind or saying he event don’t know what he want. Without VBA, engineer never
survive !!
LikeLike
Could IT department do it? you don’t think about it…
LikeLike
Some sensationalist comments on Excel and JP Morgan 2012 CIO Losses:
http://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2
LikeLike
“On top of not being tested correctly, the report states that the model suffered from some pretty standard Excel flaws”
Well no, the model seems to have suffered from some pretty standard ways in which Excel is mis-used.
If someone uses a hammer to break some wood, when they intended to knock in some nails, that is the fault of the person weilding the hammer, not the fault of the hammer.
LikeLike
Doug I think they feel more comfortable by not knowing how to properly structure an Excel model. I left some comments regarding to this case for my students: https://github.com/rnfermincota/IVEY/wiki/Three-comments-on-the-FI-article-A-tempest-in-a-spreadsheet–and-JP-Morgan's-129-page-report-on-the-$6-billion-trading-loss
LikeLike
Is Excel+VBA still worth learning, or has it had its day? If you’re a data analyst, then yes. Can’t comment of whether its worth it for programmers.
If MS keep extending it to cover new functionality that they add to excel, then it will remain a damn handy way for non programmers WITHOUT alternatives to program. And a great vehicle for non programmers to become programmers WITH alternatives, should they need them.
I hope MS DO keep extending it so that it remains tightly coupled to what Excel can do. Some updating is needed for sure. For instance, without VBA you can almost instantly filter a pivottable for a partial string match, using the search box they added in 2010 (maybe in 2007 too…can’t remember). But you can’t address this very cool functionality with VBA. Instead, you’ve got to iterate through a potentially VERY large list of pivotitems. This is damned frustrating, when users can do something manually in an instant that might take many minutes to do with VBA.
LikeLike
I have a question besides your post itself.
Who is Harlan Grove? Is he some famous person?
I found a very simple but not explainable functions which maybe his work.
I can find some comments everywhere wrote by himself. (around 2004~2007?)
People are referring as Harlan Grove PULL or LOOKUP calling it “old Harlan Grove technique”.
But there is no evidence I can find who to ask how it works.
LikeLike
Yes, he’s pretty famous in the world of spreadsheet forums.
His Pull function is a way of getting data from a closed workbook. I’m sure there are still plenty of places where you can find the code.
LikeLike