« Puzzle: Civilians in Space | Main | Reading List: Rats »

Saturday, August 5, 2006

The Hacker's Diet: Excel 2003 Macro Update

I have posted an update to The Hacker's Diet computer tools Excel macros which work around a long-standing bug in Excel. All the way from Excel 2.1 through 5 (in my opinion, when this once-great program stopped improving and began to spiral into the abyss exemplified by Clippy, VBA macro viruses, and other abominations) you could “link” a macro sheet to a document and define a macro which was automatically run when the document was loaded which opened other related documents such as auxiliary worksheets and charts. As long as all the related documents were kept in the same directory, all you had to do was double click on the main document and the entire ensemble of interrelated documents would be opened automatically.

With the advent of Excel 5, the wheels fell off this happy situation. Now, the main document would find the macro sheet all right, but when the initialisation macro tried to open the other documents, the user would get a “File not found” error message. If the main document were opened from the “File/Open” menu everything would work, but double clicking the document from Explorer or even opening it from the “Recent documents” menu in Excel would fall flat on its face.

Apparently (and this is pure inference and speculation on my part, unconfirmed by experiment), in Excel versions before 5, the process of opening a document set the current directory for the drive on which it resided to the document's parent directory, but in version 5 and later, the current directory was unchanged. Consequently, when a linked macro attempted to open a document without an explicit directory specification, the open would fail unless the user had previously navigated to that directory explicitly, for example with the “File/Open” menu.

There is, however, a function in the classic Excel macro language called GET.DOCUMENT which, if called with an argument of 2, returns the full directory path of the current document. You can use this to open auxiliary documents in the same directory with code like the following, which appears on line 263 of The Hacker's Diet macros for Excel 2003: 

    =OPEN(GET.DOCUMENT(2) & "\" & "WEIGHIST.XLS",0,FALSE)
This prefixes the directory of the yearly weight log to the name of the cumulative weight history file and guarantees that it will be opened from the same directory.

If you've been irritated by not being able to double click a yearly log document and have the history open correctly, download the Excel 2003 macros and the problem should go away. If you're maintaining Excel macros which suffer from this problem, this is how to fix it.

Posted at August 5, 2006 00:50