Why VBA macros got everywhere

Last week, I wrote a post that suggested Visual Basic for Applications (VBA) could be declining in importance for today's analysts. I do think that it is and that it will continue to do so, because it's not evolving to meet the needs of bigger data, or to compete with new and better ways of building dashboards.

VBA has got a valuable lesson to teach though. Never mind that it may now be feeling the pressure from new competitors, how on earth did a BASIC programming language that was bolted to the back of Microsoft Office - and particularly Excel - get to be so important in the first place?

I touched on one of the reasons in that previous post.

"it will let you do things that are otherwise the preserve of IT, which should be the ambition of any good analyst. If you need IT to sort data out for you, then you've failed."

VBA is a fantastic tool for empowering analysts to build their own solutions to problems. It gives analysts the power to create innovative new bits of kit without learning the sort of heavyweight programming, that is the preserve of full-time coders with computer science degrees. What analysts produce in VBA - and I speak from personal experience here - is quite often horrifying to their IT departments. Even very good code by analyst standards is a world away from the way that a good programmer might chose to solve a problem. For one thing, no programmer worth the name would have started their build in VBA.

The thing is, even with that coding deficiency, VBA works. It makes an awful lot of businesses run. (And along the way, it's completely hamstrung Microsoft with Excel upgrades, because it's too embedded in too many places to change it now.)

You could see sloppy programming as a failure on VBA's part; as evidence that all these macros should have been built by IT in a proper language, with version control and a detailed specification. I disagree. I'm an analyst, not an IT person and so I think what VBA did is amazing and we should be trying to repeat it.

VBA survived and it prospered. It did that because it met a need and it's a need that large companies in particular, go out of their way to avoid happening with other bits of software. Excel was the Trojan Horse that put IT capability into the hands of people who aren't supposed to have it. As VBA starts to show its age, we're in danger of drifting towards a world where centralized IT departments control access to data and access to the tools that can work with it.

There's no Trojan horse yet for Big Data. Sure, there's free software which can work with it but as a junior analyst, it's likely to be a struggle to persuade IT that they need to unlock the admin account on your PC so that you can install R, MySQL and FileZilla (and especially that you also need some space on an SQL server.) Getting software paid for will be even harder as the prevailing attitude is still one of, "you've got Office and that's all you need." The majority of IT departments never liked VBA in the first place, because macros that they didn't build crash and cause problems that the IT department is asked to fix. Never mind that a few macros which don't crash are saving hundreds of man hours per year in the finance department.

I should say at this point that I'm not trying to give IT procurement a kicking. What I do want to do is recognise that there are smaller, incredibly useful tools, which don't need a long IT build and which we can't always specify precisely at the start. They need to evolve and they need to be developed by the people who work with data and spend time with clients. By the people whose job it is recognise opportunities for data analysis and exploit them. VBA did that. As VBA ages, what's going to do it in future?

There are three ways that this could go. One is depressing and the other two are interesting.

First, the depressing one. The age of amateur coding within large companies could be coming to a close. I don't think this is all that likely as the benefits are too great, but we might be entering a phase where IT controls access to any kind of developer tools, before the pendulum swings back the other way. What will swing the pendulum back, is larger companies realising that they're taking a pasting from smaller and more agile competitors, where analysis teams are able to run with their ideas.

As a second possibility, somebody could develop the next VBA Trojan Horse. That somebody won't be Microsoft, which is unfortunate because in Office, they've still got the capability to deliver it. Microsoft currently seem most concerned with creating tools for centralised IT to use, which is why from an end-user point of view, all of Microsoft's BI tools are crap. If the Trojan Horse comes, I think it's more likely to be from a new developer delivering a platform that doesn't need admin rights to install on an analyst's PC. That platform could well be cloud based, which is awkward where data is highly confidential, but not an insurmountable problem. In the same way as for VBA, by the time people who are inclined to centralise IT processes work out what's going on, it will be too late.

The final possibility is by far and away my favourite and I think, also the most likely. We could finally recognise the benefits of giving all sorts of teams - not just analysts - some control over the software that they choose to use to do their job. When you think about it, the way we look at software currently is awfully nannying...

"Here's a PC, it's got Office and a web browser on it. That's what you get."

"I could do a much better job with a copy of xxx"

"Write a business case that costs more in terms of your time invested than just paying for the software would have, we'll think about it and get back to you in three months. Probably with a no."

Of course you still need some central control, but there are huge benefits to a flexible approach to software. On a factory production line, you use the tools you're given, but the companies we work in aren't a production line. A better analogy would be a construction site, where you have all sorts of skilled technicians doing different jobs and where you wouldn't dream of telling the carpenter that he can't use his choice of chisel, because it's non-standard.

The CIA recently hinted that it might be heading in a more flexible direction, when it told vendors that it wants to start paying on a metered "pay as you go" basis for its software. You'd do that so that you can install many and different pieces of software and pay for the good ones that end up being used a lot. You'd do it so that you don't have to enforce the same few tools across multiple departments doing different jobs.

We don't know how many analysts the CIA has or what its budget is, because both are classified, but some old guesses put it at around $27bn. The Twin Towers and quite a bit of inflation has happened since then, so I'd say it's a fair bet that we're looking at well above $30bn. That's a lot of analysts and a lot of software. Definitely worth keeping an eye on how they choose to do procurement.

If I were a software developer, I'd be looking for Trojan Horses to sneak my product past IT. Tableau Public is a nice idea, which aims to create critical mass from outside companies by letting bloggers use the software for free. It's not quite there though... Google Docs is probably closest to the cloud idea of not requiring an install and could be the future, but it's nowhere near mature enough for use by analysts. Just a good toy for the minute.

And for me, as once again a small cog in the enormous WPP wheel, I'm hoping that marketing can move to the more flexible software model outlined by the CIA. Back to the original argument, we should recognise what VBA does so well and look for ways to make it happen again. Give staff responsibility for knowing what tools they need and let them do their job even better.