On Jan 21, 4:57*pm, GS wrote:
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.

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