Excel 97

Hi, we have a Workbook originally developed by my company's Actuarial department to validate results for our bespoke application.

This workbook, contains numerous worksheets containing lookup tables, and calculations, some of which are User Defined functions. The User Defined Functions are written in the the code module of the relevant worksheet and are called from a cell within the corresponding worksheet. eg.

B50 : =Actuarial_Function(input1,input2,input3)

Each year new rates are added to our application and are verified by a corresponding updated version of the workbook.

This year the Workbook has manifested a strange behaviour of calling one the User Defined functions when changes are made to an independent workbook opened in the same session of Excel. eg, open testing workbook, enter data everything is fine calcualations work, open a new workbook or existing workbook and when modifications are made to that it is apparent that the User Defined Functions have been called in the Testing Workbook and when I switch back to the Testing Workbook I get a #VALUE! in the cell which has called the UDF.

I can see the reason the error generated in the way the User Defined Function has been written, the range object has not been specifically allocated to the Testing Workbook, so when a 'foreign' workbook calls this function it failsover as the range does not exist and falls over with an object not found error. I could modify the UDFs to fix this but I want to understand why this is happening in the first place.

I have tried removing the "Update remote references" and "Save External link values" from the Calculation tab in the Options menu to no avail.

This workbook has been in operation for 5 years and whilst not perfect I have never seen this behaviour. I would be much obliged if anyone can shed any light on why this has suddenly started happening.

Simon