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.


10 thoughts on “Thicker Than A Length of 4be2

  1. I hope you billed for more than just five minutes!

    Sometimes the simple problems require more effort, and I speak for myself when I say that I can’t remember everything at once. It takes some time to work towards a solution.

  2. Thank you for sharing your experience.

    The lesson that I take away is check the code and structure of source data before editing the final presentation. I would gather in most situations like this, if the code was checked first, the problem becomes how much code and data processing logic documentation is provided.

    (note: your captcha image does not display in Google Chrome)

  3. JP, the dilemma is when and under what justification do I charge for my dopiness. I accept that we can expect to be perfect every time and that customers will pay for some of my errors, but of this magnitude? I would love to charge it, but not sure I can

    Joe, I don’t see it is anything to do with looking at the code and structures. I did all of that and decided that editing the template was the simplest solution, testing proved that wrong (I could have spent 3 hours familiarising with code that I didn’t know, or 5 minutes to prove my approach – tell me which is better). Then my first thought was I had to amend the code, whereby I spent those hours, when I really needed to step back and look at the problem as if I was just implementing it, not changing an existing solution.

  4. Bob,
    I also use a 3 date system for my trial version add-ins.
    Before storing the dates, I convert each date to a long and then convert the long to a string.

    That ought to work for any date system; “40399” is hard to misinterpret. However, with my (99%) lack of user feedback, I don’t know for sure what doesn’t work.

    The recycle bin is my home away from home.

  5. Hi Jim,

    I was thinking of changing it for a date format of say 10 Jun 2010. This has the advantages of being unambiguous, and I can also read it wen I look in the registry.

    I must admit that the best defence against VBA dates that I have found is to do a CDate on them. Excel understands international date issues, and this seems to understand them as well even if VBA does not.

  6. I disagree that dopiness was involved at all. What you call a “five minute problem” is a matter of perspective. The solution itself may take a few moments to implement, but actually arriving there (especially when having to fix someone else’s work) may take much more.

  7. It’s a trick one, if you find a quick solution to a big issue do you still charge for the big fix? I guess it depends on the relationship you have with the client as well to some extent. I think the first issue is fair enough, but the second one should be charge at “cost” not “actual” ! Its a problem we all face though, no ones perfect.

    The date issue is fun, I still have a part written data validation “class” which has halted at the data function, so may way to address it, need to get back in there and just pick one!


  8. I think that is an easy one Ross, you charge the big fix. They are getting the benefit of an expert there, so that is quite ethical. But that in many ways is why you can’t charge when you are charging them as an expert, and perform like a RentACoder .

  9. It seems to me that your 5-minute solution is actually the better solution, looked at from the point of simpler future maintenance and the smaller chance of introducing new problems. Therefore it would be ethical to charge more for the quick solution. But – had you not thought of that solution you wouldn’t have known that you should be charging less!

    Thank you for posting your thought processes. I know that I often spend too much time producing overly complicated solutions, but in this case the Excel format idea came into my head at the first mention of USD, and that makes me feel more optimistic about today’s crop of problems.

  10. After many glitches with dates I finally started using ISO date formats, either yyyymmdd or yyyy-mm-dd. These have the huge advantage of being readable AND sorting properly as text. OTOH, longs aren’t readable, and other date formats as text generally don’t sort.

Leave a Reply

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