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 USD.


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 thenExcel Do, Dynamic Does


Unfortunately, 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.


Next 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 working.


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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>