Writing VBA macros for Excel or Word

Writing VBA macros for Excel or Word

This is a blog post to document and hopefully help people who find themselves with the problem I had today.  You’ll also get to hear a little about my job.

Part of my job is to witness, photograph and take video of shock testing that we do for the Navy.  We have a medium weight shock test machine that can handle items up to a few thousand pounds.  Most of the items tested are furniture (desks, chairs, couches, lockers) but we also get computer racks, wall panel systems, insulation, and sometimes huge pipes and fittings.

Here’s a video of something similar to what we do, but not the same…  http://www.youtube.com/watch?v=WStzQSLkrXs

(I should see if my company objects to using social media such as YouTube for our videos. We have a couple of ‘in-house’ ones I could probably share.)

Well-designed items that we test are fairly straight forward, nothing breaks, sometimes a few welds get hairline cracks.  Sometimes things are more interesting.  If part of a test item “comes adrift,” i.e. flies into the air, then that’s a safety hazard and the item fails the shock test.

Anyway… that introduction leads me to my scripting issue.  After an item passes a test I have to combine all the photos and descriptions into a report.  These follow a template, so I wrote a script to take the photos and descriptions from a spreadsheet and insert them into the right format in my Word template.  I wrote this a few years ago when I first started working here and have been using it since then.

Strangely when I went to run it yesterday I got an error from Word.

4198-runtime-error

So helpful, yes?

When I hit Debug it highlighted the following code:

code-txt

Nothing to see there really.  I googled and Microsoft had some page about problems associated with that error, but in the end it wasn’t related at all.

So how did I solve my problem? The best way a non-programmer who dabbles in programming can. By Recording a new macro that did what I wanted my script to do.  You see, Word and Excel have a “record macro” function built in to help you create macros and scripts.  When writing a script I usually do this and then try to modify it without breaking it.  Usually pretty easy once you get the hang of it.

Well this time when I recorded the script it output the following:

code-fixed

Ah ha!  That first line is brand new!  And once I added it to my script it solved my problem.

The only thing I don’t understand is how the program ran for 2 years without that line and only just now required it.  But whatever. It worked. And because I save time by automating my report I had time to write about it.

Scripting is a good tool to learn if you work with computers a lot.  Any task that you’re doing the same thing or mostly the same thing over and over again can probably be automated.

Submit a Comment

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

CommentLuv badge

Related Posts Widget for Blogs by LinkWithin