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

Just off the top of my head...

Where any of these problem formulas copy/pasted into the new workbook
from the test workbook? This would establish a link, I would thing,
causing the new workbook to call the UDF in the test workbook.

Were any of the sheets containing problem formulas copied from the test
workbook into the new workbook?

Is the new workbook being created from a template, OR is it the old
workbook "SavedAs"?

If anything that references a range object in the test workbook is
copied into another workbook, the definition copies as well. This
suggests to me the range objects in the test workbook are globally
defined. If, as you state, the UDFs are in the code module of each
sheet then any range references should be defined as local to that
sheet.

Global range defs travel with copy/paste,
replacing any global defs with the same name.
(Dependant on how the Q asked during paste was answered)

Local range defs are unique to their respective sheet,
and travel with it wherever it goes.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 12, 7:10*pm, GS wrote:
Just off the top of my head...

Where any of these problem formulas copy/pasted into the new workbook
from the test workbook? This would establish a link, I would thing,
causing the new workbook to call the UDF in the test workbook.

Were any of the sheets containing problem formulas copied from the test
workbook into the new workbook?

Is the new workbook being created from a template, OR is it the old
workbook "SavedAs"?

If anything that references a range object in the test workbook is
copied into another workbook, the definition copies as well. This
suggests to me the range objects in the test workbook are globally
defined. If, as you state, the UDFs are in the code module of each
sheet then any range references should be defined as local to that
sheet.

* Global range defs travel with copy/paste,
* replacing any global defs with the same name.
* (Dependant on how the Q asked during paste was answered)

* Local range defs are unique to their respective sheet,
* and travel with it wherever it goes.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

To answer your questions no in all cases. I could understand there
being some form of link in place if the sheets were any related.
However, if I just do a file new workbook and start typing, it upsets
the "Test Workbook".

I double checked the UDF's and my initial statement that they were
linked to the code sheet was wrong, they are placed in generic code
modules within the workbook. I have tried limiting their scope to
PRIVATE but the same behaviour still manifests.

Regards

Simon

It happens that armsiee formulated :
On Jan 12, 7:10*pm, GS wrote:
Just off the top of my head...

Where any of these problem formulas copy/pasted into the new workbook
from the test workbook? This would establish a link, I would thing,
causing the new workbook to call the UDF in the test workbook.

Were any of the sheets containing problem formulas copied from the test
workbook into the new workbook?

Is the new workbook being created from a template, OR is it the old
workbook "SavedAs"?

If anything that references a range object in the test workbook is
copied into another workbook, the definition copies as well. This
suggests to me the range objects in the test workbook are globally
defined. If, as you state, the UDFs are in the code module of each
sheet then any range references should be defined as local to that
sheet.

* Global range defs travel with copy/paste,
* replacing any global defs with the same name.
* (Dependant on how the Q asked during paste was answered)

* Local range defs are unique to their respective sheet,
* and travel with it wherever it goes.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

To answer your questions no in all cases. I could understand there
being some form of link in place if the sheets were any related.
However, if I just do a file new workbook and start typing, it upsets
the "Test Workbook".

I double checked the UDF's and my initial statement that they were
linked to the code sheet was wrong, they are placed in generic code
modules within the workbook. I have tried limiting their scope to
PRIVATE but the same behaviour still manifests.

Regards

Simon

Does the UDF in question ref ActiveWorkbook or ThisWorkbook?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
It happens that armsiee formulated :
On Jan 12, 7:10 pm, GS wrote:
Just off the top of my head...

Where any of these problem formulas copy/pasted into the new
workbook
from the test workbook? This would establish a link, I would thing,
causing the new workbook to call the UDF in the test workbook.

Were any of the sheets containing problem formulas copied from the
test
workbook into the new workbook?

Is the new workbook being created from a template, OR is it the old
workbook "SavedAs"?

If anything that references a range object in the test workbook is
copied into another workbook, the definition copies as well. This
suggests to me the range objects in the test workbook are globally
defined. If, as you state, the UDFs are in the code module of each
sheet then any range references should be defined as local to that
sheet.

Global range defs travel with copy/paste,
replacing any global defs with the same name.
(Dependant on how the Q asked during paste was answered)

Local range defs are unique to their respective sheet,
and travel with it wherever it goes.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

To answer your questions no in all cases. I could understand there
being some form of link in place if the sheets were any related.
However, if I just do a file new workbook and start typing, it upsets
the "Test Workbook".

I double checked the UDF's and my initial statement that they were
linked to the code sheet was wrong, they are placed in generic code
modules within the workbook. I have tried limiting their scope to
PRIVATE but the same behaviour still manifests.

Regards

Simon

Does the UDF in question ref ActiveWorkbook or ThisWorkbook?



If you "start typing, and it upsets the test workbook" suggests to me
that you are doing something with Application.OnKey. I have a workbook
that I created under xl2003 that uses Application.OnKey to modify the
tab / enter keystroke behavior.

For reasons that I never did get to the bottom of, that workbook does
not always reset Application.OnKey when it loses focus ... and the
modified Application.OnKey processing invokes the code module that it is
pointing to, even if that workbook happens to be closed at the time. In
fact, that was the clue that helped me to identify the trouble - seeing
a completely unrelated workbook suddenly open itself "for no reason!"

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin used his keyboard to write :
"GS" wrote in message
...
It happens that armsiee formulated :
On Jan 12, 7:10 pm, GS wrote:
Just off the top of my head...

Where any of these problem formulas copy/pasted into the new workbook
from the test workbook? This would establish a link, I would thing,
causing the new workbook to call the UDF in the test workbook.

Were any of the sheets containing problem formulas copied from the test
workbook into the new workbook?

Is the new workbook being created from a template, OR is it the old
workbook "SavedAs"?

If anything that references a range object in the test workbook is
copied into another workbook, the definition copies as well. This
suggests to me the range objects in the test workbook are globally
defined. If, as you state, the UDFs are in the code module of each
sheet then any range references should be defined as local to that
sheet.

Global range defs travel with copy/paste,
replacing any global defs with the same name.
(Dependant on how the Q asked during paste was answered)

Local range defs are unique to their respective sheet,
and travel with it wherever it goes.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

To answer your questions no in all cases. I could understand there
being some form of link in place if the sheets were any related.
However, if I just do a file new workbook and start typing, it upsets
the "Test Workbook".

I double checked the UDF's and my initial statement that they were
linked to the code sheet was wrong, they are placed in generic code
modules within the workbook. I have tried limiting their scope to
PRIVATE but the same behaviour still manifests.

Regards

Simon

Does the UDF in question ref ActiveWorkbook or ThisWorkbook?



If you "start typing, and it upsets the test workbook" suggests to me that
you are doing something with Application.OnKey. I have a workbook that I
created under xl2003 that uses Application.OnKey to modify the tab / enter
keystroke behavior.

For reasons that I never did get to the bottom of, that workbook does not
always reset Application.OnKey when it loses focus ... and the modified
Application.OnKey processing invokes the code module that it is pointing to,
even if that workbook happens to be closed at the time. In fact, that was the
clue that helped me to identify the trouble - seeing a completely unrelated
workbook suddenly open itself "for no reason!"

What would really be helpful is the OP posting the code for the errant
UDF!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc