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