User Defined Function being called by changes to an independent workbook

[armsiee]

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

 

[GS]

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.

 

[armsiee]

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

 

[GS]

It happens that armsiee formulated :

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?

 

[Clif McIrvin]

It happens that armsiee formulated :

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!"

 

[GS]

Clif McIrvin used his keyboard to write :

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!

 

[Clif McIrvin]

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

No kidding. This guessing game isn't all that easy <g>.

 

[armsiee]

No kidding. This guessing game isn't all that easy <g>.

I hadn't posted the UDF as my original post was getting so long in the
first place and as stated I know what the problem is with the UDF in
the context of this issue. What I don't understand is why this UDF is
being called by an event on a completely seperate workbook but as
requested ....

Function MVA_Factor_Function(MVA_Expected_Return_Fac,
MVA_Actual_Return_Fac, _
MVA_Minimum, MVA_Maximum,
MVA_Max_Multiplier)

'Set variables to be used in the calculation
Dim MVA_Ratio As Double

'Variables picked up from the input sheet
MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor")
MVA_Actual_Return = Range("MVA_Actual_Return_Factor")
MVA_Maximum = Range("MVA_Max")
MVA_Max_Multiplier = Range("MVA_Max_Mult")
MVA_Minimum = Range("MVA_Min")

'Calculate MVA as a ratio of the Actual return factor and the expected
return factor
MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 +
MVA_Expected_Return_Fac)

'MVA is applied under the following conditions
If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio
If MVA_Ratio > MVA_Maximum Then MVA_Factor_Function = MVA_Ratio *
MVA_Max_Multiplier
If MVA_Ratio > MVA_Minimum And MVA_Ratio < MVA_Maximum Then
MVA_Factor_Function = 1

 

[Clif McIrvin]

No kidding. This guessing game isn't all that easy <g>.

I hadn't posted the UDF as my original post was getting so long in the
first place and as stated I know what the problem is with the UDF in
the context of this issue. What I don't understand is why this UDF is
being called by an event on a completely seperate workbook but as
requested ....

Function MVA_Factor_Function(MVA_Expected_Return_Fac,
MVA_Actual_Return_Fac, _
MVA_Minimum, MVA_Maximum,
MVA_Max_Multiplier)

'Set variables to be used in the calculation
Dim MVA_Ratio As Double

'Variables picked up from the input sheet
MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor")
MVA_Actual_Return = Range("MVA_Actual_Return_Factor")
MVA_Maximum = Range("MVA_Max")
MVA_Max_Multiplier = Range("MVA_Max_Mult")
MVA_Minimum = Range("MVA_Min")

'Calculate MVA as a ratio of the Actual return factor and the expected
return factor
MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 +
MVA_Expected_Return_Fac)

'MVA is applied under the following conditions
If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio
If MVA_Ratio > MVA_Maximum Then MVA_Factor_Function = MVA_Ratio *
MVA_Max_Multiplier
If MVA_Ratio > MVA_Minimum And MVA_Ratio < MVA_Maximum Then
MVA_Factor_Function = 1


---------

Is this the entire UDF? Your post ended rather abruptly.

Does this behavior manifest on any workstation, or only some?

What I don't understand is why this UDF is
being called by an event on a completely seperate workbook but as
requested ....

Have you attempted to pare the complexity down to the simplest
demonstration of the problem that you can achieve? That is, can you
create a brand new workbook, copy / paste the UDF into it, and reproduce
the behavior?

I'm still puzzled as to precisely what you mean by "event" .... from
your earlier description of the problem, I understand that you can open
Workbook "A" (containing the UDFs, tables, etc) and all works as
expected, then you can File | New (Create empty Workbook "B"), and when
you begin typing into Workbook "B" the UDF is called. Is this correct?
From the code you posted, I see nothing at all to explain what you are
seeing. In my (admittedly limited) experience, the only mechanisms I
know to invoke a UDF from a worksheet are a) by an explicit call from a
formula in a cell, b) through a "command button" - whether a control or
object on a form, in the worksheet,
or from a toolbar, c) using "Application.OnKey" to modify keyboard
processing or d) using a UDF in a conditional formatting formula.

I'm wondering if something "in the innards" of that workbook has become
"confused" and the solution will be to rebuild the workbook "from
scratch." I don't have enough experience to know of any method for
doing that other than "brute force" ... If I had to try, I'd try to
export/import as .csv, and copy/paste the code manually.

 

[GS]

Clif McIrvin laid this down on his screen :

I'm wondering if something "in the innards" of that workbook has become
"confused" and the solution will be to rebuild the workbook "from scratch."
I don't have enough experience to know of any method for doing that other
than "brute force" ... If I had to try, I'd try to export/import as .csv, and
copy/paste the code manually.

You could try Rob Bovey's VBA Code Cleaner addin...
http://www.appspro.com/Utilities/CodeCleaner.htm

 

[Clif McIrvin]

Clif McIrvin laid this down on his screen :

You could try Rob Bovey's VBA Code Cleaner addin...
http://www.appspro.com/Utilities/CodeCleaner.htm

--
Garry

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

Thanks! I bookmarked that.

 

[armsiee]

I hadn't posted the UDF as my original post was getting so long in the
first place and as stated I know what the problem is with the UDF in
the context of this issue.  What I don't understand is why this UDF is
being called by an event on a completely seperate workbook but as
requested ....

Function MVA_Factor_Function(MVA_Expected_Return_Fac,
MVA_Actual_Return_Fac, _
                                MVA_Minimum, MVA_Maximum,
MVA_Max_Multiplier)

'Set variables to be used in the calculation
Dim MVA_Ratio As Double

'Variables picked up from the input sheet
MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor")
MVA_Actual_Return = Range("MVA_Actual_Return_Factor")
MVA_Maximum = Range("MVA_Max")
MVA_Max_Multiplier = Range("MVA_Max_Mult")
MVA_Minimum = Range("MVA_Min")

'Calculate MVA as a ratio of the Actual return factor and the expected
return factor
MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 +
MVA_Expected_Return_Fac)

'MVA is applied under the following conditions
If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio
If MVA_Ratio > MVA_Maximum Then MVA_Factor_Function = MVA_Ratio *
MVA_Max_Multiplier
If MVA_Ratio > MVA_Minimum And MVA_Ratio < MVA_Maximum Then
MVA_Factor_Function = 1

---------

Is this the entire UDF?  Your post ended rather abruptly.

Does this behavior manifest on any workstation, or only some?


Have you attempted to pare the complexity down to the simplest
demonstration of the problem that you can achieve?  That is, can you
create a brand new workbook, copy / paste the UDF into it, and reproduce
the behavior?

I'm still puzzled as to precisely what you mean by "event" .... from
your earlier description of the problem, I understand that you can open
Workbook "A" (containing the UDFs, tables, etc) and all works as
expected, then you can File | New (Create empty Workbook "B"), and when
you begin typing into Workbook "B" the UDF is called.  Is this correct?
From the code you posted, I see nothing at all to explain what you are
seeing.  In my (admittedly limited) experience, the only mechanisms I
know to invoke a UDF from a worksheet are a) by an explicit call from a
formula in a cell, b) through a "command button" - whether a control or
object on a form, in the worksheet,
or from a toolbar, c) using "Application.OnKey" to modify keyboard
processing or d) using a UDF in a conditional formatting formula.

I'm wondering if something "in the innards" of that workbook has become
"confused" and the solution will be to rebuild the workbook "from
scratch."  I don't have enough experience to know of any method for
doing that other than "brute force" ... If I had to try, I'd try to
export/import as .csv, and copy/paste the code manually.

I'm sorry if I haven't made myself clear but have kept trying to say
that I don't believe the UDF is the issue (which is why i did not post
it originally) it is a sympton of the problem. It appears that the
"Test Sheet" recalculates itself whenever anything else happens within
that instance of Excel, eg, open another spreadhseet, type into a
blank spreadhseet and thus calls that UDF which produces errors (as it
is not specific with its Range declarations) sorry just missed the End
Function on the code snippet. And yes the same behaviour occurs on a
colleagues workstation.

Good idea, regarding code cleaner i will give it a whirl.

 

[GS]

armsiee explained :

I'm sorry if I haven't made myself clear but have kept trying to say
that I don't believe the UDF is the issue (which is why i did not post
it originally) it is a sympton of the problem. It appears that the
"Test Sheet" recalculates itself whenever anything else happens within
that instance of Excel, eg, open another spreadhseet, type into a
blank spreadhseet and thus calls that UDF which produces errors (as it
is not specific with its Range declarations) sorry just missed the End
Function on the code snippet. And yes the same behaviour occurs on a
colleagues workstation.

What I don't see in your UDF is how it knows NOT to run on any workbook
since there's no explicit ref to the workbook that uses it. You ref the
Range object in general and so Excel takes that to be a range on the
active sheet. IOW, your UDF (as written) should work with any open
workbook. I think <IMO> you need to fully qualify the workbook that
it's to work with so Excel knows when/where it should be used. For
example, wrap your code in a With...End With construct something
like...

Function MVA_Factor_Function(MVA_Expected_Return_Fac, _
MVA_Actual_Return_Fac, _
MVA_Minimum, MVA_Maximum, _
MVA_Max_Multiplier)

'Set variables to be used in the calculation
Dim MVA_Ratio As Double

With ThisWorkbook.ActiveSheet
'Variables picked up from the input sheet
MVA_Expected_Return_Fac = .Range("MVA_Expected_Return_Factor")
MVA_Actual_Return = .Range("MVA_Actual_Return_Factor")
MVA_Maximum = .Range("MVA_Max")
MVA_Max_Multiplier = .Range("MVA_Max_Mult")
MVA_Minimum = .Range("MVA_Min")
'**Note the dot before Range.
End With

'Calculate MVA as a ratio of the Actual return factor...
'other code here...
End Function

Also, if it happens whenever another workbook is activated then there
must be some code in an event that's causing it to execute, OR the test
workbook uses a similar named function also not restricted to that
workbook. Surely this would cause conflict that would cause unexpected
results.

 

[armsiee]

armsiee explained :


What I don't see in your UDF is how it knows NOT to run on any workbook
since there's no explicit ref to the workbook that uses it. You ref the
Range object in general and so Excel takes that to be a range on the
active sheet. IOW, your UDF (as written) should work with any open
workbook. I think <IMO> you need to fully qualify the workbook that
it's to work with so Excel knows when/where it should be used. For
example, wrap your code in a With...End With construct something
like...

  Function MVA_Factor_Function(MVA_Expected_Return_Fac, _
                               MVA_Actual_Return_Fac, _
                               MVA_Minimum, MVA_Maximum, _
                               MVA_Max_Multiplier)

    'Set variables to be used in the calculation
    Dim MVA_Ratio As Double

    With ThisWorkbook.ActiveSheet
      'Variables picked up from the input sheet
      MVA_Expected_Return_Fac = .Range("MVA_Expected_Return_Factor")
      MVA_Actual_Return = .Range("MVA_Actual_Return_Factor")
      MVA_Maximum = .Range("MVA_Max")
      MVA_Max_Multiplier = .Range("MVA_Max_Mult")
      MVA_Minimum = .Range("MVA_Min")
      '**Note the dot before Range.
    End With

    'Calculate MVA as a ratio of the Actual return factor...
    'other code here...
  End Function

Also, if it happens whenever another workbook is activated then there
must be some code in an event that's causing it to execute, OR the test
workbook uses a similar named function also not restricted to that
workbook. Surely this would cause conflict that would cause unexpected
results.

--
Garry

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

Yes thanks Garry, I have had similar thoughts, however this workbook
has been written like this and functioning for nearly 5 years with the
UDF code exactly the same (i've gone back and double checked through
the version history)

I have searched through the workbook to see if the UDF is called
anywhere else and the only place is the explicit reference within the
sheet, which leads me to believe that something else has happened to
the Workbook.

I can work around it, thanks for your efforts.