Journey to Access: Part 2
In Part 1 of this series, I described a typical scenario where an enterprising employee assumes the mantle of "power user." It starts with a common office environment where the staff manage the daily tasks through a series of Word documents and Excel workbooks.
In that first part, I asked you to imagine that you started a job at just such an office. I described common office tasks like printing blank forms, tracking multi-step processes, sending letters, and running quarterly reports. By the end of that article, you had created your first Excel macro and were looking forward to saving three hours every time you had to run those reports in the future.
Fast forward three months. It's finally time to run the next set of quarterly reports. The Excel macro you built and tested last time has been on your mind ever since. Instead of dreading quarterly reports day, you've actually been looking forward to it. Heck, you even get to the office fifteen minutes early that day.
You log in to your computer. Open up Excel. Load up your "Permit Requests.xlsx" file. Run the "Quarterly Reports" macro that you recorded 90 days ago. And in the blink of an eye you have... last quarter's report.
It's disappointing, but as soon as you think about for two minutes it makes perfect sense. When you recorded the macro last quarter, Excel saved all of your steps. When you replayed the macro, it replayed all of those same steps. Right down to selecting the records from the previous quarter.
So maybe this won't be as easy as you thought. But you know this will pay off in the long run, so you set to work figuring out how to make this happen. After some brief googling, you realize you need to change some of the VBA code. You open the Macros window, select your macro, then click [Edit].
You're met with a wall of text on a white background. It's mostly black text, but there's also some blue and green mixed in, too. You're not sure what that's all about yet, but you press on. You start reading the code itself. It's brief and there are an awful lot of periods, but it's surprisingly readable. It should look familiar. After all, it's just a transcription of all the steps you took inside Excel.
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B1:D5").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Selection.Font.Bold = True
End Sub
Before long the problem becomes clear. It's that very first line: Range("B1:D5").Select. That's the range for last quarter's data. This quarter is a different range. The easiest thing to do is just update that range with the new range for this quarter. It's going to be annoying having to do that every quarter. But hey, five minutes to update the starting range is still a lot faster than three hours.
You update the range. Save the workbook. Re-run the macro. And then something else breaks. Back to Google. Ten minutes of searching and reading, twenty minutes of trial and error, and you've fixed that problem. Only for another one to pop up. Back to Google.
The cycle only repeats itself about ten times. But by the tenth time, the cycle is getting shorter.
And then. Finally. Forty-five minutes after you were supposed to go home. After eating lunch at your desk (even though you're really not supposed to do that). And blowing off both of your scheduled breaks. You re-run the macro for about the fiftieth time that day. AND IT WORKS!!!! Sweet Mary mother of God it works!!!
You leave work that night with a huge smile on your face. You're so excited. You get home still on cloud nine. You forget that you're getting home an hour late. You walk in the door and your husband (or your wife or your dog or your fish) looks at you and says--with their human voice or their understanding eyes--"Rough day at work? You've been gone more than ten hours. What happened?"
"I saved over three hours of work!"
Your housemate looks back at you with a mixture of pity and bewilderment, silently thinking, "I'm living with a weirdo."
That's right. If you're the kind of person who gets excited about spending ten hours to save three hours, then you are most definitely a weirdo. But weirdo is too broad a term for this specific form of psychosis. The weirdos who enjoy spending lots of time to avoid doing mundane tasks that only take a little time have their own special name: programmers. Welcome to our very weird club.
UPDATE [2021-07-30]: Fixed link to Part 3 (h/t John Clark).