Unknown reason for #VALUE/Wrong data type returned by macro

[vin4559]

I am calculating values in a spreadsheet array using two variables that are the margins of the array and two variables that are fixed outside the array. Each cell calls a macro function:
=femalefitness04Small.xls!WNDf.WNDf($P$3,$P$4,$B4,C$3)

The function code is:

Option Explicit
Function WNDf(N, p, c, k)
Dim GSum, WeightedExpectedValue
Dim nm, xm As Integer
Dim s, ExpectedValue, Weight
GSum = 0
s = 1 - c
For nm = 0 To N
For xm = 0 To nm
ExpectedValue = (xm + (1 - k) * (N - nm)) / (xm + N - nm)
Weight = _
WorksheetFunction.BinomDist(nm, N, p, False) * _
WorksheetFunction.BinomDist(xm, nm, s, False)
WeightedExpectedValue = Weight * ExpectedValue
GSum = GSum + WeightedExpectedValue
Next xm
Next nm
WNDf = GSum
End Function

With the line "for nm = 0 to N", a #value error resulting from a wrong data type returns to all cells in the array. If the line is "for nm = 0 to N-1", the function returns what I think are correct values--no wrong data type. I can't figure out what changes in the first case (N, not N-1) that leads to the #VALUE. If you see what's causing the problem, I would appreciate your help. Thanks!

 

[PHV]

I don't think you may have a module and a function with same name (WNDf)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vin4559]

Thanks to PHV for responding. The function and others like it work except in this one case, so the answer does not solve the problem, which is the unique #VALUE response. Thanks for any help.... but I'll go away if you guys think this is too elementary.....

 

[PHV]

You get a division by zero when nm=N and xm=0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vin4559]

Thanks for that common sense. The question is answered but for one thing: stepping through the function with the debugger never produced the divide by zero error. I wonder why not?

 

[PHV]

stepping through the function with the debugger never produced the divide by zero error
But exhibited the #VALUE issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vin4559]

TO PHV-
Yes, #value appeared in the cells of the spreadsheet array. Since Excell's error checking provided the advice that the error was a result of a wrong data type, it wasn't obvious (though it should have been to me, as you have shown) that there was a division by zero producing the #value error.
Thanks for the attention.....