There are so many goddamn ways to program in Excel

So I'm both a software engineer with ~5 years of experience and a new student pursuing my Bachelor's in Accounting at WGU (online school). I've just started taking an Excel course, but I've been interested in Excel (and spreadsheets more generally) for a few years. I recently built a prototype spreadsheet application with static typing, testing built in, and other weirdness (not what this article is about).

I just wanted to make a list of all the ways you can do programming in Excel, since I'll likely use it a lot more in the future as I apply to jobs that combine my accounting and software engineering skillsets. Here's a short list I'm pretty sure is incomplete:

• The formula language. This is more powerful than you think, with spilling a.k.a returning values into multiple cells with a single formula, the LET function providing locally scoped named values for multi-step formulas (a bit like with in some languages), and the LAMBDA function which allows for creating custom functions! You create a lambda as a "named item" to do the equivalent of declaring a reusable function.
• Macros. These macros can be "recorded" by hitting the record button and clicking around or manually written in Visual Basic for Applications (VBA). VBA is pretty powerful. You can write scripts that launch any microsoft office suite application and control it programmatically.
• TypeScript. Microsoft has created "Office Script" as effectively a direct rip off of Google Apps Script (I'm not actually sure which came first), allowing users to write scripts in TypeScript that execute in the life cycle of their Office 365 applications and have access to a set of useful APIs. I think this is considered a better alternative to VBA now, because it can run for the native apps and in the browser (Microsoft really wants you to lock into 365).
• Python. Microsoft has added an easy "write Python formula" button to create cell formulas with Python instead of the native formula language. So much of the data analytics world is built on Python, this one kinda makes sense to me.
• Add-ins. These are truly interesting. Excel has a standard plugin architecture that consists of hosting your own web application and then providing a configuration file specifying what menu items to add and what events to register with those UI components? I haven't made one, so that architecture is still a bit unclear to me. Add-ins are apparently split into two categories, COM (Component Object Model) and Automation add-ins, with the key difference being that Automation add ins can provide functions (like LAMBDA, but not limited to what the formula language can already do).

There's an inversion happening here that I find quite fascinating. Here's my theory:

Most software is useful to a point. When our requirements out pace the software's capabilities, we're forced to adopt a different or more custom approach, which can be costly. The magnitude of Excel's ubiquity is such that it reversed the gravitational pull of increasing complexity. Rather than graduating from it's functionality to more custom solutions, we're more likely to shove more custom solutions back into Excel.

I genuinely do not know if this is good or bad.

I'm all for the democratization of software engineering and programming more generally. I also kind of love the idea of never having to make a custom UI again (I'm really more of a backend guy). But there is definitely a cost to building up large codebases inside Excel.

Version control, peer review, testing, and other developer tooling are deeply hobbled, if not missing altogether, when writing code in any of these ways. That's partially by design. You might not want an accountant to have to learn and care about testing practices. But if the code they're writing deals with your company's finances, IT SHOULD MAYBE PROBABLY BE TESTED A LITTLE.

I'm not trying to pick on Excel, as this is a problem more generally with any system aimed at "citizen developers". Many systems provide ways to script / create plugins, but do so in a way that makes it extremely difficult to follow best practices in the name of keeping things simple (we don't want the accountant to have to know anything but accounting). But I think this might just be a different flavor of gate keeping. The general concept of creating tests is not that complicated, and if we wanted to empower accountants to write quality code, we would prioritize providing built in ways to create tests in the systems they use.

I think if we genuinely want to avoid gate keeping software engineering, then we need to do the work to give "citizen developers" the same quality tooling and the same responsibilities as "real software engineers".

I'll try and let you know how I'm feeling when I finish the course, but every time I write a formula and then drag it down to copy that code 50 times, slightly altered by the relative cell references, and don't write any kind of test, I die a little inside.