I’m adding this to my work portfolio being as I did it, it worked, and it functioned from all users’ perspective perfectly and even impressively (more on that at the end). But this was really just a toe-dip into Visual Basic for Applications (VBA). I make no claim to “know VBA”.

I made a series of macros that processed varying kinds of text files received (usually) weekly from clients. The output was used by staff to verify active eligibility of benefits for members. The macros removed extraneous data, sorted, surfaced, and in some cases highlighted criteria for differing levels of benefit coverage, then output a formatted spreadsheet to the company shared filespace.

I got it done, but I’d had never worked in VBA before (or since). Done properly, this would have been a pretty straightforward series of scripts. Instead, it got accomplished to a large extent using Excel’s recording tool (for roughly 70% of the code generated), and then (30%) cleaned up and enhanced with VBA I wrote (for file loading, some formatting, and smoothing out some of the recorded actions).

Recording the macro actions did provide an unintended benefit though. When ran, team members watched a rapid-fire visual of the macro at work, which was impressive to the staff and got me some kudos as it “looked like magic”.

Putting on my user interface hat for a minute, this is a pretty good example of when user interface becomes more important than efficiency. The team valued and used the macros in part because it was fun to watch it run. It’s entirely plausible that were the script just a black box of “put the file here” there would have been some adoption resistance from a few staff. Had this project been before I taught Information Design, it would have definitely made it into the lecture.