VBA Function returns #VALUE! when working on another book

[caroline]

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

 

[Charles Williams]

Here are some suggestions:

Dim Vara, Varb, Varc As Long defines Vara and Varb as variant not long,
which is probably not what you meant

It's not a good idea to reference Defined Names or ranges inside a UDF when
they have not been passed in as parameters: you may get unexpected results
because Excel's calculation engine does not expect this. I suggest you make
all the ranges the UDF references into parameters for the UDF.

I don't know why you are declaring some of the variables as Public, some as
Module scope and others inside the function, but I suggest you move all your
declarations to inside the function.

Using Application.WorksheetFunction.SUM inside a UDF will sometimes return
#Value the first time its recalculated: try pressing Ctrl/Alt/F9.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Patrick Molloy]

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?