Last week the publications at work went to press. I’ve always referred to this week as “Hell Week,” though for the most part it’s really not that bad. Maybe I have to stay late on Wednesday, but that’s not such a bad thing; I unplug the headphones on the computer speakers, crank up the subwoofer, and rock out to something really loud with a metric fuckton of bass because there’s no one else on my half of the sixth floor. 🙂
This particular Hell Week was a little rougher than any in the past six months, but everything turned out well. I plan for the worst, and when the best happens, it’s like the sun coming out.
The thing I realized this week is that I need to do three things
- Write a few new Excel and Word macros
- Rewrite a few old Excel and Word macros
- Document what I do on one or two things; I’ve been reinventing the wheel when I don’t have to
Let’s take number 1, “Write a few new Excel and Word macros.”
In Excel, I need macros that deal with the unique formatting concerns of the consumer and UK documents. The specific macro I’ve been using for those documents works, but it’s also limited in that it only does about sixty percent of what I need done in Excel. Two new procedures, then, to handle the other forty percent.
In Excel, I also need a macro to pull specific product data out of an in-stock spreadsheet, delete out the unnecessary data, and put it in a format I can then work with in Word.
Also in Excel, I need a macro to take information from the consumer spreadsheet mentioned above and delete the unnecessary bits, leaving me with a two-column spreadsheet for clean-up in Word.
Then, in Word, I need macros to handle the unique formatting needs for the resulting spreadsheets from the latter two procedures. The macro for the first won’t be difficult; it’s a variant on something I’ve written before. The latter will be a little more complicated, but not by much.
The macro rewrites shouldn’t be especially complex. I need to adjust an existing Word macro to account for the customer and UK Excel macros I’ve mentioned above.
And then comes the documentation.
I think when I document my code, it’s going to start out with a passage from Estoire del Saint Graal: “Here begin the terrors, here begin the miracles.” It’s an appropriate thought; if someone had to use these macros without knowing what they did, it would be terrifying, while if someone knew how to use them, they would be miraculous. I suppose, later in the code documentation, I should put in “Whom do the macros serve?”
Scoff if you must at my coding frivolity, but I should tell you that I once wrote a macro entitled “Paint It Black” (it’s a silly little routine to turn the text of an entire document black) that puts up lyrics to the Rolling Stones song on the screen in message boxes. 🙂
For a long time, I resisted the idea of taking the Word documents that are produced, stripping them down into raw text, and then performing surgery on them in Excel. The Word documents had formatting that I didn’t want to lose. But Word was also limited in what it could do; Word macros are like blunt sledgehammers, while Excel macros are more like delicate scalpels. It took a weekend of coding back in April to come up with a way to take a plain text file and put it back into the same Word format from whence it came.
Well, almost. Two things get lost and have to be fixed by hand. I could write a macro for Word to fix those last two things, but I won’t. First, they’re easy to fix; highlight a line and, using Word’s style formatting, change the style. Second, I’d rather be forced to go through the document, fixing these fifteen or so lines by hand, which also forces me to look at the document to make sure that nothing else got munged up, than to have everything fixed, only a formatting problem creeps through.
There’s no urgency on these; I have about a month until I have to use them in a production capacity. I should have time on Tuesday to really sit down and figure out what I need.
A good macro really is like the Holy Grail. It takes a barren wasteland and gives it life. 😆