VBA Player Piano: Pt I

My exposure to Excel VBA started with the record-point-click-stop method. My work in accounting had me manipulating lots of text-generated reports. Different reports had different frequencies of being run. In the end lots of reports were pasted to excel, then massaged and formatted.

The copy-paste-point-clicks-repeating manual work was uninspiring, Change was in order. Not just for me but for my colleagues who would spend too much time on trivial matters instead of focusing on the bigger picture. It was just unnecessary.

My first couple of macros were easy enough. Point-and-click seemed to work as lots of formatting we were doing on day-to-day reports were text-to-columns with headers, summation and minor tweaks. When I clicked on Edit Macro and saw the VBA code after recording, I paid no mind at first. Let Microsoft do its "magic", I just wanted our reports done. And my position wasn't as a programmer so I didn't have time to dedicate to exploring.

On one slow day pre-tax season deluge, I started to read up on VBA code and specifically on the dreaded "Select" statement and how it should be avoided. I looked at my previous macros and noticed "Select" was littered everywhere. And why wouldn't it? If all you're doing is recording a macro based on point-click, that's what you get. As I still had an accounting job to do and we were approaching busy season, I didn't have time to spend refactoring VBA code, and since the macros themselves didn't take long to perform, I thought it was alright for our small use-case.

It wasn't until the next tax season where I gained more responsibility, exposure and experience to both deeper internal accounting processes and programming in general that I started to think more deeply about how to automate these frequent but ever-involving reports.

I've found that in the Excel accounting world, two things a person will commonly encounter are dynamic sorting and absolute vs relative references. CTRL End or CTRL Down won't cut it as easily in VBA. All the "gamer style" keyboard shortcuts a casual Excel user takes for granted have to be explicitly mapped out for a macro.

Complications multiply when working with multiple sheets and files. Suddenly simple Selects and ActiveSheets can have devastating effects when the macro freezes up then crashes because it's trying to perform something on what it thinks is a nonexistent sheet. Not only is point-and-click replication not enough but the need to be accurate and efficient in code becomes crucial.

I found point-and-click recording works well for "instant documentation" to give an idea to the VBA code for small things such as expanding columns, formatting colors/fonts, even inserting pivot tables. It's also nice to show to a total VBA newbie who wants to stop doing repetitive formatting manually. The trick with VBA code is how to structure and declare variables, work with ranges, looping, and ultimately organizing the code in functions and modules. This is where bread and butter computer programming comes into play.

In 2019 I was in a meet-and-greet for a new employee. At the table someone introduced me as an "excel wizard". Maybe relative to my peers who didn't know how I was creating these reports I was a wizard, but compared to the forums where I got most of my tips, I felt like a blushing fraud. It was then that one of our senior accounting consultants asked me into his office for an "excel question".

It turns out he was quite competent in Excel but didn't have time to devleop this specific project. Over some lightly roasted Gregory's coffee and conversational asides into Sci Fi topics, I asked him in detail what was needed. While I didn't have direct experience on senior accounting operations or reporting, this project would be strictly Excel based. All the accounting prep work involving data gathering and calculations would be available.

The cliff-notes version was that the accounting team had 9 tabs (among more) pulling in different sources of data points from internal and external systems, which all needed to be encoded to the department's specifications, filtered on certain parameters and be exported to a template file to be uploaded as journal entries in another module. In theory it wasn't too difficult, but in practice as it currently stood, there were lots of manual steps that resulted in this monthly procedure taking 1-2 working days to complete. Complications arose when other pieces of the month-end took time away from this.

At the time our department was all over the place with a lot of restructures, new hires and tangled web of ad-hoc excel formats. My mission, should I choose to code it, was to knock this down to a 1 button click and spare my colleagues the pain of long nights at the office every first to second of the month. Like a bored pilot reading the newspaper in a hangar, I felt like I finally had a mission worth flying...