VBA 如何创建数组公式

当您想要使用 UDF（用户定义公式）填充选定范围时，您会面临几个问题：

• 您无法将选择内容（当前选定的单元格）作为参数传递
• UDF 无法写入工作表。它只能计算某些内容，然后 Excel 将结果写入使用公式的单元格中。
• 您不能使用 VBA 创建溢出函数（更新：我错了，您可以。）
• 如果可以创建这样的函数，那么每当 Excel 认为需要重新计算工作表时，就会触发该函数。由于该函数会创建随机数，因此每次重新计算工作表时都会重新填充范围 - 这可能不是您想要的。

因此，编写一个填充范围的子程序：

Sub randM(Optional r As Range = Nothing)
    If r Is Nothing Then Set r = Selection
    
    Dim valueList As Collection, i As Long
    Set valueList = GetRandomValueList
    
    Dim cell As Range
    For Each cell In r
        Dim randomIndex As Long, randVal As Double
        randomIndex = Int((Rnd * valueList.Count) + 1)
        cell.Value = valueList(randomIndex)
        valueList.Remove randomIndex

        If valueList.Count = 0 Then Exit Sub  ' No more possible values.
    Next
End Sub

现在您需要一个简单的例程来用可能的值填充列表（我使用 Collection）。以下示例创建了一副牌：

Private Function GetRandomValueList() As Collection
    Dim valueList As New Collection
    Dim i, j
    For Each i In Array(2, 3, 4, 5, 6, 7, 8, 9, 10, "Jack", "Queen", "King", "Ace")
        For Each j In Array("Spade", "Hearts", "Diamonds", "Clubs")
            valueList.Add i & " of " & j
        Next
    Next
    Set GetRandomValueList = valueList
End Function

由于您只能从 Excel 调用无参数子程序，因此只需创建一个在当前选择上运行的包装器

Sub randMOnSelection
    randM Selection
End Sub

现在，只要您需要一个由随机数填充的范围，只需调用此例程即可。您可以从宏菜单（开发人员->宏或视图->宏）执行此操作。您还可以在宏菜单下的 [选项...] 中将宏分配给某个键（例如 Ctrl+Shift+R）。或者，您可以在工作表上放置一个按钮或形状来调用此例程。