CODEWITHSUNDEEP
vbaexcel
James Chen
James Chen

Reputation: 237

#Value in my custom function

I inhereited a custom function on one of my sheets. Its basically doing a strightline interp on 1 of 3 tables depending on bunch of variables. Some reason everytime i open another workbook all my outputs turn into #Value errors. If i reset the macro then it works. Weird. Please help!

Function INTERP(A, CURVENUM)

COL1 = CURVENUM * 3 - 2
COL2 = COL1 + 1


NUMPOINTS = Range("INV").Cells(-1, COL2)
'A = 80
MAXINV = Range("INV").Cells(1, COL1)
MININV = Range("INV").Cells(NUMPOINTS, COL1)

If A >= MAXINV Then
M = (Range("INV").Cells(2, COL2) - Range("INV").Cells(1, COL2)) / (Range("INV").Cells(2, COL1) - Range("INV").Cells(1, COL1))
Q = Range("INV").Cells(1, COL2) - (Range("INV").Cells(1, COL1) - A) * M
End If

If A <= MININV Then
M = (Range("INV").Cells(NUMPOINTS, COL2) - Range("INV").Cells(NUMPOINTS - 1, COL2)) / (Range("INV").Cells(NUMPOINTS, COL1) - Range("INV").Cells(NUMPOINTS - 1, COL1))
Q = (A - Range("INV").Cells(NUMPOINTS, COL1)) * M + Range("INV").Cells(NUMPOINTS, COL2)
End If


For I = 1 To NUMPOINTS - 1
X1 = Range("INV").Cells(I, COL1)
X2 = Range("INV").Cells(I + 1, COL1)

If A <= X1 And A > X2 Then
N2 = Range("INV").Cells(I + 1, COL2)
N1 = Range("INV").Cells(I, COL2)
D2 = Range("INV").Cells(I + 1, COL1)
D1 = Range("INV").Cells(I, COL1)
M = (N2 - N1) / (D2 - D1)  'SLOPE

Q = N2 - (D2 - A) * M
INTERP = Q
Exit Function
End If

Next I

If A = MAXINV Then Q = Range("INV").Cells(NUMPOINTS, COL2)

INTERP = Q

End Function

Upvotes: 0

Views: 80

Answers (1)

Charles Williams
Charles Williams

Reputation: 23520

There are 2 problems with this function:

1) It will look for Range("Inv") in whatever happens to be the active workbook (which will change when you open another workbook). change all your references to Range("Inv") to Application.caller.parent.parent.Names("Inv").ReferstoRange

2) your function will not recalculate when INV changes unless you make the function volatile or add INV as a parameter to the function

Upvotes: 1

Related Questions