Ever have one of those moments where you do something, and you think it’s brilliant, and then forty-eight hours later or so you crinkle the nose and say, “Wait, why did I do that again?”
I spent part of last week in Code-land.
I had some VBA macros to write. I’d had meetings in December with various people, getting their input on what kind of output they needed from some Excel spreadsheets. The macros were to take these spreadsheets and chop them up appropriately, and then reformat the data into something useful and pleasing to look at.
I’d run into a logic problem that I couldn’t quite work out — working with Excel’s AutoFilter within a macro can be a gigantic pain, because if you don’t get things just right… — and then my copywriting duties reared their head, so the project fell to the wayside.
I finally came back to it last week. I’d figured out the logic problem, made the AutoFilter do precisely what I wanted from it, and it turned out quite well.
Oh, it needed some serious tweaking once it was done. The specs of the spreadsheet that the macro was for changed between when I started the project and when it went into production. So, naturally, I had to figure out what to do with the code in a very short period of time.
My solution for that, actually, was rather clever. Take the columns for the production spreadsheet. Put them into the order of the dummy spreadsheet that I had worked with in coding the macro. Then, run the rest of the macro code. Not the most elegant of solutions, but it was simple and it worked. 🙂
A few days later, a coworker approached me with a problem. He had this other spreadsheet, and he wanted the same output that the macro produced for the first spreadsheet. Could he just run that macro on this other spreadsheet? No, I said, that wouldn’t work; the columns wouldn’t match up. I said I’d look at the spreadsheet, then see how to make it work. If it would work. Fortunately, this other spreadsheet had the five important columns, just not in the same places.
Did I do the sensible thing, as I had done with the production spreadsheet? Code a little routine to move the columns around to where the macro wanted the various pieces to be? The simple thing, really?
No.
I did the hard thing. I took the macro code and edited it, top to bottom, to work specifically with this other spreadsheet. I thought it was brilliant. I tested it. I tested it again. I pronounced it good.
I thought about this on the train this morning.
Why hadn’t I just done the simple thing? Move the five columns. Call the complication macro routine. Call it a day. That would have been so simple.
Sometimes, you just can’t see the forest for the trees.