VBA Como criar uma fórmula de matriz

Você enfrenta vários problemas quando deseja usar uma UDF (fórmula definida pelo usuário) para preencher o intervalo selecionado:

• Você não pode passar a seleção (as células selecionadas atualmente) como parâmetro
• Uma UDF não pode escrever na planilha. Ela só pode calcular algo e o Excel escreve o resultado na célula onde a fórmula é usada.
• Você não pode criar uma função spill com VBA ( Atualização : eu estava errado, você pode.)
• Se fosse possível criar tal função, ela seria acionada sempre que o Excel achasse que precisa recalcular a planilha. Como a função cria números aleatórios, o intervalo seria preenchido novamente a cada recálculo da planilha - provavelmente não é o que você quer.

Então, em vez disso, escreva uma sub-rotina que preencha o intervalo:

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

Agora você precisa de uma rotina simples que preencha uma lista (eu uso uma Collection) com valores possíveis. O exemplo a seguir cria um baralho de cartas:

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

E como você só pode chamar sub-rotinas sem parâmetros do Excel, basta criar um wrapper que seja executado na seleção atual

Sub randMOnSelection
    randM Selection
End Sub

Agora, basta chamar essa rotina sempre que precisar de um intervalo preenchido com números aleatórios. Você pode fazer isso no Macro-Menu (Developer->Macros ou View->Macros). Você também pode atribuir a Macro a uma Tecla (por exemplo, Ctrl+Shift+R) no Macro Menu em [Options...]. Ou você coloca um botão ou forma na sua planilha que chama essa rotina.