Learn Excel VBA? Top Reasons Why You Should

In November 2015, a user suggested Python replace Excel VBA as the Excel scripting language in response to an Excel UserVoice (Excel’s official feedback hub) query. Within a month, the request had the most votes on the hub, prompting Microsoft’s Excel team to set up a survey. The survey received over 10,000 responses by February 2018. Microsoft is yet to update on a decision. So, how does this affect your desire to learn Excel VBA?

We can all agree that this state of affairs isn’t the best for anyone or anything. And if that were all there was to Excel VBA’s tale, no one would bother learning or using it. But, as you’ll soon see, there is more to it than meets the eye, and there are several terrific reasons to learn Excel VBA today.

As with most things in life, there’s a downside to consider with Excel VBA. In this article, we’ll explore both sides of the Excel VBA coin. Though this article is robust and factual, it is by no means exhaustive. So, I look forward to your opinions and questions to further nourish the discussion.

The Case for Learning Excel VBA

Reports of VBA’s demise have been greatly exaggerated

Countless articles have declared VBA dead in the decade since 2008. Why 2008? Well, that’s when Microsoft dropped support for VB6 IDE. Microsoft also stopped licensing VBA to new vendors in 2007. But is VBA really dead? Should you take the time to learn Excel VBA? Well, a certain famous musician once said, “Men lie, women lie, numbers don’t.” So, let’s run the numbers, shall we?

There are many published indicators evaluating the status of programming languages. In this article, I’ve considered the most relevant and reliable indices for obvious reasons. It also helps to be concise, after all, we are all busy people. So, I’ve examined the TIOBE Index, the PYPL Index, and Stack Overflow Trends.

The TIOBE Index is arguably the most popular indicator. It rates languages by the number of hits they have on the 25 most popular Alexa search engines. TIOBE runs the query “+<langauge> programming” on the likes of Google, Baidu, Yahoo, Bing, and Amazon. The VB group (Visual Basic, VB, VB6, and VBA – excluding .NET) ranked 20th in January 2019. That was its lowest rank since 2001 and represents an 11-spot drop since its highest position of 9th in April 2015. The group ranked right below Ruby and SQL, and above Groovy and Kotlin (which was adopted by Google in 2017). That’s impressive since Microsoft dropped support for these languages a decade ago.

The PYPL Index rates languages based on the number of hits they have on Google Trends. It runs a “<language> tutorial” query which gives a more honest look at a language’s popularity and growth trends. VBA is not grouped with VB and VB6 in the PYPL index. The PYPL Index’s chart for VB/VB6 and VBA (see below) is quite interesting. While VB/VB6 declined, interest in VBA largely stayed the same since 2005. If anything, there were spikes in VBA interest in some years. So, by inference, the apparent negative VBA trend in the TIOBE Index rating was more likely due to VB/VB6’s decline.

The PYPL index suggests that the apparent negative VBA trend in the TIOBE Index rating was more likely due to VB and VB6’s decline.

Popular programming Q&A website, Stack Overflow, tracks users’ question views and language tags. You probably use this site regularly as I do. The website provides language trend summaries based on data collated since its founding. Below is a screenshot of the trend in VBA and VB/VB6 tags used in questions since 2008. The chart shows a greater digression between VBA and VB/VB6, relative to the PYPL Index. It goes beyond the PYPL Index and suggests a significant increase in VBA usage in recent years.

The above facts contradict the declarations that “VBA is dead.” So, why is that the prevailing sentiment? Perhaps Microsoft dropping VB/VB6’s support and new VBA licensing a decade ago are to blame. Another plausible reason is the dislike many programmers have for VBA. It isn’t uncommon for people to say VBA isn’t a real programming language. My evidence for this? Check out the Stack Overflow Developer Story chart below. It shows that VBA was the only heavily disliked language that actually grew, in the two years reviewed. Curious stuff, indeed!

Whatever the reasons for the predominant VBA doom and gloom sentiment, they lack a factual basis. VBA’s use and learning are growing at best or holding steady at worst. Excel VBA is guilty by association. The data says you should definitely take the time to learn Excel VBA.

I don’t care if VBA is dead. It still works for me now, I’m very effective with it, and I’m still solving real problems using it every day. If it’s dead, it’s the best damn corpse in the office.

Dick Kusleika (2014)

People use what is available, and Excel VBA is available to most

In 2016, Microsoft revealed there were about 1.2 billion Office users globally. That number included around 750 million Excel users. Also, about 83% of desktop computers run Windows OS, and most have Office installed. Also, each Office application has VBA embedded. These facts give VBA one of its greatest strengths; near-ubiquity.

Worldwide, people need to manipulate, analyze, and present large data sets efficiently. Naturally, people use what they have instead of struggling to get something else. When did you last purchase or download a new Text Editor or Spreadsheet application? See! Also, other tools aren’t always accessible. If you work (or have worked) at a firm with an IT department or tight budgets, you know what I am talking about. IT restrictions and budget hassles can kill your creative vibe, cause stress, and waste valuable time — the things you wanted to eliminate in the first place.

So, should you learn Excel VBA? Well, it’s a big deal that it runs in a platform (Excel) used by about 10% of people on the planet. Excel is a platform that is accessible on most people’s personal and IT-protected work computers. Also, people are comfortable using Excel VBA applications as the GUI is familiar to them.

Many large firms rely on Excel VBA for quality and productivity

Excel VBA is prevalent in the finance sector. There, Excel VBA is often used to generate and support complex financial spreadsheets. By automating Excel’s in-built statistical features, analysts create dynamic forecasts. Many financial firms have built intricate systems with Excel VBA since its launch. Drastic changes to Excel VBA will affect these businesses significantly.

Excel VBA is commonplace in other industries besides Finance. As an experienced engineer, I can attest to how useful Excel VBA is in processing large data sets. Advanced engineering analysis software generates such data sets. Finite element analysis (FEA) tools like SAGE Profile 3D and CAESAR II are good examples. These tools output otherwise cumbersome data, Excel VBA helps handle.

These are just two industries where Excel VBA excels (pun intended). I am sure you know several others, do let me know in the comments. Excel VBA developers are always needed to support and improve proprietary applications in such firms. And they remain vital for the continued success of these high productivity businesses. This a superb reason to learn Excel VBA today.

Low entry barrier and highly transferable skills

VBA is not as difficult to learn and master as other programming languages. This is down to its friendly syntax, clean editor layout, and macro recording feature. There’s also an extensive online community of VBA developers, so you always have help when in a jam. Though each Office application comes with its unique VBA, they all share a core of standard tools. So, learning VBA in Excel is fast, and you can easily transfer your skills to any of the other Office applications.

VBA improves logical thinking and frees up your time

This is the VBA benefit I most appreciate because time is the most expensive thing we can’t buy. Rational thinking is necessary to build effective and efficient programs in any language. Devising algorithms for automating tasks always involves a thorough deconstruction of the problem. This stimulates your thought process. Once you’ve automated the often cumbersome and repetitive work, you get more free time. More time for analyzing new tasks, improving existing solutions, or placing a quick call to your loved ones! This applies to Excel VBA.

Future support for the massive number of legacy VBA code

If another programming language replaces VBA, then the legacy code pain would sting like a bee! Massive numbers of existing VBA programs will need rewriting. And this task would be difficult without an understanding of the legacy VBA code. So, even in this glum scenario having VBA skills would still present opportunities.

Although our first joy of programming may have been intense, the misery of dealing with legacy code is often sufficient to extinguish that flame.

Michael Feathers (2013)

The Case against Learning Excel VBA

Unfortunately, it’s not all rosy using Excel VBA. There are a few shortcomings that you need to be aware of. There are workarounds for most and their severity depends on your unique requirements.

Host Application Dependence

An obvious downside of VBA is that it can’t work outside its host application. Translation? VBA can’t build standalone applications. Moreover, host applications are regularly updated increasing the risk of broken VBA code. Deprecated or adapted features sometimes lead to code breaks.

Comparative Execution Speed

VBA runs slower than compiled languages (e.g. C, C++, VB). This stems from VBA being a combined compiled-interpreted language. So, VBA code first compiles to an intermediate form called Microsoft P-Code. Finally, the host application’s virtual machine parses, interprets, and runs the P-Code. But, in compiled languages, compilation produces native code which processors run. So, we can see how VBA would be slower.

Lack of Automated Unit Testing Features

VBA has no built-in automated testing features, only basic debugging tools. Now, testing ensures the proper software behaviour pre-release. So, rigorous testing is a no-brainer in the development cycle. Testing is either manual or automatic. Manual testing of lines of code (unit testing) is often tedious and repetitive. But, automated testing, using test scripts run by testing tools, is quick and versatile. While most modern programming languages have in-built automated testing frameworks, VBA does not.

Difficulty with Web Connectivity Technologies (JSON & XML)

VBA struggles with today’s prominent web (server) connectivity technologies. Those being JSON and XML. Today, most web APIs exchange data with requesting applications in these formats. Twitter’s API is one famous example. VBA does work with JSON and XML, but it takes a lot more effort compared to high-level languages like Java or Python.

The ability of discerning high quality unavoidably implies the ability of identifying shortcomings.

Edsger Dijkstra (1987)

The VBA Case Verdict

VBA’s purpose is to customize the Microsoft Office, and other VBA-enabled, applications. VBA was not intended for building standalone applications. In pondering VBA’s benefits and shortfalls, you should bear that in mind. Coupling that intent with the arguments for and against, I think you’ll agree that learning VBA is well worth the effort. Just make sure to follow the Excel VBA coding best practices on your journey to pro!