Well, I've worked out my problem and not a particularly satisfactory
workaround - it does appear to be a bug in Excel.
What I did first was to upgrade all my spreadsheets to Excel 2007 format
with macros enabled (xlsm) to ensure I didn't have a 2003 / 2007
compatibileity issue. That didn't solve my problem.
I use the NETWORKDAYS worksheet function with the Holidays option in my
destination spreadsheet and the Holidays are set up in a separate source
spreadsheet (and displayed in ddd dd/mm/yyyy format).
Example of a statement causing the crash if my Public Holdays spreadsheet
"Public Holidays.xlsm" isn't opened first:
=NETWORKDAYS(A2,B2,'[Public Holidays.xlsm]SA Public Holidays'!$B$4:$O$104)
My current workaround:
Open a new worksheet in my destination spreadsheet, name it "SA Public
Holidays" and define links in every cell in that worksheet to equivalent
cells in "Public Holidays.xlsm". i.e. cell B4 is set as ='[Public
Holidays.xlsm]SA Public Holidays'!B4
etc. through to O104.
The following statement then works perfectly without Excel 2007 crashing
regardless of whether "Public Holidays.xlsm" is opened first or not:
=NETWORKDAYS(A2,B2,'SA Public Holidays'!$B$4:$O$104)
So, how do we alert Microsoft and have them fix the bug?
Kathy said:
Using Excel 2007 but saving all files as 2003. When I open a file with links
to other files and multiple charts, Excel crashes during the open
("encountered unepected error and needs to close" error). If I first open
the sheet I am linking to, sometimes it won't crash. This is a big problem,
as I can't open the file to see what the problem really is (and what links I
have in there). Anyone else experiencing something similar?
Thanks.