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