运行时错误'1004'：无法获取WorksheetFunction类的Combin属性 [英] run-time error '1004': Unable to get the Combin property of the WorksheetFunction class

问题描述

我在Excel 2013中的工作簿中具有VBA功能，可根据泊松分布计算p值。当以下代码中的事件变量超过1029时，我得到运行时错误'1004'：无法获取WorksheetFunction类的组合属性。只要事件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）。值
 days2 = Sheet1.Range（C7）。value 
 
如果events2> 0然后
 events = events1 + events2 
 p_c = days1 /（days1 + days2）
 p_lo = 0 
 p_hi = 0 
对于i = 0到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 
对于i = 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_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 Sub 
  / pre> 
 
解决方案
在Excel 2013中，可以返回的最大正数b一个公式是1.7976931348623158e + 308在你描述的情况下，你的数字很可能达到一个值。您可以通过尝试使用变量的因子执行COMBIN函数来确认，并查看是否返回#NUM错误。
 
 
 
一个可能的解决方案是安装 XNumbers加载项并使用其xComb_big功能。 XNumbers是一款免费的工具，可以使用大量的数据和更高的精度。据说在97-2010版本的Excel版本上工作。我不知道它是否可以与2013一起工作。但是如果可以，它可以被配置为使用指数高达2147000000和高达32760有效数字（你可能不想要那么多，但它是可配置的，默认值安装次数较少）。
 
I have VBA function within a workbook in Excel 2013 that calculates a p-value based on a poisson distribution.  When the events variable in the code below exceeds 1029 I get run-time error '1004': Unable to get the Combin property of the WorksheetFunction class. So long as the sum of events1 and events2 remains at 1029 or below there are no issues and the macro executes properly.

Is anyone aware of a way to get this to function properly with higher numbers? Any guidance is greatly appreciated!
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

解决方案
In Excel 2013, the largest positive number that can be returned by a formula is 1.7976931348623158e+308  It is likely that your numbers work out to a value greater than that under the circumstances you describe.  You can confirm this by trying to execute the COMBIN function on the worksheet using the factors for the variables and seeing if it returns a #NUM error.

One possible solution is to install the XNumbers add-in and use its xComb_big function. XNumbers is a free tool that allows the use of large numbers and increased precision. It is said to work on versions of Excel from 97-2010.  I don't know if it will work with 2013.  But if it does, it can be configured to work with exponents up to 2147000000 and up to 32760 significant digits (you may not want that much, but it is configurable, and the default installation has fewer digits).

                        
这篇关于运行时错误'1004'：无法获取WorksheetFunction类的Combin属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！