On Coding Hijinks

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?


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.

Published by Allyn

A writer, editor, journalist, sometimes coder, occasional historian, and all-around scholar, Allyn Gibson is the writer for Diamond Comic Distributors' monthly PREVIEWS catalog, used by comic book shops and throughout the comics industry, and the editor for its monthly order forms. In his over ten years in the industry, Allyn has interviewed comics creators and pop culture celebrities, covered conventions, analyzed industry revenue trends, and written copy for comics, toys, and other pop culture merchandise. Allyn is also known for his short fiction (including the Star Trek story "Make-Believe,"the Doctor Who short story "The Spindle of Necessity," and the ReDeus story "The Ginger Kid"). Allyn has been blogging regularly with WordPress since 2004.

Leave a Reply

Your email address will not be published. Required fields are marked *