Whenever I program,I try and write that code as clean as possible. I try to enforce practices such as stylized guides and enforce good github project guidelines as well. And program daily to practice these standards
However, this is not always the case. Sometimes I’ll program in a way that reflects that language or framework, (e.g. writing python in a pythonistic way). Occasionally I’ll have to touch a tool that I’d rather not use (e.g. Autohotkey) that has inconsistent syntax. But there’s no other good alternatives for adding complex windows shortcut hotkeys
One of these off-cases is excel VBA (visual basic applications). Its one of my infrequent languages as it is verbose, so I don’t invest in a dedicated development environment. I only wrote a few basic simple scripts with it.
But the microsoft app suite practically runs off this, and excel is very useful for analyzing and working with small datasets. Its portable once you understand how VBA ties into the microsoft suite environment.
Last year, I started using a SaaS relational database management tool called Airtable. I described it to my colleagues as a google spreadsheet 2.0 with RDBMS features like microsoft access built in, all in one slick user friendly interface.
Companies like weWork use airtable to capture their object catalog for constructing their shared office building suites. I use it at work to manage product data and sending purchase orders to China.
In any case, airtable lacks some important features. It doesn’t have a ready exportable way for downloading assets your assets. This is a hassle for risk management, since I want access to all the work locally. I
I spent 2 months writing a bulk downloader and renaming script in excel VBA for handling this. What it does is take a list of amazonS3 links, download it to a sub folder, and copy over those into a unique primaryKey file names using a windows batch script.
I could have written this solution in many ways. One is python with a pandas library for dealing with CSV files, using automate the boring stuff as a guideline. However, I don’t like relying on build processes if it can be avoided (e.g. installing python). I know that all the end users using airtable probably do not want to either. I opted to using an excel VBA solution as it is very portable (all you have to do is share an excel .xslm file)
Beginning of Sloppy Code
Excel VBA is not my programming language of choice. Its confusing to someone who doesn’t frequently develop in that framework or application. It requires you to understand what excel is doing in the background, on top of understanding what VB6 is doing as well. I knew what the end result should be and the steps taken to get there, but it was a painful process.
First, get excel VBA to download an image asset using a HTTP GET request. After digging through stackoverflow, I found the solution here, which I still have no idea what it really does since it looks like assembly code and relies on .dll libraries beyond my comprehension
Next, get a column of data in excel into a .bat forma & execute in bash/cmd. Stackoverflow had the solution again!. Naturally, copy pasta
It was a much deeper process though in figuring out that a window batch process could do this (like 10 questions on stackoverflow…), . It boiled down to is this command,
copy "C:\folder\original-s3image" "C:\folder\sub\new-image-names"
Now, I needed to just connect the dots together. I had my starting and ending point. Just needed the chunky middle man code. The Pseudocode for the whole program as follows, where (column A = newImageName, column B = unformatted amazonS3 download link)
- Download airtable excel CSV file
- Ask user to make a subfolder name (e.g. batch1) to host final named assets
- Format all necessary data (amazonS3 download link, image name on download, batch file commands in columns)
- Run download image library
- Process out results by making a batch file and running it
The Middleman Sloppy Code
You can find my sloppy code here. It looks like gibberish. Here’s a small sampling of what the code looks like
If this makes no sense, it probably shouldn’t. Its a macro recording of the find/replace method in excel, in 3 seperate statements. I wrote it purely by recording macros, and dumping that output right into my VBA file.
Its sloppy, and doesn’t follow any of the conventions that I normally use while programming. Its mostly using built-in functions that excel has to offer and writing it the most excel-way possible
Why would I choose to write it like this when I could’ve written it in a clean VB6?
A few reasons
- The project was small enough / low technical debt – that being sloppily written didn’t make a difference, technical debt is very small. Its not like I was going to integrate this codebase into a larger excel VBA suite project.
- One time project – I wrote it for myself. I knew 100% what the end result would be, and what improvements could be made, they were very minimal (Error checking, handling other file outputs)
- Results fast – I needed to rapidly prototype and get to an end result. Refactoring can always come later. I compartmentalized all my poorly written code and they are all seperated in a organized manner (Seperating poorly written code VS well written code). Not only that, recording excel macros makes it easier to process out the information (e.g. copying over a column of data into a batch file).
- Niche uses – Only a few hundred people needed this script, it was not designed for long term scalability, and didn’t need to have any performance checks either (bigO is irrelevant). Users would only use this once a month maybe at most
- Forced development environment – I can’t really use my own development environment in excel VBA, so managing things on git requires you to copypaste and or add plugins / build a development environment. This makes it extremely hard to track changes. Anytime I work in excel files in general, its generally a messy pile of 💩 until I can get it into a proper database
- No intentions of collaborating in mind – I had no intentions of collaborating with other users on this project, so something like the below image is irrelevant. Not only that anyone in experienced in VBA could have rewritten easily since I documented what the macro was doing on the my-how-to-use-guide or even the readme.
In summary, this is why I intentionally chose to wrote sloppy, excel VBA code. It wasn’t my primary language, writing with macros was less painful, and it worked. All my use cases were met, and it frees up time where I could be focusing on more important things.
Naturally, this goes against a lot of principles that I try to practice. My rule of thumb is to go all out or not at all, in this case, I achieved the bare minimal viable product – a working prototype.
If I wanted to scale up this project, excel VBA would not be the first tool I grab off the shelf. Upon using it is rather complex and/or limited to working in things outside of excel, e.g. downloading image URLs.
To sum up, this post from stackoverflow summarizes my opinions on intentionally writing sloppy code
At the end of the day, ship the f*****g thing! It’s great to rewrite your code and make it cleaner and by the third time it’ll actually be pretty. But that’s not the point—you’re not here to write code; you’re here to ship products. – Jamie Zawinsky
As long as your technical debt doesnt lead you to “technical bankrupt”
If both of these and most of my cases outlined above are met, If the end user is happy and there is no demand/integration for new features, then sloppy code suffices. In the mean time, sloppy code should be ideally be minimalized and /or compartmentalized with good commenting conventions so its easy to refactor.