Unique Random Numbers in a Dynamic Table

[Statto]

I have a macro that generates a set of random numbers from 1 to 8 in a table that progressively expands with each iteration (the Interval number in D3 determines the pause time). The problem is these numbers are not unique.

You'll see I'm using id = Evaluate("LET(a,RANDARRAY(8),MATCH(a,SORT(a)))") in my code. This formula generates a set of unique random numbers in a spilled cell range but not in the VBA code.

What is the solution?

Number Sequence Loop.xlsb

This is the main procedure.

Sub TableNumberSequence()

Dim tbl As ListObject
Dim rng As Range
Dim n As Integer

Set tbl = Worksheets("Sheet1").ListObjects("Table1")

Set rng = tbl.ListColumns("Number").DataBodyRange

Call DeleteAllRows

    n = 1
   
    Do While n < 9
   
    Call IntervalTime

        n = n + 1
       
        AddRow n - 1
       
    Loop

End Sub

These are the three other procedures that the main one uses.

Sub AddRow(id)

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = Worksheets("Sheet1")
    Set tbl = ws.ListObjects("Table1")
   
    id = Evaluate("LET(a,RANDARRAY(8),MATCH(a,SORT(a)))")
   
    Worksheets("Sheet1").ListObjects("Table1").ListRows.Add.Range.Cells(1).Value = id
   
    Application.ScreenUpdating = True
   
End Sub

Sub DeleteAllRows()

    With Range("Table1").ListObject
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With

End Sub

Sub IntervalTime()
  
   Dim target As Date

   target = Now + TimeValue("00:00:" & Range("Interval").Text)
  
    Do
       DoEvents
    Loop Until Now >= target
  
End Sub

 

[BSALV]

the macro makes an array of 8*2, with 1st column 1-8 and 2nd column random.
You sort that array and then you copy the 1st "id" rows to your table.
This works in 365-2021, because you use LET-functions.

Sub addRow2(id)
     Dim arr(1 To 8, 1 To 2)     'array 8 rows, 2 columns
     For i = 1 To UBound(arr)
          arr(i, 1) = i     '1st column 1-8
          arr(i, 2) = Rnd     '2nd column random
     Next
     arr1 = Application.Sort(arr, 2, 1)     'sort arr on the 2nd column ascending

     With Worksheets("Sheet1").ListObjects("Table1")     'your table
          If .ListRows.Count = 0 Then .ListRows.Add     'if no listrows, add 1
          .DataBodyRange.Resize(id, 1).Value = arr1     'paste id cells in column A for that table
     End With

End Sub