Geek Answers Handbook

Runtime Error "1004": Unable to get Combin property of WorksheetFunction class

I have a VBA function in a workbook in Excel 2013 that calculates a p-value based on a poisson distribution. When the event variable in the code below exceeds 1029, I get a "1004" runtime error: I cannot get the Combin property of the WorksheetFunction class . As long as the sum of events1 and events2 remains at 1029 or lower, there are no problems, and the macro runs properly.

Does anyone know how to make this function properly with higher numbers? Any recommendations are welcome!

Sub poisson_meansB()
    Dim events1 As Long
    Dim events2 As Long
    Dim days1 As Long
    Dim days2 As Long

    events1 = Sheet1.Range("B6").Value
    events2 = Sheet1.Range("C6").Value
    days1 = Sheet1.Range("B7").Value
    days2 = Sheet1.Range("C7").Value

    If events2 > 0 Then
    events = events1 + events2
    p_c = days1 / (days1 + days2)
    p_lo = 0
    p_hi = 0
    For i = 0 To events1
        poisson_p_value_term = Application.WorksheetFunction.Combin(events, i) * Application.WorksheetFunction.Power(p_c, i) * Application.WorksheetFunction.Power(1 - p_c, events - i)
        p_lo = p_lo + poisson_p_value_term
    Next i
    For i = events1 To events
        poisson_p_value_term = Application.WorksheetFunction.Combin(events, i) * Application.WorksheetFunction.Power(p_c, i) * Application.WorksheetFunction.Power(1 - p_c, events - i)
        p_hi = p_hi + poisson_p_value_term
    Next i
    p = Application.WorksheetFunction.Min(2 * p_lo, 2 * p_hi)
    Sheet1.Range("C13") = p
    Else
    Sheet1.Range("C13") = "-"
    End If
End Sub
+3
source share
2 answers

Excel 2013 , , 1.7976931348623158e + 308 , , , . , COMBIN , #NUM.

XNumbers xComb_big. XNumbers - , . , Excel 97-2010. , 2013 . , 2147000000 32760 ( , , ).

+2

, , ​​ : events p_c p_lo p_hi i poisson_p_value_term

-1

More articles:


All Articles