Formula incorrecting showing 0 as result

[BigJimmer]

I'm using Excel 2000. In a particular file only, when I enter a UDF as a
formula, and the dialog showing the parameters is displayed, I see that the
function will produce a correct, non-zero result. When I click OK to save
the formula, 0 is displayed.

When I try adding a breakpoint to the first line of code in the UDF, it is
never reached.

I then enter a formula into another cell, it can be anything including the
same UDF as used in the first cell. This time when I click OK, the
breakpoint in the UDF is reached (twice when this 2nd cell uses the same UDF
as the original formula), and then I see the correct results displayed in
both of the cells.

If I then edit the original formula again (and not change anything), it
again does not braeak at the beginning of the function, and shows 0. Edit
the 2nd cell, and both will then display correctly.

The value does not recompute when I change the cell values that are used for
the UDF parameters either, even though I have the calculation set to
automatic.

It's very strange. What could be causing this?

Thanks!

 

[Patrick Molloy]

can you please show use the code for the UDF?

 

[BigJimmer]

Here is the function, and the parameters I used.

PayRange - A1:B120 Column A should be dates in descending order, column B
would be amounts (any posititve, non-zero numbers)

AveragePeriod - cell with the value of 60
PeriodType - cell with the value of M
FAPE_Type - cell with the value of T
ProjRate - left to default of 0

Function FAPE(ByVal PayRange As Range, _
ByVal AveragePeriod As Integer, _
ByVal PeriodType As String, _
ByVal FAPE_Type As String, _
Optional ByVal ProjRate As Single = 0) As Double

Dim PayAllDict As Scripting.Dictionary

Dim bInputError As Boolean

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Cols As Integer
Dim Rows As Integer
Dim PriorPeriod As Integer
Dim iMonths As Integer

Dim LimitYear As Long

Dim AnnualAmt As Double
Dim dAnnualLimit As Double
Dim TotalComp As Double
Dim FAP As Double

Dim strDate As String

5 On Error GoTo FAPE_Error

10 bInputError = True
15 Cols = PayRange.Columns.Count
20 Rows = PayRange.Rows.Count
25 Set PayAllDict = New Scripting.Dictionary

'Copy data inout to the dictionary. The input range expects the data to
have a column of dates, then
'a column of amounts. This can be repeated for multiple sets of
columns (eg, year 1 is first 2 columns,
'year 2 is columns 3 - 4, etc,)
30 For i = 1 To (Cols - 1) Step 2
35 For j = 1 To Rows
40 If Not IsDate(PayRange(j, i).Value) Or Not IsNumeric(PayRange(j,
i + 1).Value) Then
45 FAPE = 0
50 Exit For
55 End If
60 PayAllDict.Add Key:=PayRange(j, i).Value, Item:=PayRange(j, i +
1).Value
65 bInputError = False
70 Next j
75 Next i

80 If bInputError = False Then
85 PriorPeriod = 0
90 FAP = 0
95 For i = 0 To (PayAllDict.Count - AveragePeriod) Step 1
100 k = 0
105 TotalComp = 0
110 If PeriodType = "A" Then
115 For j = 0 To (AveragePeriod - 1)
120 AnnualAmt = PayAllDict.Items(i + j)
125 If FAPE_Type = "T" Then
130 TotalComp = TotalComp + AnnualAmt
135 Else
'Insert 401A(17) limitation logic here
140 End If
145 k = k + 1
150 Next j
155 ElseIf PeriodType = "M" Then
160 j = 0
165 While j <= AveragePeriod - 1
170 LimitYear = Year(PayAllDict.Keys(i + j))
175 If (i + j + 11) > PayAllDict.Count - 1 Then
180 k = PayAllDict.Count - 1 - i
185 Else
190 k = j + 11
195 End If
200 For iMonths = j To k
205 AnnualAmt = AnnualAmt + PayAllDict.Items(i + iMonths)
210 Next iMonths
215 j = j + 12
220 If FAPE_Type = "T" Then
225 TotalComp = TotalComp + AnnualAmt
230 Else
'Insert 401A(17) limitation logic here
235 End If
240 AnnualAmt = 0
245 k = k + 1
250 Wend
255 End If
'limit comp
260 If k >= PriorPeriod And TotalComp > FAP Then
265 FAP = TotalComp
270 PriorPeriod = k
275 End If
280 Next i
285 If FAP > 0 And PriorPeriod > 0 Then
290 FAPE = FAP / PriorPeriod
295 End If
300 End If

FAPE_Exit:

310 Set PayAllDict = Nothing

315 On Error GoTo 0
320 Exit Function

FAPE_Error:

325 'Call Error_Msg("FAPE", "FAPE")
330 Resume FAPE_Exit

End Function