Fonction Alea

=ENT(1+66*ALEA())

Option Explicit

Function AleaJJ(NbNu&, Plage As Range, Ordre As Byte, Optional Colonne) As Variant
    'nbNu = nb n° total
    'Plage sur 2 colonnes contenant 1 - les n° / 2 - les %
    'Ordre = Ordre du n° tiré, logique en ligne (évite doublons) ou - si option - en colonne
    Dim Ta#(), Tb#(), n&, i&, j As Byte, p#, b As Boolean, r&, m As Boolean, a&
    Application.Volatile
    n = Plage.Rows.Count: ReDim Ta(1 To n, 1)
    For i = 1 To n
        For j = 0 To 1
            Ta(i, j) = Cells(Plage.Row + i - 1, Plage.Column + j)
            If Ta(i, 0) > NbNu Then AleaJJ = CVErr(xlErrRef): Exit Function
        Next j
        p = p + Ta(i, 1)
    Next i
    If p > 1 Then AleaJJ = CVErr(xlErrRef): Exit Function
    ReDim Tb(1 To NbNu, 1)
    For i = 1 To n: Tb(Ta(i, 0), 0) = Ta(i, 1): Next i
    Erase Ta: p = (1 - p) / (NbNu - n)
    If Tb(1, 0) = 0 Then Tb(1, 0) = p
    Tb(1, 1) = Tb(1, 0)
    For i = 2 To NbNu
        If Tb(i, 0) = 0 Then Tb(i, 0) = p
        Tb(i, 1) = Tb(i - 1, 1) + Tb(i, 0)
    Next i
    Randomize: r = Application.Caller.Row: n = Application.Caller.Column: m = IsMissing(Colonne)
    Do
        p = Rnd
        For i = 1 To NbNu
            If Tb(i, 1) > p Then AleaJJ = i: Exit For
        Next i
        b = True
        For i = 2 To Ordre
            If m Then a = Cells(r, n + 1 - i) Else a = Cells(r + 1 - i, n)
            If AleaJJ = a Then b = False: Exit For
        Next i
    Loop Until b
End Function