Excel VBA掷骰子概率模拟数组写入单元格出现#NA错误求助

问题根源 #

你遇到的问题完全来自Excel工作表函数在VBA中调用时的内置长度限制：

• WorksheetFunction.Transpose 函数在32位Excel中最多只能处理长度为65536的一维数组，部分兼容模式下限制更低，你传入的10万长度数组超过限制后，超出部分会被填充为#N/A，就是你看到的34465行后异常的原因。
• WorksheetFunction.Sum 处理VBA原生数组时也存在相同的长度限制，仅能统计前65536个元素的和，自然会导致最终计算的概率远低于理论值（该场景理论概率为4/36≈11.11%）。

修复方案 #

你只需要调整数组定义逻辑，完全避开调用有长度限制的工作表函数即可，推荐两种修改方式：

方案1：修改结果数组为二维结构，跳过转置步骤 #

纵向写入工作表的数组直接定义为二维结构就不需要转置，修改对应代码即可：

Sub ProbabilityMeyerArray()
    Dim i As Long
    Dim ArrayDices(1 To 100000, 1 To 2) As Variant
    ' 修改为二维数组，匹配工作表列的结构
    Dim ArrayResult(1 To 100000, 1 To 1) As Variant
    
    'Simulation
    For i = 1 To 100000
        ArrayDices(i, 1) = WorksheetFunction.RandBetween(1, 6)
        ArrayDices(i, 2) = WorksheetFunction.RandBetween(1, 6)

        If (ArrayDices(i, 1) = 1 And ArrayDices(i, 2) = 3) _
            Or (ArrayDices(i, 1) = 1 And ArrayDices(i, 2) = 2) _
            Or (ArrayDices(i, 1) = 3 And ArrayDices(i, 2) = 1) _
            Or (ArrayDices(i, 1) = 2 And ArrayDices(i, 2) = 1) Then
            
            ArrayResult(i, 1) = 1
        Else
            ArrayResult(i, 1) = 0
        End If
    Next i
    
    'print the values to cells
    Range("A1:B100000").Value = ArrayDices
    ' 去掉Transpose调用，直接写入
    Range("C1:C100000").Value = ArrayResult
    
    'Calculate the probability
    ' 改用VBA内置循环求和，避开工作表函数限制
    Dim sumResult As Long
    For i = 1 To 100000
        sumResult = sumResult + ArrayResult(i, 1)
    Next
    Probability = sumResult / 100000
    
    MsgBox "The Probability is " & Probability
End Sub

方案2：直接累加命中次数，省略不必要的数组存储 #

如果你不需要把所有结果都写入单元格，直接在循环中累加命中次数效率更高，完全不会触发长度限制：

Sub ProbabilityMeyerOptimized()
    Dim i As Long
    Dim hitCount As Long
    Dim dice1 As Integer, dice2 As Integer
    
    For i = 1 To 100000
        dice1 = WorksheetFunction.RandBetween(1, 6)
        dice2 = WorksheetFunction.RandBetween(1, 6)
        If (dice1 = 1 And (dice2 = 2 Or dice2 = 3)) Or _
           (dice2 = 1 And (dice1 = 2 Or dice1 = 3)) Then
           hitCount = hitCount + 1
        End If
    Next i
    
    MsgBox "The Probability is " & hitCount / 100000
End Sub

内容的提问来源于stack exchange，提问作者Avocado