Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula incorrecting showing 0 as result
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula incorrecting showing 0 as result
can you please show use the code for the UDF?
"BigJimmer" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula incorrecting showing 0 as result
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 "Patrick Molloy" wrote: can you please show use the code for the UDF? "BigJimmer" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula result showing 0 | Excel Discussion (Misc queries) | |||
Macro Showing Formula and Result | Excel Programming | |||
VLOOKUP result is not showing up - only the formula | Excel Worksheet Functions | |||
formula in cell not showing result | Excel Worksheet Functions | |||
Formula result not showing in cell | Excel Worksheet Functions |