Skip VBA, use JavaScript for spreadsheets

Spreadsheets are ubiquitous, they're used everywhere and probably the most beloved of them all is Microsoft's Excel. Spreadsheets have been a killer app for the last 40 years. Even before Excel, VisiCalc was the killer app that helped sell Apple II's like hot-cakes. It's no wonder that spreadsheets are used everywhere and the basics of them are even tought in highschools.

At one point, if you do any sort of complex analysis and want to visualize the data, you may end up throwing the data into an Excel spreadsheet and run some analysis. There will be a point where you have data that regularly comes in: data that needs to be appended to a sheet, or some regular data set that comes out that needs to be imported, processed and plotted. When the work becomes repetitive, at some point you will look at automating that process.

What comes with the territory with Excel is the VBA programming language. I've personally learned and used VBA at my first jobs and migrated away from VBA because of its limitations. The language is quite slow, the syntax is clunky and installing packaged libraries is a pain. There are also been polls ranking VBA as the most hated language out there! If that isn't enough of a deterrent then, I don't know what would be!

There are alternatives of course to VBA. If you're a Python programmer then you could use xlwings which is an automation tool for excel. But if you're looking for a native solution without the need for the bulk of needing to add third party integrations for integrations, I would recommend dropping Excel for Google Sheets and learn JavaScript

There are several reasons that I'd make this recommendation.

Scripting for Google Sheets is done in JavaScript

While I would not make the argument that JavaScript is the best (or worst) language out there, I would say that at least it's not the most hated and that the language has a large ecosystem of libraries that are easy to install and use with the language. In fact, at the time of this writing there are over 1.3 million packages for that can be used with the npm package manager – if there is something you need to do, download the libary and be miles ahead instead of coding it from scratch.

JavaScript is a widely used language

JavaScript is the same language that powers nearly all of the dynamic content you see on webpages. As a result of that, over half of all developers in the world will have used JavaScript at one point in their career. If you want to learn a reusable skill, this should be a great reason to do so as you'll also be able to use the same fundamentals code dynamic webpages and backend services should you ever want to.

You can use a modern Integrated Development Environments (IDE) for coding

Modern development evironments are powerful; in that they provide a swath of functionality that allow you to code faster. VSCode has recently become one of the most popular IDEs out there and functionalities like auto import, auto formatting, debugging and more are hughely useful in helping people that write code for their analysis to be far more productive. Google allows code to be pushed and pulled out of Google App Scripts via a tool called clasp. Being able to write and execute code outside of spreadhseet context can also provide the added benefit of writing unit tests that allow you to check that your code is behaving as expected instead of running and hoping that things will work – this is especially useful when doing large projects and in addition since the code can live outside of a spreadsheet you'll also be able to check it in to a version control system such as git.

The google App Script framework allows code that you've written to be stored in modules

This is a great way to modularlize code and allow it to be centrally managed in a single module instead of having copies of spreadhseets with different versions of your written scripts that would need to be updated one by one – maintaining code and traking things would simply be impossible and many organizations institutions that have worked in this manner have been bitten really hard when data sources change and spreadsheets suddenly break. With the App Script framework you could use clasp to update your module to a new version and users down the line could update the module used just by selecting the newest version – this could also be managed by setting sheets to just track the latest version and all of this could just be automatic too.

There are a variety of other reasons out there that I would recommend using JavaScript with Google Sheets. My recommendation is to give this a try and see how it goes. If you have challenges, feel free to reach out!