我在Excel 2013工作簿中有一个VBA函数,该函数根据泊松分布计算p值。当下面代码中的 事件 变量超过1029时,我会收到运行时错误 '1004':无法获取WorksheetFunction类的Combin属性。只要事件1和事件2的总和保持在1029或以下,就没有问题,宏就可以正确执行。
是否有任何方法可以使其在更高的数字下正常运行?非常感谢您的任何帮助!
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
1.79769313486231E+308
。你知道你计算出来的结果是否会比这个更大吗?如果是,那就是导致错误的原因。 - tigeravatar