par , 17/03/2023 à 07h29 (37002 Affichages)
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ésente 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
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
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
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
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| Sub TestGetUniqueValue_3()
Dim t As Variant
Dim e As Integer
t = GetUniqueValue("t_data", "Statut")
With UserForm1
With .TabStrip1
.Tabs.Clear
For e = LBound(t) To UBound(t)
.Tabs.Add 1, t(e)
Next
End With
.Show
End With
End Sub |
Consulter le profil
Marquer ce blog comme lu
