Philippe Tulliez

Préambule
Il arrive régulièrement que nous devions utiliser une liste de valeurs uniques au sein d'une plage de données.
Il y a évidemment de nombreuses méthodes pour y arriver. J'en ai choisi deux. La première utilise la fonction Evaluate en y incluant comme argument, la fonction native d'excel UNIQUE (uniquement pour la version 365) et la seconde utilise l'objet Dictionary.
Chacune d'elles est présentée au sein d'une fonction générique.



Exemple choisi
Pour l'exemple, nous allons choisir d'obtenir les éléments uniques de la colonne Statut du tableau structuré nommé t_Data comme illustré plus haut.

La fonction générique
La fonction générique nommée GetUniqueValue renvoie une table à une dimension contenant les éléments uniques de la colonne d'un tableau structuré dont les noms sont passés en arguments (TableName et LabelName)

Important : La table dont le nom est passé en argument est sensé être présent dans le classeur actif et le nom de la colonne doit exister (la fonction présentée ici ne le contrôle pas) .

Code de la procédure (Exemple 1)
Utilisation de la fonction native d'excel UNIQUE comme argument de la fonction VBA Evaluate.
Pour l'exemple choisi, la formule est =UNIQUE(t_Data[Statut])Attention, pour utiliser cette première procédure, il faut la version 365

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Function GetUniqueValue(TableName As String, LabelName As String)
  ' Renvoie la liste des éléments unique présents dans la colonne du tableau passé par arguments
  ' Philippe Tulliez (https://magicoffice.be)
  ' Arguments
  '   TableName  Nom du tableau
  '   LabelName  Etiquette de la colonne
  ' Déclaration
  Const FormulaPattern As String = "=UNIQUE(<Table>[<Label>])" ' Modèle de la formule
  Dim f As String
  ' Remplacement des balises <Table> et <Label> par les arguments passés
  f = Replace(FormulaPattern, "<Table>", TableName)
  f = Replace(f, "<Label>", LabelName)
  ' Renvoi des éléments uniques
  GetUniqueValue = Application.Transpose(Evaluate(f))
End Function

Code de la procédure (Exemple 2)
Utilisation de l'objet Dictionary en Late Binding

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Function GetUniqueValue(TableName As String, LabelName As String)
  ' Renvoie un Array contenant la valeur Unique dans une colonne
  '   Utilise l'objet Dictionary en Late Binding
  ' Philippe Tulliez (https://magicoffice.be)
  ' Arguments
  '   TableName  Nom du tableau
  '   LabelName  Etiquette de la colonne
  '
  ' Déclaration et affectation
  Dim l As ListObject, c As Range, d As Object
  Dim t As Variant, e As Long
  Set l = Range(TableName).ListObject
  Set d = CreateObject("Scripting.Dictionary") ' Late Binding
  t = l.ListColumns(LabelName).DataBodyRange.Value
  ' Chargement des données
  With d
    For e = 1 To UBound(t): .Item(t(e, 1)) = t(e, 1): Next
    GetUniqueValue = .items
  End With
  ' Libère la mémoire
  Set l = Nothing: Set c = Nothing: Set d = Nothing
End Function

Exemples pour l'invoquer

Exemple 1
Affiche à l'aide d'un MsgBox, la liste des valeurs uniques de la colonne Statut du tableau structuré t_data

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub TestGetUniqueValue_1()
  Dim t As Variant
  t = GetUniqueValue("t_data", "Statut")
  If IsArray(t) Then MsgBox Join(t, vbCrLf)
End Sub

Exemple 2
Alimente le ListBox (ListBox1) d'un UserForm nommé usf_List de la liste des valeurs uniques de la colonne Statut du tableau structuré t_data

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub TestGetUniqueValue_2()
  ' Charge les valeurs uniques de la colonne Statut du tableau t_Data
  '   dans un ListBox d'un UserForm
  With usf_List
  .ListBox1.List = GetUniqueValue("t_data", "Statut")
  .Show
  End With
End Sub

Exemple 3
Crée dynamiquement les onglets d'un TabStrip (TabStrip1) d'un UserForm (UserForm1) avec les valeurs uniques de la colonne Statut du tableau t_Data

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub TestGetUniqueValue_3()
  Dim t As Variant
  Dim e As Integer
  t = GetUniqueValue("t_data", "Statut")
  With UserForm1
    With .TabStrip1
      For e = LBound(t) To UBound(t)
       If e < .Tabs.Count Then
          .Tabs(e).Caption = t(e)
        Else
          .Tabs.Add 1, t(e)
       End If
      Next
    End With
  .Show
  End With
End Sub