Category Archives: 13895

Thicker Than A Length of 4be2

Being a
developer can be addictive fun. Sometimes you just get into the swing and
before you know it is midnight and the house is eerily quiet. But it can also
be hugely frustrating. Yesterday I had just such one of those days. I spent a
whole day on a task that should have taken an half an hour maximum.


I have
inherited the maintenance of a small application that that creates Word
documents from a set of inputs in an Excel form and spreadsheets. Now being a
good coder, the original developer created Word templates with a series of
bookmarked fields that he updated with the information collected on the Excel
sheets. I was recently asked to make a small amendment. This amendment affected
a table of prices on one of the Word outputs which shows a product number,
description and price. The client wanted the price to be appended with the text


Originally I
thought I would just update the template and add USD after the price bookmark
fields. This I did, just needed to test it then
Excel Do, Dynamic Does


getting the code to run was gave me great problems, the addin would just start
and then immediately exit. To cut a long story short, after much rooting around
(imagine trying to debug an app that immediately shuts down), I tracked the
cause of this oddity. It was all down to the fact that the code stores 3 dates
in the registry, and compares these dates at startup, shutting down if there is
some issue. Unfortunately, the dates were being stored in the registry in US
date style (mm/dd/yy), and so today’s date of 8th June (as I said,
this was yesterday) was being stored as 06/08/10 because of VBA’s idiosyncratic
handling of dates in a non-US date format, and when this was read back in the
code saw it as 6th Aug, and immediately exited.


I finally
got around this by changing the date in the registry, but will have to change
the code to save the date in a format that is not US-centric for future
changes. I can see the original developer’s logic, he is US based, the client
is US based and there would be no possibility of anyone else using the app, so
US style dates were not a problem. Not until I came along anyway.


On testing
my simple solution, I hit a  problem because
 the list of prices catered for 10 different
items. An offer may be made with less than 10, so that when the app was run,
and I only entered 3 items, the document had 7 lines with just USD on them. I
couldn’t add the text to the template Word template.


thought was to dive into the code and when the price is taken from the Excel
sheet and copied into the bookmarked fields, I would append USD at this point
(there is no concern with other currencies, this is a custom app for a solely
US-centric company).


Now, I had
never dived into the code of this app before, all previous changes that I did had
just been effected by changing the Word templates or the Excel sheets. Stepping
through the code I found that it uses named ranges on the Excel sheets that
correspond to the bookmark names in the Word template, and so just goes round
in a loop and transfers the values from the named range to the equivalent Word
bookmark. This was not good news, for me, because it meant that I would have to
test these names and look for specific names, and add USD to those values. Not
a great coding challenge, but it seemed a very poor solution to me, breaking
what was a very clean and tidy original code model. Worse, I had some similar
changes in other docs to do, and I could see it getting very messy.


Then after
dinner, my brain finally engaged. The prices in the Excel spreadsheet were
being displayed as dollar values, for instance as $1,234, and this was what was
transferred to the Word bookmark field. It finally dawned on me that all I had
to do was to change the format of the Excel field from “$”#,##0;;; to
“$”#,##0 “USD”;;; and the existing code would take care of it all for
me. A quick change of formats, a simple test run, and all was shown to be


So a 5
minute problem took me a couple of hours to resolve. At times, my denseness amazes
even me. My only thoughts were that getting over the app dates problem has slowed
my brain down, at least that is the excuse I am using.