Function ListeTriée(champ)
Application.Volatile
Set mondico = CreateObject("Scripting.Dictionary")
n = 0
For i = 1 To champ.Areas.Count ' parcours des zones du champ multi-zones
n = n + champ.Areas(i).Count
For j = 1 To champ.Areas(i).Count ' parcours des éléments d'une zone
If champ.Areas(i)(j) <> "" And champ.Areas(i)(j) <> "." Then
x = champ.Areas(i)(j)
mondico.Item(x) = x ' ajout au dictionnaire (doublons éliminés)
End If
Next j
Next i
temp = mondico.items 'transfert dictionnaire dans une table temp()
Call Tri(temp, LBound(temp), UBound(temp)) ' tri optionnel
Dim d(): ReDim d(n) ' table pour retour
For i = LBound(temp) To UBound(temp): d(i) = temp(i): Next i
ListeTriée = Application.Transpose(d)
End Function
'http://boisgontierjacques.free.fr/pages_site/TriQuickSort.htm
Sub Tri(a, gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
tmp = a(g): a(g) = a(d): a(d) = tmp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call Tri(a, g, droi)
If gauc < d Then Call Tri(a, gauc, d)
End Sub
Function RechercheMZ(valCherchée, champRech As Range, ChampRetour)
Application.Volatile
For i = 1 To champRech.Areas.Count
For j = 1 To champRech.Areas(i).Count
If valCherchée = champRech.Areas(i)(j) Then
RechercheMZ = ChampRetour.Areas(i)(j)
Exit Function
End If
Next j
Next i
RechercheMZ = "pas trouvé"
End Function
