Thirty seven years ago I briefly worked for a little startup based in Hoover, Alabama, which is a suburb of Birmingham. The personal computer era was just dawning and this company was selling personal computers to small businesses and writing software to fit their needs. The computers were from an outfit known as Ohio Scientific and the software was written in a very early version of Microsoft Basic.
This version of Microsoft Basic provided two letter variable names and all variables were globally visible. That means that if you used a variable in a subroutine you couldn’t use it anywhere else in the program without clobbering it every time you called the subroutine. And recursion was definitely not supported. I spent a month debugging a primitive Numeric Control compiler that I wrote in that Basic. I swore I’d avoid writing any software in Basic after that.
Flash forward to the present and I find myself trying to automate analysis of a large data set that is stored in an Excel spreadsheet using Microsoft’s Visual Basic for Applications (or VBA as they like to call it). I will admit, it is slightly improved over the language that gave me fits four decades ago. But, it is still giving me fits. I am in the unenviable position of being too aware of what I want to automate and too ignorant of the mechanisms that might help me achieve that automation in VBA.
I have made a cursory search for solutions to my problem and so far I have learned several things. First, most people that are using VBA are not experience programmers, they are Excel power users. I, on the other hand am an experienced programmer and at best a proficient beginner with Excel. I have been using spreadsheets for twenty five or thirty years but haven’t progressed much farther than summing a column of figures.
Another thing that I’ve learned is that there are hundreds of books that teach you how to use Excel. Most of these have a chapter or two on writing VBA macros. Macro is what Microsoft calls VBA subroutines and functions to try to make them seem less intimidating to non-programmers. Truth be told, if you are writing VBA macros you are writing programs. There are even some books that are narrowly focused on writing VBA macros but these all seem to assume that you are already an Excel power user.
I am reminded of the Star Trek episode (City on the Edge of Forever) where Mr. Spock explains “I am endeavoring, ma’am, to construct a mnemonic memory with stone knives and bearskins.” That is what it feels like trying to write analysis software in VBA.
There are other options. I will investigate them. One that comes to mind is to use a more rational language like Python to manipulate the data using one of the libraries that allow you to read and write to Excel spreadsheets from Python. It will probably be a lot better, for me at least, than struggling with VBA. It certainly couldn’t be any worse.
Sweet dreams, don’t forget to tell the ones you love that you love them, and most important of all, be kind.