VBA生成带排除值的区间随机数：求更优实现方案

针对你需要在区间内生成随机数但排除特定值的需求，以下是几种比暴力Do While循环更高效、且能保证统计特性的方案：

方法1：预生成合法列表，随机取索引 #

适合区间不大或排除项较多的场景，先一次性构建所有合法数值的数组，后续直接随机取数组索引。这种方法完全避免循环，每个合法值被选中的概率均等，统计特性和原生Rnd完全一致。

Function GetRandomExclude(lowerBound As Integer, upperBound As Integer, ParamArray excludeValues() As Variant) As Integer
    Dim validNumbers() As Integer
    Dim i As Integer, count As Integer
    
    ' 统计合法数值的数量
    count = 0
    For i = lowerBound To upperBound
        If Not IsInArray(i, excludeValues) Then
            count = count + 1
        End If
    Next i
    
    ' 初始化合法数值数组
    ReDim validNumbers(1 To count)
    count = 1
    For i = lowerBound To upperBound
        If Not IsInArray(i, excludeValues) Then
            validNumbers(count) = i
            count = count + 1
        End If
    Next i
    
    ' 随机选取数组中的元素
    GetRandomExclude = validNumbers(Int(Rnd * UBound(validNumbers)) + 1)
End Function

' 辅助函数：检查数值是否在排除列表中
Function IsInArray(val As Integer, arr As Variant) As Boolean
    Dim elem As Variant
    For Each elem In arr
        If elem = val Then
            IsInArray = True
            Exit Function
        End If
    Next elem
    IsInArray = False
End Function

优点：统计特性完美，后续调用效率极高（O(1)）；缺点：首次调用需要遍历区间构建数组，超大区间（比如1-1000000）会占用较多内存。

方法2：映射法（将排除项映射到合法值） #

适合区间大但排除项少的场景，核心是把需要排除的数值映射到区间末尾的合法值，生成随机数时如果命中排除项，直接返回映射后的合法值，无需循环重试。

Function GetRandomExcludeMap(lowerBound As Integer, upperBound As Integer, ParamArray excludeValues() As Variant) As Integer
    Dim excludeDict As Object
    Dim mappedNum As Integer, randomNum As Integer
    Dim i As Integer, currentMap As Integer
    
    Set excludeDict = CreateObject("Scripting.Dictionary")
    currentMap = upperBound
    
    ' 为每个排除项分配对应的合法映射值
    For i = upperBound To lowerBound Step -1
        If Not excludeDict.Exists(i) And Not IsInArray(i, excludeValues) Then
            For Each mappedNum In excludeValues
                If Not excludeDict.Exists(mappedNum) Then
                    excludeDict(mappedNum) = i
                    currentMap = currentMap - 1
                    Exit For
                End If
            Next mappedNum
        End If
    Next i
    
    ' 生成缩小范围后的随机数（原区间长度减去排除项数量）
    randomNum = Int((upperBound - lowerBound + 1 - UBound(excludeValues) - 1) * Rnd + lowerBound)
    
    ' 命中排除项则返回映射值，否则直接返回
    If excludeDict.Exists(randomNum) Then
        GetRandomExcludeMap = excludeDict(randomNum)
    Else
        GetRandomExcludeMap = randomNum
    End If
End Function

' 复用辅助函数IsInArray
Function IsInArray(val As Integer, arr As Variant) As Boolean
    Dim elem As Variant
    For Each elem In arr
        If elem = val Then
            IsInArray = True
            Exit Function
        End If
    Next elem
    IsInArray = False
End Function

优点：无需预生成完整列表，内存占用低，每次调用效率O(1)；缺点：映射逻辑稍复杂，适合排除项数量远小于区间长度的场景。

方法3：优化版过滤法（集合快速判断） #

如果偏好类似循环的逻辑，但想大幅提升效率，可以用Scripting.Set存储排除项，将「是否命中排除项」的判断从O(n)降到O(1)，同时设置最大重试次数避免极端情况。

Function GetRandomExcludeOptimized(lowerBound As Integer, upperBound As Integer, ParamArray excludeValues() As Variant) As Integer
    Dim excludeSet As Object
    Dim randomNum As Integer, retryCount As Integer
    Const MAX_RETRIES As Integer = 1000
    
    Set excludeSet = CreateObject("Scripting.Set")
    ' 将排除项加入集合
    For Each val In excludeValues
        excludeSet.Add val
    Next val
    
    retryCount = 0
    Do
        randomNum = Int((upperBound - lowerBound + 1) * Rnd + lowerBound)
        retryCount = retryCount + 1
    Loop While excludeSet.Contains(randomNum) And retryCount < MAX_RETRIES
    
    ' 极端情况兜底：若重试多次未命中，返回第一个合法值
    If retryCount >= MAX_RETRIES Then
        For randomNum = lowerBound To upperBound
            If Not excludeSet.Contains(randomNum) Then
                Exit For
            End If
        Next randomNum
    End If
    
    GetRandomExcludeOptimized = randomNum
End Function

优点：逻辑简单，兼容你原来的思路，效率比暴力循环高很多；缺点：排除项占比极高时（比如90%以上），可能会触发重试次数上限，此时会返回第一个合法值，但这种场景建议用方法1。

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