Durstenfeld’s Algorithm Jumble Array

[YasserKhalil]

UDFs is considered a ready-used solutions for such stuff so I prefer using them. I don't know if I am right or not, but I like using UDFs.

[HansV]

But how would you use it?

[YasserKhalil]

Suppose I have a range I would assign the range as parameter
JumbelArray(rng as range)

[HansV]

What should the return value be? A one-dimensional array or an array the same size as the input range?

[YasserKhalil]

the desired output would be an array the same size as the input range

[HansV]

For example:

Code: Select all

Function ShuffleRange(rng As Range)
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim nr As Integer
    Dim nc As Integer
    Dim rn As Integer
    Dim Temp

    With rng
        n = .Cells.Count
        nr = .Rows.Count
        nc = .Columns.Count
    End With

    ReDim a(1 To n)

    k = 1
    For i = 1 To nr
        For j = 1 To nc
            a(k) = rng.Cells(i, j)
            k = k + 1
        Next j
    Next i

    k = 1
    For i = 1 To nr
        For j = 1 To nc
            rn = WorksheetFunction.RandBetween(1, n - k + 1)
            Temp = a(n - k + 1)
            a(n - k + 1) = a(rn)
            a(rn) = Temp
            k = k + 1
        Next j
    Next i

    ReDim output(1 To nr, 1 To nc)
    k = 1
    For i = 1 To nr
        For j = 1 To nc
            output(i, j) = a(k)
            k = k + 1
        Next j
    Next i

    ShuffleRange = output
End Function

To use this as a UDF, you must confirm it with Ctrl+Shift+Enter to make it an array formula.

S3183.png

[YasserKhalil]

Thank you very much for your patience my tutor
Best Regards