My Sloppy Excel VBA Code

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 sloppy and 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 put 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.

Introducing Airtable

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 already know 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)

1. Download airtable excel CSV file
2. Ask user to make a subfolder name (e.g. batch1) to host final named assets
3. Format all necessary data (amazonS3 download link, image name on download, batch file commands in columns)
4. Run download image library
5. Process out results by making a batch file and running it

Data before the script

Data after the script

Image assets after the script

.bat file helper created to copy+rename images

The Middleman Sloppy Code

You can find my sloppy code here. It looks like a massive load of gibberish on glancing through it. It doesn’t look pretty. 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

1. VB6/VBA is not my primary language — I write python and javascript with good practices in mind, this is not true with excel VBA, writing excel VBA is inherently messy in my opinion dealing with how microsoft excel’s objects / built in functions. I personally like extremely minimalistic frameworks and buildtools
2. 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.
3. 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)
4. 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).
5. 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
6. Familiarity with excel — I use what is most familiar to me, recording macros, to understand what goes on behind the scenes. Using macros this way is similar to using javascript + the debugger in chrome. Its also significantly faster outputting columns of data using built in excel functions than writing it in VB6 to columns of data.
7. 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
8. 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

and this:

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.

Originally published at vincentmtang.com on March 16, 2018.