vba - 创建一个循环来迭代所有成功/失败事件的组合

Question

我正在编写一个代码来计算给定一定数量的成功的概率。我在编写一个 for 循环时遇到了麻烦，这样我就可以将一些事件归类为失败，而将一些事件归类为成功。我能够为 Case Is = 1 做到这一点，但我没有想到任何聪明的方法来迭代其他案例，这种方式可以在用户输入的成功数量下经历成功和失败的所有组合。

我已经尝试过 Case=2，但想不出记录失败的方法。在 Case=1 中，我通过使用模数来解决它。

Function CustomBinomial(MoveType, SuccessAmount) As Double

'Variables to record which events succeed and fail
    Dim FirstEvent As Double
    Dim SecondEvent As Double
    Dim ThirdEvent As Double
    Dim FourthEvent As Double
    Dim FifthEvent As Double
    Dim SixthEvent As Double

'Rows the probability values are in
ProbSuccessArray = Array(15, 19, 23, 27, 31, 35)
    
    Select Case SuccessAmount
        Case Is = 0
            'Record 0 events as successful
        Case Is = 1
            For i = 0 To 5
                'Record 1 event as successful
                FirstEvent = ProbSuccessArray(i)
                
                'Record 5 events as failures
                SecondEvent = ProbSuccessArray((i + 1) Mod 6)
                ThirdEvent = ProbSuccessArray((i + 2) Mod 6)
                FourthEvent = ProbSuccessArray((i + 3) Mod 6)
                FifthEvent = ProbSuccessArray((i + 4) Mod 6)
                SixthEvent = ProbSuccessArray((i + 5) Mod 6)
                
                CustomBinomial = CustomBinomial + Cells(FirstEvent, 3).Value * (1 - Cells(SecondEvent, 3).Value) _
                                * (1 - Cells(ThirdEvent, 3).Value) * (1 - Cells(FourthEvent, 3).Value) _
                                * (1 - Cells(FifthEvent, 3).Value) * (1 - Cells(SixthEvent, 3).Value)
                
            Next i
        Case Is = 2
            For i = 0 To 5
                For j = 1 To 5
                    'Record 2 events as successful
                    If i = j Then GoTo Continue
                    FirstEvent = ProbSuccessArray(i)
                    SecondEvent = ProbSuccessArray(j)
                    
                    'Record 4 events as failures
                    'code here
                    
                    'CustomBinomial = CustomBinomial + Successes * Failures
Continue:
                Next j
            Next i
        Case Is = 3
        'Record 3 events as successful
        Case Is = 4
        'Record 4 events as successful
        Case Is = 5
        'Record 5 events as successful
        Case Is = 6
        'Record 6 events as successful
                
                    
    End Select

标签： vba