Why VBA is the ideal introductory language

I still remember my time at a higher level technical school in Austria from 2009 to 2014. My first programming language was C, followed by Java and Assembler and later C# at university. Today, block based programming languages such as Scratch are taught from an early age at school in both Austria and the UK. However, classic text based programming languages like Python are still taught in later years, as they are more powerful but also more demanding. A quantitative study from 2017 showed that Java was the most popular programming language at UK universities, followed by Python and the C family (C, C++ and C#), although the trend at that time was already moving towards Python. The situation was similar globally.

I learnt a lot with the mentioned programming languages; I learnt paradigms, data types, functions, instructions, I got to know the compiler. However, I never needed any of these programming languages in my professional environment as a data analyst alongside my studies in corporate groups. What I really needed was Visual Basic for Applications (VBA) in industrial companies — even today as a project manager. Not always voluntarily, but often it was the only solution to automate digital processes. In this article, you can read why I think VBA is a very suitable first text based programming language.

A little theory about VBA

VBA is a scripting language (because it is interpreted and not compiled) like Python, which has been included in the Microsoft (MS) Office suite since the mid 1990s and is derived from the BASIC programming language. Although VBA is primarily an event driven, object orientated programming paradigm, it also supports procedural and structured programming. Classes and objects can be created in VBA, which is typical of object orientated programming (OOP). The only thing VBA lacks to become a fully fledged OOP language is one of the four pillars of OOP, inheritance. However, since classes and objects can be created in VBA, basic OOP paradigms can be taught. In comparisons of scripting languages, VBA is often not even mentioned; in the PopularitY of Programming Language (PYPL) Index, which compares the popularity of programming languages internationally, VBA has a share of less than 1%. And yet the scripting language is extremely important within companies — so important, in fact, that Microsoft promptly reversed a deactivation by default of VBA in Microsoft 365 in 2022.

Advantages of VBA

From experience in corporate groups, I can say that software restriction policies in companies are often very restrictive, meaning that no additional software, including integrated development environments (IDEs), can be installed. Ironically, even the successor to VBA, Office Scripts, is often blocked to prevent malicious code. Although VBA macros can also be blocked via Group Policy Object (GPO), experience has shown that this is rarely done in most companies, because since VBA is often the only option for software automation too many scripts are based on it. This is due to the fact that VBA is available on every company computer with MS Office installed. And there are a lot of them: namely 85% of computers in companies in Germany and 80% in the USA. Likewise, 80% of Fortune 500 companies (the 500 companies with the highest turnover in the world) use the latest version, Microsoft 365. MS Office is also used privately in many places — albeit not always the latest version. In 2018, 1.2 billion people worldwide used an MS Office product or service, which makes it the most popular office software. With a share of almost 10%, the UK is the second largest Microsoft 365 customer after the United States.

For you

Be part of something bigger, join BCS, The Chartered Institute for IT.

All of the more than one billion customers are potential VBA developers, as they have already installed the IDE for VBA. The most popular programming language in the world, Python, only appeals to 8.2 million developers in comparison. In my opinion, this availability is a huge advantage over conventional programming languages in terms of accessibility; VBA can be tried out by simply activating the developer options without having to install one of many IDEs, packages or similar.

MS Excel and Word have a macro recorder, which can be used to convert actions directly into code. As changes are immediately visible, the learning effect is enormous. This quick option is particularly useful if you are not sure whether programming is for you. It is also not necessary to find a suitable IDE by comparison, as this is already installed — namely the Visual Basic Editor (VBE).

The question now is, what can VBA be used for? In my opinion, another advantage of the scripting language is that it can be used for everyday problems on the home computer or in the company — unlike C, for example. During my time as a data analyst, I used it to analyse incoming emails in Outlook and trigger processes fully automatically, to automate monthly reports in PowerPoint, to automate virtually every repetitive activity in Excel and to automate repetitive input masks for our SAP users using VBA. SAP has graphical user interface (GUI) automation using Visual Basic Script (VBS), but VBS is blocked as a directly executable file on most company PCs. However, as VBS and VBA are largely identical, the VBS code can simply be copied into an Excel workbook and executed from there.

Disadvantages of VBA

Of course, VBA also has some disadvantages. One major disadvantage from a performance point of view is that VBA only supports single threading due to its age. This problem can be circumvented, but only in very cumbersome ways. Another problem, which is why Microsoft would also like to get rid of VBA, is the security aspect, as it can be used to execute malware. Although it is often clear from the file extension (XLSM, PPTM, DOCM etcetera) that a macro may be present in the file, it is not clear which macro it contains. Furthermore, though this may still be clear to many users, for fewer is it clear that XLS, XLSB and many other file extensions can also contain macros and therefore potentially malware. Other disadvantages are that VBA scripts do not support Excel on the web and Power Automate.

Conclusion

Fully grown programming languages such as Python are suitable for everyday use, but often cannot be used in companies by non-developers and are in my opinion much harder to learn than VBA. I don't want to deny Python's status as the most popular programming language — I really like using Python myself.

However, the simple syntax, easy accessibility and relevance in companies of VBA not only make the scripting language very easy to get started with, but also offer real added value. I particularly like the idea that novice programmers can use the macro recorder to create, understand and improve code while familiarising themselves with the most popular Office software. To address the disadvantages of VBA, the security concerns do not detract from VBA for a learner, as they use their own code in their own environment, which is safe. Single thread performance is also not a problem, especially for smaller programmes at the beginning. To summarise, in my opinion VBA is a very good introduction to the world of text-based programming.