yuor function explicitly looks up named ranges. Do these exist on the active
sheet of the other workbooks?


this line is worng:
Dim Vara, Varb, Varc As Long

should be
Dim Vara As Long
Dim Varb As Long
Dim Varc As Long

or
Dim Vara As Long, Varb As Long, Varc As Long

Also, your function doesn't use Varb...is that an oversight?


What is the purpose of this function?









"caroline" wrote:

I create a function called BassDiff1()
I put it in General/Standard module:VBAProject\Modules\Module1
I an trying to use it in a worsheet formula: example: =BassDiff1(L37)
It works fine except when I start working on another workbook and come back,
it displays #VALUE!
this is the function:
Any help greatly appreciated
thanks

Option Explicit
Public NDataPoints As Integer
Dim Vara, Varb, Varc As Long
Public InputDataCell1, SummaryOutput, YVar As Range

Function BassDiff1(num)

'VARIABLES
Dim m1 As Variant
Dim p1 As Variant
Dim q1 As Variant

NDataPoints = Range("NDataPoints").Value
Set YVar = Range(Range("YVarCell1"), Range("YVarCell1").Offset(NDataPoints -
1, 0))
Vara = Range("Vara").Value
Varb = Range("Varb").Value
Varc = Range("Varc").Value

m1 = Application.WorksheetFunction.Sum(YVar)
p1 = Varc / m1
q1 = p1 + Vara

'FUNCTION
BassDiff1 = p1 * (m1 - num) + (q1 * (num / m1) * (m1 - num))

End Function


--
caroline