I’ve written my first Microsoft Word macro since, let me think, 1999. I’ve recorded macros to do repetitive tasks, but for work I needed something a little more… robust. Yeah, robust. That’s a good word. Robust.
Wait. What was I saying? Oh, yes.
There’s a process I have to run once a month. I ran it, the first time, my second day on the job, and I had a feeling that the macro the company supplied to do the job didn’t do the job when I ran it. When the printouts came, I knew–the macro hadn’t worked. When I ran the process again last week, again, I felt that the macro hadn’t worked, though I managed to manually fix ninety percent of what the macro was supposed to catch.
I had some downtime this morning while waiting on a report, so I decided to look at the macro code.
If my jaw could have hit my desk, my jaw would have hit the desk.
First, I hadn’t seen a macro written in WordBasic in, oh, ten years. WordBasic went out of style when Office 97 came out.
Second, the reason the macro didn’t work was because what the macro was supposed to do the macro wasn’t coded to do.
I talked to my supervisor, explained what I wanted to do, drew lots of circles as a demonstration, and he told me to knock myself out.
I think I spent four hours on thirty lines of code. I wrote out the algorithm in plain English, then started looking for VBA examples online and in the VBA help system. (I have a VBA manual, but it’s in storage. Fat lot of good it’s doing me there.) And I used the macro recorder for the one tricky text manipulation I needed done. I found an example of the Do… Loop structure I wanted. I consulted VBA’s WordBasic-to-VBA equivalent page. I plugged in the code I’d recorded. Two other functions I’d done manually with the same data the original macro dealt with I automated with two more lines of code. I was shocked–it worked right the first time.
If I have more time this week there are more functions I’ll add.
The process will never reach the point where I’ll be able to press a single button and voila! because, let’s be frank, that would be obscene. But a whole sequence of search-and-replaces can be automated.
One thought on “On Word Macros”
I caused a little ripple of surprise by creating a tracker in Excel in order to ensure things didn’t go missing at my new job. It has formula to produce the estimated costs and stuff. Geekiness is fun.