Why would you code in Excel?4 min read

A few months ago I discovered that, indeed, you can do programming in Microsoft Excel – by itself, a really controversial statement in the coding community. Long story short, I tried working in VBA and here are my thoughts on it.

If you have ever tried oranges after brushing your teeth it perfectly describes my overall impression of using Microsoft Excel to write a “program”. It looks like an orange and the texture is right but the taste is just wrong.

Let me explain how I got to this conclusion…

Introduction

For this week’s project, I wanted to experiment with the math behind a popular gambling game called “Crash”. It was a convenient occasion to try out VBA development and give a “young blood’s” opinion about it. You can check out the result here.

The Crash Game is a popular gambling game in the crypto and gaming community. The rules are simple:

• The player makes a bet and starts the game in which a multiplier increases as time goes on. 
• If the player chooses at any time during the game to cash out, the multiplier is applied to the amount of the bet and added back to the player’s wallet. 
• However, if the player fails to cash out before the multiplier reaches a randomly pre-generated value, the bet amount is lost and the round ends.

The game can have one or more players betting at the same time because it’s a player vs house game. With these out of the way let’s see how VBA holds its ground today.

Coding environment

Okay, everyone knows how Excel looks but it got more there than meets the eye. Going into Files > Options (at the bottom of the sidebar) > Customize Ribbon you can find the Developer tab in the Main Tabs window. Ticking the box unlocks the Macros menu that is used to access the development environment.

In the new tab, you can find the “Macros” menu where you can create, delete and edit macros. If you choose to edit a macro, it opens another window for Visual Basic for Applications.

The environment, to my surprise, looks alright. It is much better than Pascal for example but can’t compete with a modern IDE. There’s nothing to complain about but also nothing to write home about. A bland environment reminding me of an old version of Eclipse.

The graphics are rudimentary of course, I wouldn’t expect more from a spreadsheet environment. Nonetheless, with a python graphics library, it would have taken me much more time to create what in Excel is a piece of cake. You can conveniently hide most of the annoying tools and gridlines in the “View” tab. Here is a peek.

P.S. To my huge disappointment, there is no Dark Mode as a color scheme option!

Coding style and syntax

The syntax, like the archaic sounding name Visual Basic for Applications, gives away the 34 years the language has. There are only a few primitive data types available that are declared like this:

Dim instantCrashCondition As Integer
Dim crashMultiplier As Double
Dim playerOut As Boolean

The most annoying I found, unlike modern languages, flow controls like “if-else”, “for” or “while” need to be ended with an “end” statement like this:

'Checking instant loss condition or generating the crash multiplier
If instantCrashCondition Mod 33 = 0 Then
     crashMultiplier = 1
Else
     crashMultiplier = 0.01 + 0.99 / Rnd
End If

This wouldn’t be such a big issue but the errors can’t be copy-pasted when debugging so I could search for their meaning! Oh, and the error outputs are pretty hard to understand.

Otherwise, a decent syntax.

An added feature is that you can combine the “normal” Excel and the VBA easily. 

It is easy to store and read data from the cells and it allows some interesting interactions between the code and the spreadsheet. For example, the graph of the growing function displayed and most of the variables are referenced from cells to update the “interface” with simple Excel references or manually instead of programmatically.

This “feature” creates some unconventional tricks and shortcuts for generating graphics as I did with a pre-made list of multipliers to animate the graph. In the end, you can update the variables in the cells with VBA and let Excel handle the graphics updates.

Compatibility

You are probably screwed if you are not using Windows. While you can record simple macros with VBA, you should be on a Windows machine to unlock the full functionality. On Mac and Linux the application is sandboxed meaning that you can’t import and access commands like sleep(). Also, Google Sheets has no support for VBA.

All things considered, inside Windows and Excel, VBA takes advantage of both really well with full compatibility with Windows libraries and mixing things up with the pre-made spreadsheet formulas.

Overall impressions

I think you can already tell the pattern here. I am really trying to believe in VBA being a legit language as it succeeds on so many fronts but there are some things missing that most developers are taking for granted today.

There are no custom data structures, the data types available are pretty limited, while there are many functions available, you are still limited to what they can do and custom behaviours have to be put together through some creative workarounds. VBA is more of a scripting than a programming language but that really tries (with some success) to compete with the young kids like newer C++ versions and Go.

Finally, it is a convenient language to get a proof of concept like a Crash Game tested in like 5 hours total and from what I’ve experienced it is powerful at parsing and analyzing data but if you want to create something more robust it just feels janky compared to most modern languages.

Happy coding and a good day!