VBA数组公式开发：自定义函数如何获取选中单元格范围地址

核心限制说明 #

Excel的普通用户定义函数（UDF）在单元格中调用时，无法直接获取用户选中的单元格范围——这是Excel的安全机制，UDF被限制为只能基于传入的参数或自身单元格进行计算，不能主动读取工作表的选中状态。

可行实现方案 #

方案1：带范围参数的动态数组UDF（适用于Excel 365/2021）

如果用的是支持动态数组的Excel版本，可以写一个VBA函数，接受目标范围作为参数，返回对应大小的唯一随机数数组，输入后自动溢出填充：

Function RandM(rng As Range) As Variant
    Dim arr() As Double
    Dim uniqueNums As Collection
    Dim cellCount As Integer
    Dim i As Integer
    Dim num As Double
    
    cellCount = rng.Cells.Count
    Set uniqueNums = New Collection
    
    ' 生成指定数量的唯一随机数，可替换为RandBetween生成指定范围整数
    On Error Resume Next
    Do While uniqueNums.Count < cellCount
        num = Rnd()
        uniqueNums.Add num, Key:=CStr(num)
    Loop
    On Error GoTo 0
    
    ' 将集合转为对应行列的数组
    ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For i = 1 To cellCount
        arr((i - 1) \ rng.Columns.Count + 1, (i - 1) Mod rng.Columns.Count + 1) = uniqueNums(i)
    Next i
    
    RandM = arr
End Function

使用方法：

• 选中目标范围（比如H16:J24）
• 输入=RandM(H16:J24)，按回车即可自动填充唯一随机数

方案2：宏按钮填充（更直接，无需输入函数）

如果不想依赖参数输入，可以写一个VBA宏，绑定到按钮上，点击后直接给选中范围填充唯一随机数：

Sub FillUniqueRandoms()
    Dim targetRng As Range
    Dim uniqueNums As Collection
    Dim cellCount As Integer
    Dim i As Integer
    Dim num As Double
    
    Set targetRng = Selection
    If targetRng Is Nothing Then Exit Sub
    
    cellCount = targetRng.Cells.Count
    Set uniqueNums = New Collection
    
    On Error Resume Next
    Do While uniqueNums.Count < cellCount
        num = Rnd()
        uniqueNums.Add num, Key:=CStr(num)
    Loop
    On Error GoTo 0
    
    ' 填充到选中范围
    For i = 1 To cellCount
        targetRng.Cells(i).Value = uniqueNums(i)
    Next i
End Sub

使用方法：

• 选中目标范围（H16:J24）
• 点击绑定了这个宏的按钮，直接完成填充

补充说明 #

如果坚持要无参数调用=RandM()，只能通过工作表事件配合，但这种方式会有副作用（比如每次工作表计算都触发），不推荐。更稳妥的方式还是上面两种方案，尤其是方案1在Excel 365中体验更好。

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