UDF no looping and returning answer for worksheet function

[SerenityNetworks]

I'm trying to return the answer to the formula for BINOMDIST to the worksheet. However, unless I comment out the "cdf" row, the macro does not loop. I don't know what I'm doing wrong here. Any help will be appreciated.

Thanks,
Andrew

Function AllowedErrors(Size As Double, Conf As Double, p As Double) As Long
  Dim cdf           As Double   ' cumulative distribution function
  Dim nSize         As Double   ' calculation counter
  Dim nErrors       As Double   ' counter for errors
  If Conf <= 0# Or Conf >= 1# Then Exit Function
  If p <= 0# Or p >= 1# Then Exit Function
  If nErrors = 0& Then nErrors = 1 ' sets initial value of nErrors to 1
  With WorksheetFunction
        Do
          cdf = .BinomDist(nErrors, Conf, p, True)
          Debug.Print (nErrors) & " | " & (Conf) & " | " & (p) & " | " & (cdf)
          nErrors = nErrors + 1&
        Loop While nErrors < Size + 1
  End With
End Function

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Accuracy
[/TD]
[TD]75.00%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Confidence
[/TD]
[TD]50.00%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Allowed Errors
[/TD]
[TD]Sample Size
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]A5: =AllowedErrors(B5, $B$2, 1-$B$1)
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]A6: =AllowedErrors(B6, $B$2, 1-$B$1)
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]11
[/TD]
[TD]A7: =AllowedErrors(B7, $B$2, 1-$B$1)
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]A8: =AllowedErrors(B8, $B$2, 1-$B$1)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SerenityNetworks]

Ugh! Never mind for the moment. I realize I'm populating the binomdist function incorrectly. (I also forgot to add "AllowedErrors = cdf" after the End With.

I need to populate the binomdist function correctly and then stop the loop when the required accuracy is met.

I'm not too good at this, so if someone wants to help me out then I won't turn down the assistance. Otherwise, I'll work on it.

Thanks,
Andrew

 

[SerenityNetworks]

Okay, I was able to get it working. However, I'm not a coder. If someone would care to check the code to see if it could use some improvement then I'd be appreciative.

Thanks,
Andrew

Function AllowedErrors(Size As Double, Conf As Double, p As Double) As Long
  Dim cdf           As Double   ' cumulative distribution function
  Dim nErrors       As Double   ' counter for errors
  Conf = 1 - Conf   ' converts required confidence to form of value binomdist function outputs
  p = 1 - p         ' converts required accuracy to "probability" value binomdist function is expecting
  If Conf <= 0# Or Conf >= 1# Then Exit Function
  If p <= 0# Or p >= 1# Then Exit Function
  If nErrors = 0& Then nErrors = 1 ' sets initial value of nErrors to 1
  With WorksheetFunction
        Do Until cdf > Conf
          'cdf = .BinomDist(Allowed Errors, Sample Size, Accuracy (p), True)
          cdf = WorksheetFunction.BinomDist(nErrors, Size, p, True)
          prevErrors = nErrors
          nErrors = nErrors + 1
          'Debug.Print Round(cdf, 4) & " | " & prevErrors
         Loop
  End With
  AllowedErrors = nErrors - 2
  'Debug.Print "=binomdist(" & AllowedErrors & "," & Size & "," & p & ",TRUE)"
End Function