For the last 10 years StackOverflow have surveyed developers to understand the state of the industry and uncover current trends. A staggering 65,000 people answered this year’s survey.
The survey tells us the most loved, dreaded and wanted languages. This year was very bad news for VBA. The results show it came bottom of the most loved and most wanted languages, and top of the most dreaded! This is a terrible but perhaps unsurprising result for VBA.
The results show the percentage of users of a language that have expressed an interest in continuing to use it. The survey found out of the people already using VBA, only 19% of those actually want to continue using it!
VBA has fallen out of favor because it hasn’t kept up with the modern demands of a programming language. It has hardly changed since it was first introduced in 1993. Other languages have continues to evolve and newer languages have appeared to dominate the landscape. Programming has changed a lot since 1993 and VBA has failed to keep pace.
Where VBA still has a foothold is in Excel. Virtually all businesses use Excel to a greater or lesser extent on a day to day basis. From accounting and finance to sales and marketing, Excel is an essential tool. That’s not looking like changing any time soon. Writing VBA macros in Excel is many peoples first introduction to programming because it is so accessible, but that the StackOverflow survey shows it is not a loved development language.
VBA is not the only language you can use with Excel. Excel functions, macros and anything you can do in VBA can also be done in other languages! This may come as a surprise to many Excel users, but I hope a pleasant surprise! It means there’s a path from the most dreaded language to some of the most loved, all while still working in Excel.
Python as an alternative to VBA
Consistently over the last few years, Python has been one of the most loved languages. Python came 3rd in this years survey behind Rust and TypeScript. That is impressive in itself, but when you consider Rust is primarily a systems language and TypeScript is a mostly a web language, Python stands out as the most popular language suited for data wrangling and analysis.
Did you know that Python can be used as a complete replacement for VBA in Excel? Everything you can do in VBA, including macros and custom worksheet functions, can easily be done using Python – with the added advantage of Python’s extensive 3rd party packages (eg. pandas for data science, pytorch and tensorflow for machine learning, scipy for scientific computing and much much more).
PyXLL lets you write Excel add-ins in Python. If you are using VBA because you think that’s your only choice, think again! To help VBA developers get started with learning Python we prepared a free e-book. If you are interested in graduating to Python then this is an excellent place to start.
Looking again at the list of most loved languages, there are even more in that list that can be used with Excel as a VBA replacement. If you are using VBA only because it’s what you use in Excel then you have plenty of other great choices besides Python.
Kotlin and the other JVM languages
Kotlin has appeared as the 4th most loved language. It’s highly regarded because of its concise, readable syntax but with the benefits of running on the same platform and being able to seamlessly interoperate with Java.
The JVM languages all run on the Java Virtual Machine. The JVM has long been popular in the enterprise with its original Java language. These days Java is still popular but the ecosystem has extended and more JVM languages have appeared. Kotlin, Scala and Clojure are a few of the more popular languages (as well as Java!) that run on the JVM.
Kotlin and all of the other JVM languages can also be used to write Excel add-ins instead of VBA. This is good news for anyone that’s invested time and effort building out analytics or solutions in Java or the other JVM languages. It’s virtually effortless to expose these to Excel users.
Jinx enables you to write Excel add-ins in Java or any other JVM language. For a tutorial on how to write an Excel add-in in Kotlin, see Creating a Kotlin Excel Add-in from the user guide. There are similar articles for Java, Scala and Clojure.
There is perhaps more of a learning curve to Kotlin than Python. Kotlin is a great language and it really isn’t hard to get started with, but many of the tutorials online assume some familiarity with command line tools. Using a decent IDE (Integrated Developer Environment) like IntelliJ or Eclipse will definitely be a help!
C# and Excel-DNA
C# was Microsoft’s language introduced to tackle some of the perceived problems with Java. It evolved quickly and has built up a huge amount of support from Windows developers. With .NET Core Microsoft are taking C# to macOS and Linux and expanding the possibilities of this language.
Like the other languages we’ve seen so far, you can write Excel add-ins in C#! Excel-DNA is an independent project to integrate .NET into Excel, allowing C# developers to write Excel add-ins instead of VBA.
If you are coming from VBA then C# might not be as easy to get started with compared to Python, you will benefit from better performance and maintainability using C# over VBA. If your department or team are already using C# or .NET then it could be a no-brainer!
Conclusions
VBA is the least loved, most dreaded programming language, according to the StackOverflow survey. If you are still using VBA because you work in Excel and think you have no alternative, think again!
For current VBA developers transitioning Python will make you a better, more productive developer and who knows, maybe even a littler happier! You can continue to use all the great parts of Excel that allow you to do the work you need. With an Excel add-in written in Python you can write all the worksheet functions and macros you need in a vastly superior way than you can using VBA. You will also find the performance of your spreadsheets can be massively improved resulting in less time waiting for calculations to finish.
If you are already a developer working in another language and you avoid Excel because of VBA, you are not alone. Fear and loathing of Excel among the developer community is a real thing! Learn to embrace Excel by using whatever language you are most productive in to write Excel add-ins – it’s easier than you think!
References
- Stack Overflow Survey
- PyXLL, for writing Excel add-ins in Python
- Python Guide for VBA Developers
- Jinx, for writing Excel add-ins in Java/Kotlin/Scala etc.
- Kotlin Excel add-in tutorial
- Excel-DNA for C# and .NET Excel add-ins