VBA is a coding language used by millions of people across the world to automate tasks in Microsoft Office products. It’s a language that has been around for decades and is one of the easiest coding languages to learn if you don’t have a computer science background. With its endless popularity in such programs as Excel, it might come as a surprise that there is this lingering fear VBA is going to be removed from Microsoft Office in the near future. I constantly see questions on forums asking “is it worth learning VBA at this point in time?” or “when will VBA stop working?” or better yet, “what is replacing VBA?”.
Don’t get me wrong, these are all valid questions; especially with the way Microsoft has treated VBA over the past 10 years. But there is hope and through this article, I will present my thoughts and predictions about the so-called “inevitable death” of the VBA coding language.
To simply put it, it’s something that virtually anyone can teach themselves with very little effort.
Now before I get too far, let’s back up and talk about me for a minute. I, Chris Newman, founder of TheSpreadsheetGuru, am first and foremost a finance guy. Why is this important to clarify? Well, I want to stress I know nothing about computers, computer coding, or how it is physically possible to send information like this article through the air and into millions (ok, hundreds…) of digital devices to be read. Needless to say I am not the poster child to be somewhat authoritatively writing about a computer coding language.
But I am. How? Because VBA is something that makes sense to anyone familiar with Excel (or Word or PowerPoint). There’s no weird syntax like curly brackets or a complex lexicon of phrases to memorize, you simply write what you want to do and it is done.
But the real beauty of VBA and it’s ease of learning is the availability of the Macro Recorder . This feature allows you to click a record button and it will write code for every single thing you physically carry out on your screen. While it might not be the most efficient way to write the code, it writes it in a way that in most cases automates your process without any further modifications. Teaching yourself how to automate specific tasks can be as easy as using the recorder and copying the code it spits out.
Let’s get back to the question in the header, “Why learn VBA today?”
Have you ever wished you could build your own phone app or website or even a robot to clean your house? I know I have and it’s somewhat depressing because unless you have a ton of money to pay someone to build your dream invention or are ridiculously smart, you’re never going to be able to build those things. I remember back to when I was an ambitious 22-year-old, fresh out of college and ready to conquer the world. Smartphones apps were in their infancy and I had an idea or two that was going to make me a millionaire. Lucky for me, Apple had a huge following and tons of educational content to help newbies like me learn to make apps. To make a short story even shorter, after a month of trying my hardest, I had gotten nowhere. There was simply too much to learn, and even basic coding concepts were not making any sense to me. I sadly had to scratch making my million dollar app from my “To Do” list.
Fast forward a year, and I’m starting to dive into VBA. With just a few lines of code, I am manipulating thousands of lines of data within fractions of seconds. I start to get fancy and make interfaces with buttons and sliders. I figure out how to pull data from the internet. I finally come to the realization that I can easily figure out how to do virtually anything I want with VBA and Excel and my whole world changed.
I’m assuming many of you VBA coders out there have a similar story where the abilities you discovered via VBA brought your wildest dreams to fruition. I’ve seen some many creative solutions out there utilizing VBA code. There are examples of using VBA to build games with opponent AI, animated charting, and business management interfaces. You can truly do amazing things with this simple language!
Learning how to use VBA is a very quick way to differentiate yourself from the millions of other Excel users throughout the world. Excel users instantaneously associate folks who utilize VBA as an advanced user and will look to them for help. Chances are, if you were able to teach yourself VBA, you are pretty good at searching for Excel help and you more than likely can provide solutions to others quickly even if you don’t know the solution off the top of your head (thank you Google!).
VBA is a language that is been around for a long time and because of this, there is a tremendous amount of documentation and help available to us. I am still amazed at the response time in Excel forums such as Mr Excel where you can literally get help/solutions provided to you within minutes (yes I said minutes) of posting a well-written question. Forum help was a major source of education for myself as I taught myself VBA or explored for ideas on how to tackle an automation I wanted to implement. The Excel VBA community is absolutely one of the most generous and responsive online communities out there and it is because of this, that folks are able to take advantage of this language everyday!
So now that we know why Excel users have reason to learn and use VBA, what are Microsoft’s current thoughts on the language? With VBA’s popularity among the user-base, it seems like a no-brainer that they would want to see it’s capabilities and uses grow, right?
Let’s start off by clearing the air…Microsoft doesn’t hate VBA, it’s just not as sexy as it used to be. In fact, many of the Excel developers I have talked with love VBA and use it quite frequently. But there does seem to be this communal movement inside Microsoft not to touch anything VBA unless (1) it crashes Excel or (2) a LARGE customer has a specific issue with something that used to work and now doesn’t. Proposals for new capabilities, efficiencies, and more modern functionality have all been seemingly ignored in the hope that we the users will just get tired of asking. This period of silence over the last 10 odd years has driven a lot of confusion among Excel users as to if the VBA language is even supported anymore.
Microsoft Developers have told me repeatedly that their current policy is to only maintain VBA’s object model when new objects are created so that users who want to automate creating some of the newly implemented charts like the Waterfall graph can do so. Only there’s a BIG problem! While the developers are giving us users access to new objects (think shapes, charts, tables, cells, etc…), they aren’t ensuring all the properties work properly or in some cases function at all!
Lets circle-back to those shiny new Waterfall charts Microsoft released into Excel a couple of years back. I worked on a consulting project where I was building an Excel add-in that would automatically create and format of a variety of different charts. For example, the client wanted a bar chart with certain coloring and font formats. I used the macro recorder, re-created the chart in Excel to their desired specifications and I was off to the races whipping up a nice clean macro to automate the chart creation.
Now it’s time for the Waterfall Chart. I turn on the macro recorder, build the Waterfall to the exact specs, and guess what shows up in my Visual Basic Editor? Well, the written code started off with the creation of the waterfall (which was good!), but then it just recorded all my selections. There was absolutely no formatting associated with the code!
Well…..any experienced VBA enthusiast knows some things don’t get picked up with the recorder, so I use my overall VBA knowledge to start scribing the formats from scratch. I finish, run my compiler to check everything is in order and it checks out OK. Great! Now to the all-important testing phase. I run my beautiful waterfall-creating code and my chart doesn’t look right. I notice the font color didn’t change. The Axis title wasn’t manipulated the way I wanted. The chart border was still showing! To my embarrassment, I had to tell the client some of their formatting requirements for the waterfall chart would still need to be done manually because it simply wasn’t possible (they loved that answer….).
Yes, that was kind of a long (and painful to relive) story, but I foresee many examples like the one I described occurring in the future as Microsoft continues to roll out more and more new features. It’s a shame because with VBA and Excel combined you create truly amazing functionalities and with what Excel has in its pipeline, it will be very painful thinking it should be easy to automate something and two hours later realize the functionality is simply not possible.
So if Microsoft has plans to only minimally maintain VBA, what basket are they putting all their eggs into? Let’s explore that in the next section.
OK, here’s the section you all have been waiting for. When is VBA going to go away? In my opinion, it will NEVER fully go away, but if I had to put a number to its remaining lifespan of relevance, I would say at least 15 years (that’s around 2035).
How can I be so confident it won’t be going away anytime soon? Because (1) too many companies rely on VBA to automate processes and (2) too many Excel users have invested time in learning VBA to go up and drop it and learn something completely different.
Let’s talk about company investments. Many companies both large and small rely heavily on VBA to automate tasks. I personally work with companies throughout the year implementing VBA solutions to automate tasks, making quite a big investment into the implementation of VBA. This can be anything from cleaning up data to customizing model protection, to driving a company-specific add-in. There’s just simply too much money/time invested into VBA automation throughout the world for Microsoft to fully get rid of it.
Let’s now talk about the VBA user-base. Like I mentioned earlier in this article, most VBA code writers are not experts in code writing. The majority of VBA users are Finance, HR, Marketing, or Project Management professionals. These are professionals who were able to teach themselves how to write and implement automating scripts to make their lives easier. These folks are not full-time coders who are used to (or dare I say enjoy) learning new languages all the time. The majority of VBA coders were able to teach themselves because the language made sense to them and are not going to be very motivated to learn a whole new language in their spare time, especially with so little information/tutorials available to them currently. Hence, this population (the majority) of VBA users are more than likely going to stay within the bounds of what they know throughout their careers.
For those of you who didn’t have the time to enjoy my long-winded rant, here’s the gist of my thoughts on the future of VBA:
VBA will be a widely used automation solution for the next 10-15 years
VBA will never completely go away because too many companies have invested in it
VBA is still something that should be learned and can easily differentiate you from other Excel users
So don’t give up on VBA just yet! I know I won’t be any time soon :)