I. Introduction▲
La collection « ArrayList » n’est pas souvent utilisée en programmation VBA. Elle dispose pourtant de méthodes pour manipuler les données, préalablement chargées sous forme d’une liste (d’où son nom) qui simplifient la tâche du programmeur. Pour ne citer que les plus utiles :
- « Sort » trie la liste ;
- « Reverse » inverse l’ordre de la liste ;
- « Contains » indique si un élément est présent dans la liste ;
- « Item » renvoie ou modifie le contenu d’un élément de la liste ;
- « IndexOf » renvoie la position d’un élément dans la liste ;
- « Clone » fait une copie de la liste.
Les données étant en mémoire, les traitements peuvent dépasser la limite des 1 048 576 lignes d'une feuille Excel.
Vous trouverez sur Internet des documentations sur la collection « ArrayList », comme celle-ci que je vous recommande pour débuter : VBA ArrayList – A Complete Guide.
Le seul hic est, qu’en principe, une collection « ArrayList » n’a qu’une dimension, c’est-à-dire que la liste ne comporte qu’une seule colonne. Ce qui en restreint l’usage à des cas simples. Certainement la raison pour laquelle cette collection est si peu utilisée.
Mais, en trichant un peu, une collection « ArrayList » peut avoir plusieurs dimensions, tel un tableau d’Excel qui a plusieurs colonnes.
Malheureusement dans ce cas certaines des méthodes les plus intéressantes (« Sort », « Contains », « IndexOf », etc.) ne sont plus disponibles...
Il m’a donc fallu écrire en VBA des fonctions équivalentes afin de profiter de la rapidité de traitement de cette collection sur des listes de grande taille à plusieurs colonnes.
À l’appui d’un exercice simple, nous allons étudier dans les lignes qui suivent comment charger des données dans une liste à plusieurs dimensions, les manipuler à notre guise puis les restituer dans un tableau structuré.
II. Gérer une collection ArrayList à plusieurs dimensions▲
Avant de commencer notre étude, rappelons que pour utiliser une collection « ArrayList » il faut préalablement initialiser un objet de cette façon :
Dim List As Object
Set List = CreateObject("System.Collections.ArrayList")
Normalement la liste n’a qu’une dimension comme dans cet exemple où l’on y ajoute deux éléments :
List.Add "Apple"
List.Add "Pear"
Cet objet est en base 0 (et ne comporte pas d'en-tête), c’est-à-dire que le premier élément a l’indice 0.
Pour connaitre la valeur du premier élément : MaValeur = List.Item(0)
Notez que la liste est dynamique : lors de l’ajout ou de la suppression d’un élément sa taille est automatiquement ajustée.
Et que les données sont de types Variant, c'est-à-dire qu'elles peuvent être des chaînes de caractères, des nombres, des dates.
L’astuce pour avoir plusieurs dimensions est d’ajouter à la liste non pas une valeur classique mais un Array, par exemple :
List.Add Array("Apple", 10, "Red")
List.Add Array("Pear", 33, "Green")
Les dimensions sont elles aussi en base 0. Elles peuvent avoir des variables de types différents.
J’utiliserai souvent le mot « colonne » au lieu de « dimension », en référence aux tableaux d’Excel.
Donc pour connaitre la valeur de la première colonne du premier élément : MaValeur = List.Item(0)(0)
Ou, si « i » est l’élément et « j » la colonne : MaValeur = List.Item(i)(j)
Pour éviter toute confusion, les arguments de mes fonctions nécessitant un type Array seront préfixées « Array ».
Remarque : Dans une liste à plusieurs dimensions (plusieurs colonnes) vous ne pouvez pas modifier directement une valeur par List.Item(i)(j) = "x"
Il faut procéder en trois étapes :
- Mémoriser dans une variable (de type Variant) les données de toutes les dimensions de l'élément : v = List.Item(i)
- Modifier la dimension souhaitée : v(j) = "x" (où « j » est la dimension en base 0 à modifier)
- Recopier la variable (qui contient toutes les données des dimensions) dans l'élément : List.Item(i) = v
II-A. Exercice pour mieux comprendre les fonctions utilisées▲
Prenons un cas d’école.
Les notes des élèves sont contenues dans trois tableaux différents.
Le premier est un tableau structuré nommé « Tableau1 », les deux suivants sont des plages classiques sur les feuilles « Feuil5 » et « Feuil6 » :
Le but est d’avoir un tableau de synthèse qui reprend la liste des élèves, le nombre de notes obtenues et leur moyenne. À chaque sélection d’un élève le détail de ses notes s’affiche dans un second tableau à gauche :
Gardez bien en mémoire ces tableaux car ils serviront de fil conducteur dans cette documentation.
II-B. ListFromRange - Charger des données▲
Pour commencer notre exercice nous allons charger dans un objet « ArrayList » les données de ces trois tableaux source avec la fonction ListFromRange.
Ses arguments sont :
- Rg : La plage qui contient les données, c’est-à-dire soit un tableau structuré, soit une plage classique qui possède ou non une ligne d’en-tête ;
- ArrayColumns : (facultatif) Un Array qui contient la liste des colonnes à reprendre (dans l’ordre indiqué) ou vide pour toutes les reprendre.
La fonction renvoie un objet « ArrayList ».
Pour charger le premier tableau, dans l’objet « L_Données » préalablement déclaré, nous indiquerons à la fonction de ne reprendre que les cinq premières colonnes (la colonne « Id » n’est pas souhaitée) :
Set L_Données = ListFromRange(Range("Tableau1"), Array(0, 1, 2, 3, 4))
Notez qu’il n’est pas nécessaire d’indiquer la feuille où se trouve le tableau structuré car Excel la retrouve de lui-même.
Et que Range("Tableau1") peut être remplacé par [Tableau1], tout dépend de vos habitudes de programmation.
Le deuxième tableau est chargé dans un autre objet, déclaré « L_Tps », que l’on concatènera à « L_Données » par la suite. Ce tableau n’ayant pas d’en-tête on indiquera dans l’argument « Rg » la cellule « A1 » car la fonction étendra automatiquement la plage à la région concernée. Par contre il faudra bien tenir compte de l’ordre des colonnes à reprendre :
Set L_Tps = ListFromRange(Sheets("Feuil5").Range("A1"), Array(0, 1, 2, 4, 3))
Enfin pour le troisième tableau à charger, l’argument « ArrayColumns » n’a pas besoin d’être renseigné puisque les colonnes sont dans l’ordre attendu. Il faut juste penser à indiquer dans l’argument « Rg » la cellule « A2 » pour ne pas reprendre la ligne d’en-tête et non pas « A1 » :
Set L_Tps = ListFromRange(Sheets("Feuil6").Range("A2"))
Astuce : La fonction ListFromCSV importe dans une liste un fichier texte « csv » (Comma-separated values) au format anglo-saxon, c'est-à-dire, les champs sont délimités par une virgule ; les dates sont au format année, mois, jour ; le séparateur numérique est un point.
Rassurez-vous, la fonction remet le tout au format français.
Inversement vous pouvez enregistrer une liste dans ce format avec la fonction ListToCSV.
II-C. ListAddList - Concaténer deux listes▲
Revenons à l’étape où nous avons chargé le premier tableau dans l’objet « L_Données » et le deuxième dans l’objet « L_Tps ».
Pour ajouter les données de « L_Tps » à « L_Données » nous utilisons la fonction ListAddList.
Ses arguments sont :
- List : La liste qui sera alimentée ;
- ListAdd : La liste qu’il faut ajouter à « List » ;
- ArrayColumnCompareValue : (facultatif) Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments à ajouter (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
Ce qui donne ceci pour ajouter toutes les données de « L_Tps » à « L_Données » :
Call ListAddList(L_Données, L_Tps)
Nous ferons de même avec le troisième tableau pour obtenir une liste « L_Données » qui contient l’ensemble des données des trois sources.
Les arguments facultatifs de cette fonction permettent de limiter la concaténation à certaines données, par exemple quand les notes (colonne 2) sont supérieures à 0, soit :
Call ListAddList(L_Données, L_Tps, Array(2, ">", 0))
Notez que les deux objets doivent avoir la même structure de données, c’est-à-dire le même nombre de colonnes.
Astuce : La ligne d'instruction Call ListAddList(L_Données, L_Tps, Array(2, ">", 0)) n'est pas évidente à comprendre car elle reprendre deux variables numériques dans sa partie Array(2, ">", 0) et l'on peut confondre l'argument colonne et l'argument valeur. C'est pourquoi je propose de préfixer l'argument qui représente la colonne par "C", ce qui donne Call ListAddList(L_Données, L_Tps, Array("C2", ">", 0)) et peut se lire plus facilement par : Colonne 2 est supérieure à 0.
Je vais utiliser cette notation, qui est facultative, dans la suite de cette documentation. Les fonctions sont adaptées pour gérer ces deux façons de faire, libre à vous d'utiliser ce qui vous convient le mieux.
- Les symboles que j'ai choisis pour identifier les opérateurs de comparaison commence par « ?* », contient « * », termine par « *? », peuvent être remplacés par ceux de votre choix dans la fonction VbCompare s'ils ne sont pas assez explicites pour vous.
- L'instruction Call ListAddList(L_Données, L_Tps) peut être remplacée par son équivalent : ListAddList L_Données, L_Tps
Tout dépend de vos habitudes de programmation.
II-D. ListRemove - Supprimer des éléments▲
Les données chargées dans l’objet « L_Données » inclues des notes à 0, qui signifient que l’évaluation n’a pas encore été faite. Il faut donc les supprimer pour ne pas fausser la moyenne, avec la fonction ListRemove.
Ses arguments sont :
- List : La liste qui contient les données ;
- ArrayColumnCompareValue : (facultatif) Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments à supprimer (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
Et donc pour supprimer les notes qui valent 0 (c’est-à-dire la colonne 2 vaut 0) :
Call ListRemove(L_Données, Array("C2", "=", 0))
Astuce : Pour supprimer un élément d’après sa position, utilisez la méthode « RemoveAt » qui reste disponible pour une liste à plusieurs dimensions.
Par exemple pour supprimer le dernier élément de la liste « L_Données » :
L_Données.RemoveAt L_Données.Count – 1
Ou pour supprimer le premier élément :
L_Données.RemoveAt 0
II-E. ListSort - Trier les données▲
La fonction ListSort trie par ordre croissant un objet « ArrayList » sur une ou plusieurs de ses colonnes.
Ses arguments sont :
- List : La liste qui contient les données ;
- ArrayColumns : Les colonnes (en Array) sur lesquelles portent le tri ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
Nous allons ainsi trier par ordre croissant l’objet « L_Données » sur les noms, prénoms et dates :
Call ListSort(L_Données, Array("C0", "C1", "C3"))
Cela sera nécessaire pour la restitution du détail lorsqu’un élève est sélectionné.
Astuce : Vous pouvez inverser l’ordre de la liste avec la méthode « Reverse » qui reste disponible pour une liste à plusieurs dimensions : L_Données.Reverse
II-F. ListFromList - Copier certaines colonnes d’une liste dans une autre liste▲
Pour générer la liste de synthèse des élèves, il nous faut dans un premier temps recopier les noms et prénoms de la liste « L_Données » dans une autre liste que nous nommerons « L_Synthese ».
Les arguments de la fonction ListFromList sont :
- List : La liste qui contient les données ;
- ArrayColumns : Les colonnes (en Array) qu’il faut reprendre et dans l’ordre désiré.
La fonction renvoie un objet « ArrayList ».
Ici nous copions les colonnes 0 et 1 de « L_Données » dans « L_Synthese » :
Set L_Synthese = ListFromList(L_Données, Array("C0", "C1"))
Astuce : Pour copier toutes les données de « L_Données » dans « L_Synthese » nous pouvons utiliser la méthode « Clone » qui reste disponible pour une liste à plusieurs dimensions : Set L_Synthese = L_Données.Clone
À ne pas confondre avec Set L_Synthese = L_Données qui ne génère pas une nouvelle liste en mémoire mais juste un pointeur vers la liste « L_Données », et dans ce cas la modification d’un élément dans l’une des listes se répercute dans la seconde.
II-G. ListUnique - Générer une liste unique▲
Nous pouvons maintenant générer une liste sans doublon pour la synthèse des élèves à partir de la liste « L_Synthese » qui ne contient que deux colonnes, pour les noms et prénoms.
La fonction ListUnique réalise cela.
Ses arguments sont :
- List : La liste qui contient les données ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
La liste « L_Synthese » est transformée en une liste unique, triée par ordre croissant :
Call ListUnique(L_Synthese)
II-H. ListAddcolumns - Ajouter ou supprimer des colonnes à une liste▲
Nous venons de voir que la liste « L_Synthese » des élèves (sans doublon) ne contient que deux colonnes, pour les noms et prénoms. Or il nous en faut deux autres : pour le nombre de notes et la moyenne.
Nous les ajoutons avec la fonction ListAddcolumns.
Ses arguments sont :
- List : La liste qui contient les données ;
- Columns : Le nombre de colonnes à ajouter, ou à supprimer si la valeur est négative ;
- Index : (facultatif) Si True alors indique la position de l'élément dans la dernière colonne (soit la création d'une indexation), la valeur par défaut est False (nous verrons un exemple d'utilisation de l'indexation par la suite).
Call ListAddcolumns(L_Synthese, 2)
II-I. ListCount - Compter le nombre d’éléments ▲
Pour alimenter la colonne du nombre des notes de la liste « L_Synthese » nous allons faire une boucle sur ses élément (nous verrons plus loin comment faire cette boucle) et pour chacun d’entre eux compter le nombre d’éléments dans la liste « L_Données » qui correspondent au nom et prénom de l'élève.
La fonction ListCount renvoie le nombre d’éléments qui correspondent aux critères demandés.
Ses arguments sont :
- List : La liste qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la liste est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
Soit pour chaque élément « i », il faut compter combien de fois l’on retrouve dans la liste « L_Données » en colonne 0 son nom, correspondant à « L_Synthese(i)(0) », et en colonne 1 son prénom, correspondant à « L_Synthese(i)(1) » :
Nb = ListCount(L_Données, Array("C0", "=", L_Synthese(i)(0), "C1", "=", L_Synthese(i)(1)), True, True)
Astuce : Dans notre cas la liste « L_Données » où porte la recherche est triée par ordre croissant sur les noms, prénoms et dates. Pour trouver plus rapidement les éléments recherchés dans cette liste, au lieu de tous les lire, il est bien plus rapide de faire une recherche dichotomique pour accéder rapidement au premier élément concerné et de commencer les analyses à partir de cette position. C’est pourquoi ici l’argument « Sorted » est mis à True.
Pour faire des recherches en boucle sur de grandes listes, je vous recommande de trier au préalable la liste source et de procéder ainsi, car vous gagnerez énormément en temps de traitement.
II-J. ListSum - Faire la somme d’une colonne▲
Nous allons procéder de la même façon pour alimenter la colonne de la moyenne des notes, qui est le nombre de notes divisé par la somme des notes.
Nous avons déjà le nombre de notes, nous allons retrouver la somme des notes avec la fonction ListSum.
Ses arguments sont :
- List : La liste qui contient les données ;
- Column : La colonne dont il faut faire la somme ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la liste est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
Soit pour chaque élément « i », sachant que les noms sont en colonne 0 et les prénoms en colonne 1 dans les deux listes, et que les notes sont en colonne 2 dans « L_Données » :
Somme = ListSum(L_Données, "C2", Array("C0", "=", L_Synthese(i)(0), "C1", "=", L_Synthese(i)(1)), True, True)
II-K. ListUpdateItem - Mettre à jour les données▲
Nous avons déterminé pour chaque élève le nombre de notes et leur somme. Il ne reste plus qu’à mettre à jour les colonnes « Nb » et « Moyenne », colonnes 2 et 3, de l’objet « L_Synthese », avec la fonction ListUpdateItem.
Ses arguments sont :
- List : La liste qui contient les données à mettre à jour ;
- Column : La colonne qu'il faut modifier ;
- Action : L’action à effectuer
« = » remplacer la donnée par la valeur de l'argument "Value",
« + » ajouter à la donnée la valeur de l'argument "Value",
« - » soustraire à la donnée la valeur de l'argument "Value",
« * » multiplier la donnée par la valeur de l'argument "Value",
« / » diviser la donnée par la valeur de l'argument "Value" ; - Value : La valeur de référence pour modifier la colonne suivant l'action désirée ;
- Item : La position de l’élément à mettre à jour.
Soit pour chaque élément « i », pour remplacer la note en colonne 2 dans « L_Synthese » par la valeur « Nb » retrouvée :
Call ListUpdateItem(L_Synthese, "C2", "=", Nb, i)
Et pour remplacer la moyenne en colonne 3 (avec une précision d’un chiffre après la virgule) :
Call ListUpdateItem(L_Synthese, "C3", "=", Round(Somme / Nb, 1), i)
Remarque : Pour mettre à jour chaque élément nous faisons une boucle :
For i = 0 To L_Synthese.Count – 1 (où « i » est déclaré Long).
Il n’est pas possible d’utiliser For Each Element In List (où « Element » est déclaré Variant) car dans notre cas nous avons besoin de connaitre la position « i » de l’élément pour sa mise à jour. Position qui renseigne l’argument « Item ».
Astuce : La fonction ListUpdateAll met à jour tout une liste suivant un critère, sans avoir besoin de faire une boucle.
En plus des quatre premiers arguments de ListUpdateItem cette fonction en comporte deux autres déjà présentés plusieurs fois : « ArrayColumnCompareValue » et « MatchCase ».
Par exemple pour ajouter 1 point aux notes inférieures à 10 dans la liste « L_Données » :
Call ListUpdateAll(L_Données, "C2", "+", 1, Array("C2", "<", 10))
II-L. ListToTable – Alimenter un tableau structuré▲
Nous avons terminé tous nos traitements, il ne reste plus qu’à alimenter le tableau structuré de synthèse par élève, nommé « TS_Synthese », avec les données de la liste « L_Synthese ».
La fonction ListToTable a les arguments suivants :
- List : La liste qui contient les données ;
- Action : L’action à effectuer
« = » remplacer les données existantes du tableau structuré par celles de la liste,
« + » ajouter à la suite du tableau structuré les données de la liste ; - TS : Le tableau structuré à renseigner.
Call ListToTable(L_Synthese, "=", [TS_Synthese])
II-M. ListIndexOf - Connaitre la position d’un élément▲
Pour connaitre la moyenne de l’élève « TERRIEUR Alain » il nous faut avant tout connaitre sa position dans la liste « L_Synthese ».
Pour cela nous utiliserons la fonction ListIndexOf, ses arguments sont :
- List : La liste qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la liste est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie la position du premier élément trouvé dans la liste qui répond aux critères de sélection, ou -1 si rien n’est trouvé.
Dans notre cas on obtient la position par :
i = ListIndexOf(L_Synthese, Array("C0", "=", "TERRIEUR", "C1", "=", "Alain"))
Puis la moyenne par :
Moyenne = L_Synthese.Item(i)(3)
II-N. ListContains - Savoir si un élément existe▲
Cette fois nous souhaitons juste savoir si au moins un élève a une moyenne supérieure ou égale à 15.
Pour cela nous utiliserons la fonction ListContains, ses arguments sont :
- List : La liste qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la liste est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie True si un élément répond aux critères de sélection, ou False si rien n’est trouvé.
Dans notre cas ListContains(L_Synthese, Array("C3", ">=", 15)) renvoie True.
II-O. ListAddItem - Ajouter un élément▲
Un nouvel élève vient d’arriver. N’ayant pas encore été noté il n’apparait donc pas dans les listes, mais nous souhaitons le voir dans la synthèse en dernière ligne.
Nous allons l’ajouter « manuellement » avec la fonction ListAddItem.
Ses arguments sont :
- List : La liste qui contient les données ;
- ArrayValues : Un Array des valeurs à ajouter (dans l'ordre des colonnes de l'objet) ;
- Index : (facultatif) Indique la position où insérer le nouvel élément, vide ou -1 pour l’ajouter à la suite de la liste.
Par exemple :
Call ListAddItem(L_Synthese, Array("AIMAR", "Jean", Null, Null))
Remarque : Toutes les dimensions doivent être renseignées, d’où ici l’usage des Null pour que les colonnes prennent la valeur « vide ».
III. Le code source de l’exercice▲
Voici le code intégral de l’exercice :
Public L_Données As Object
'----------------------------------------------------------------------------------------
Sub Exemple()
'----------------------------------------------------------------------------------------
Dim i As Long
Dim L_Tps As Object, L_Synthese As Object
Dim Nb As Long, Somme As Double
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
' Charge les données à importer depuis un tableau structuré nommé "Tableau1"
' (pas besoin d'indiquer la feuille car Excel la retrouve de lui-même, mais
' comme il y a une colonne de trop il faut indiquer les colonnes à reprendre),
' et mémorise les données dans "L_Données" (qui est publique car on s'en servira
' pas la suite lors d'un événement sur le tableau de synthèse):
Set L_Données = ListFromRange(Range("Tableau1"), Array("C0", "C1", "C2", "C3", "C4"))
' Charge les données à importer d'une autre liste sans en-tête
' (et dans un ordre différent que celui que l'on souhaite):
Set L_Tps = ListFromRange(Sheets("Feuil5").Range("A1"), Array("C0", "C1", "C2", "C4", "C3"))
' Concatène les deux listes dans l'objet "L_Données":
Call ListAddList(L_Données, L_Tps)
' Charge les données à importer d'une autre liste avec en-têtes (pas besoin d'indiquer
' l'ordre des colonnes car elles sont dans le bon ordre et il n'y en a pas en trop):
Set L_Tps = ListFromRange(Sheets("Feuil6").Range("A2"))
' Concatène les deux listes dans l'objet "L_Données":
Call ListAddList(L_Données, L_Tps)
' Supprime les notes à 0 (càd 0 est dans l'indice 2):
Call ListRemove(L_Données, Array("C2", "=", 0), True)
' Trie la liste par nom, prénom, date:
Call ListSort(L_Données, Array("C0", "C1", "C3"), True)
' Charge uniquement les noms et prénoms (pour alimenter la synthèse):
Set L_Synthese = ListFromList(L_Données, Array("C0", "C1"))
' Création d'une liste unique avec les noms et prénoms (triée dans l'ordre de ses colonnes):
Call ListUnique(L_Synthese, True)
' Ajoute deux colonnes (pour contenir par la suite le nombre de notes et la moyenne)
' à cette liste qui servira de synthèse:
Call ListAddcolumns(L_Synthese, 2)
' Boucle sur les éléments de L_Synthese (qui est la synthèse) en base 0:
Application.DisplayStatusBar = True
For i = 0 To L_Synthese.Count - 1
' Affiche la progression de la mise à jour:
If L_Synthese.Count > 100 Then
If i Mod L_Synthese.Count / 100 = 0 Then Application.StatusBar = "Mise à jour : " & Format(i / L_Synthese.Count, "0%")
End If
' Recherche le nombre de notes pour le nom + prénom dans la liste source triée:
Nb = ListCount(L_Données, Array("C0", "=", L_Synthese(i)(0), "C1", "=", L_Synthese(i)(1)), True, True)
' Mise à jour de la colonne Nb (2 en base 0) pour l'élément i dans la liste de synthèse:
Call ListUpdateItem(L_Synthese, "C2", "=", Nb, i)
' Recherche la somme des notes pour le nom + prénom dans la liste source triée:
Somme = ListSum(L_Données, "C2", Array("C0", "=", L_Synthese(i)(0), "C1", "=", L_Synthese(i)(1)), True, True)
' Mise à jour de la colonne Moyenne (3 en base 0) pour l'élément i dans la liste de synthèse:
Call ListUpdateItem(L_Synthese, "C3", "=", Round(Somme / Nb, 1), i)
Next i
Application.StatusBar = ""
' Affiche la synthèse en remplaçant les données existantes:
' [TS_Synthese] est équivalent à Range("TS_Synthese"):
Call ListToTable(L_Synthese, "=", [TS_Synthese])
' Prépare le tableau de détail:
With Range("TS_Données").ListObject
.DataBodyRange.Clear
If .ListRows.Count > 1 Then .DataBodyRange.Rows(2 & ":" & .ListRows.Count).Delete
.ShowHeaders = True
.ShowTotals = False
End With
[A12].Select
' Fin du traitement:
Gest_Err:
If Err.Number <> 0 Then
MsgBox "Erreur " & Err.Number & " : " & Err.Description, vbCritical, Err.Source
End If
Err.Clear
End Sub
'------------------------------------------------------------------------------------------------
Et le code sur la feuille où se trouve le tableau de synthèse pour gérer l’évènement sur sélection d’un élève :
'------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'------------------------------------------------------------------------------------------------
' Si la cellule (active) est dans un tableau structuré:
If Not Target.ListObject Is Nothing Then
' Pointe sur ce tableau:
Dim TS As Range
Set TS = Range(Target.ListObject)
' Si ce n'est pas une ligne de titre et le tableau de synthèse:
If Target.Row >= TS.Row And TS.ListObject.Name = "TS_Synthese" Then
' Retrouve la ligne dans le tableau structuré:
Dim i As Long, Nom As String, Prénom As String
i = Target.Row - TS.Row + 1
' Et le nom et prénom concernés:
Nom = TS.ListObject.DataBodyRange(i, 1)
Prénom = TS.ListObject.DataBodyRange(i, 2)
' Création d'une liste qui contient les données de l'élément cliqué:
If Not L_Données Is Nothing Then
Dim List As Object
Set List = CreateObject("System.Collections.ArrayList")
Call ListAddList(List, L_Données, Array("C0", "=", Nom, "C1", "=", Prénom))
' Affiche la liste dans le tableau structuré TS_Données:
If List.Count > 0 Then Call ListToTable(List, "=", [TS_Données])
End If
End If
End If
End Sub
'------------------------------------------------------------------------------------------------
IV. ListFromSQL - Exécuter une requête SQL sur une liste▲
Le tableau de synthèse des notes et moyennes des élèves que nous avons obtenu ferait un bon exercice de requête SQL :
« Sélectionner dans la liste « L_Données » les champs [Nom Famille], [Prénom], Nombre de ([Note]), Moyenne de ([Note]) ; Regrouper par [Nom Famille], [Prénom] ; Trier par [Nom Famille], [Prénom] ».
Mais il n’est pas possible d’exécuter une requête SQL sur un objet « ArrayList ».
Sauf si l’on triche un peu...
Il suffit de reverser les données de la liste sur une feuille de calcul provisoire, d’y ajouter une ligne d’en-tête de colonne (nommés arbitrairement C0, C1, C2, etc.), d’exécuter une requête SQL, de récupérer le jeu d’enregistrements obtenu pour le placer dans une nouvelle liste, et enfin de supprimer la feuille provisoire.
La fonction ListFRomSQL a les arguments suivants :
- List : La liste qui contient les données ;
- Fields : Les champs à sélectionner ;
- StrSQL : L’instruction en SQL.
La fonction renvoie un objet « ArrayList » qui contient le jeu d’enregistrements obtenu.
Ce qui permet d’obtenir le tableau de synthèse en une seule instruction et avec de bien meilleurs temps de traitement, sur les grandes listes, qu’avec la boucle que nous avons faite sur chaque élément :
Set L_Synthese = ListFromSQL(L_Données, "C0, C1, Count(C2), AVG(C2)", "GROUP BY C0, C1 ORDER BY C0, C1")
Les habitués du SQL remarqueront que la commande « SELECT » n’est pas indiquée : Elle est ajoutée automatiquement avant la liste des champs.
Tout comme il manque la commande « FROM » : C’est parce qu’elle doit être complétée du nom de la feuille créée temporairement, nom que l’on ne connait qu’à sa création.
Remarques sur l’argument « Fields » :
L’argument « Fields » indique les numéros des colonnes à sélectionner, en base 0 et préfixés par « C », qui correspondent donc à la dimension de la liste, elle aussi en base 0.
La première colonne est désignée « C0 », la suivante « C1 » et ainsi de suite.
Une étoile « * » permet de sélectionner directement toutes les colonnes, ce qui risque d’augmenter les temps de traitement sur de grandes listes.
Vous pouvez inclure des fonctions d’agrégation :
- AVG : Calcule la moyenne ;
- SUM : Calcule la somme ;
- COUNT : Compte le nombre d'éléments ;
- MIM : Renvoie la valeur la plus petite ;
- MAX : Renvoie la valeur la plus grande.
Remarques sur l’argument « StrSQL » :
L’argument « StrSQL » indique les instructions de la requête. Plusieurs commandes sont disponibles, les principales sont :
- GROUPE BY : Fait un regroupement des données.
- ORDER BY : Trie les colonnes par ordre ascendant (par défaut) avec la clause ASC ou descendant avec la clause DESC. Il est possible d'ordonner sur plusieurs colonnes en respectant la syntaxe :
ORDER BY « colonne 1 » [ASC, DESC], « colonne 2 » [ASC, DESC]
- WHERE : Pour restreindre la sélection vous pouvez ajouter une condition simple ou combinée avec les opérateurs AND et OR, comme en VBA.
Les données alphanumériques sont à mettre entre simples quotes. Si la donnée comporte un simple quote il convient de le doubler. Par exemple :
WHERE « colonne » = 'Val d''Oise'
Les dates sont à mettre entre deux croisillons et au format anglo-saxon (année/mois/jour). Par exemple pour sélectionner les personnes nées à Noël 1971 :
WHERE « colonne » = #1971/12/25#
Le mot clé IN permet de sélectionner des éléments parmi une liste de valeurs, soit la syntaxe suivante :
WHERE « colonne » IN ('valeur1', 'valeur2', 'valeur3'…)
Le mot clé LIKE permet de sélectionner des chaînes de caractères en disposant des jokers :
WHERE « colonne » LIKE 'T%' renvoie les noms commençants par « T ».
WHERE « colonne » LIKE '%TOTO%' renvoie les noms qui contiennent « TOTO ».
Vous trouverez sur Internet d'autres informations sur les requêtes SQL.
J'aime bien cette adresse : http://www.1keydata.com/fr/sql/syntaxe-sql.php.
Attention, toutes les commandes SQL ne sont pas compatibles avec EXCEL.
La fonction ListFRomSQL utilise des liaisons tardives, vous n’avez donc pas besoin d’installer la bibliothèque « Microsoft ActiveX Data Objects 6.0 Library » dans votre projet.
Un autre exemple :
Reprenons notre cas où la liste « L_Données » contient les données de trois tableaux fusionnés et « L_Synthese » la liste de synthèse.
On souhaite ajouter 2 points aux notes inférieures à 10, sans dépasser 10, uniquement pour les élèves dont la moyenne est inférieure à 10.
Voici comment nous allons procéder :
- Ajouter une colonne à « L_Données » qui contiendra l'indexation des éléments (vous allez comprendre pourquoi ci-dessous).
- Boucler sur les éléments de la liste « L_Synthese » et si la moyenne (colonne 3) est inférieure à 10 alors :
- Faire une requête SQL pour récupérer dans une liste temporaire les notes inférieures à 10 de l'élève identifié par son nom (colonne 0) et son prénom (colonne 1).
- Pour chaque élément de la liste obtenue :
- Récupérer l'indexation de l'évaluation contenue dans la colonne 5.
- Ajouter 2 points à la note (colonne 2) dans la limite de 10.
- Mettre à jour l'élément dans « L_Données » puisque l'on connait sa position (par l'indexation).
- Supprimer la colonne d'indexation dans « L_Données » qui n'est plus utile.
- Actualiser le tableau de synthèse par la requête SQL que nous connaissons.
- Afficher la synthèse dans le tableau structuré.
Ce qui donne :
' Ajoute une indexation à la liste L_Données
Call ListAddcolumns(L_Données, 1, True)
' Boucle sur chaque élément de la liste L_Synthese:
For Each E In L_Synthese
' Si la moyenne est < 10:
If E(3) < 10 Then
' Requête SQL pour obtenir les notes < 10 de l'élève:
Set Rt = ListFromSQL(L_Données, "*", "WHERE C2<10 AND C0='" & E(0) & "' AND C1='" & E(1) & "'")
' Boucle sur chaque note:
For Each K In Rt
' Récupère l'indexe de l'évaluation:
i = K(5)
' Ajouter 2 points à la note sans dépasser 10:
Note = K(2) + 2
If Note > 10 Then Note = 10
' Mise à jour de l'élément dans la liste L_Données
Call ListUpdateItem(L_Données, "C2", "=", Note, i)
Next K
End If
Next E
' Supprime de la liste L_Données l'indexation:
Call ListAddcolumns(L_Données, -1)
' Actualise le tableau de synthèse:
Set L_Synthese = ListFromSQL(L_Données, "C0, C1, Count(C2), AVG(C2)", "GROUP BY C0, C1 ORDER BY C0, C1")
' Et l'affiche:
Call ListToTable(L_Synthese, "=", [TS_Synthese])
Un dernier exemple pour vous convaincre de l'utilité des requêtes SQL :
Á partir de cette liste, je souhaite une synthèse des 5 départements qui ont le plus de communes.
Dim List As Object
Dim Rt As Object
Set List = ListFromRange([TS_CP])
Set Rt = ListFromSQL(List, "TOP 5 C4, C6, C3, Count(C4)", "WHERE C4>'' GROUP BY C4, C3, C6 ORDER BY Count(C4) DESC")
Call ListToTable(Rt, "=", [TS_Synthese])
V. Conclusion▲
L’exercice purement théorique repris dans cette documentation donne une idée de ce qu’il est possible de faire pour gérer des données à plusieurs dimensions en utilisant la collection « ArrayList ».
Je suis bien conscient que vous obtenez le même résultat avec d’autres techniques de programmation (ou avec certaines formules d’Excel) et que je ne vous présente rien de révolutionnaire. Mon souhait est juste de vous faire découvrir une autre approche, avec ses avantages et ses inconvénients.
Toutefois, si le sujet vous intéresse, vous trouverez dans le fichier joint le module « ArrayList » qui contient les fonctions étudiées. Vous pouvez vous en inspirer pour les améliorer, les compléter, en écrire de nouvelles : n’hésitez pas à nous en faire profiter dans cette discussion.
Et pour plus d’informations sur les tableaux structurés je vous invite à consulter cette documentation : Fonctions en VBA pour gérer les Tableaux Structurés d’Excel.
Bonne programmation.
Laurent OTT.
2024.
VI. Remerciements▲
Je remercie User, gaby277 et Valérie LEVY pour leur relecture, leurs apports et leurs conseils.
Ainsi que toute l’équipe de Developpez.com qui participe à la maintenance du site.
VII. Le fichier joint▲
Le fichier ArrayList.xlsm contient les fonctions présentées ici dans le module « ArrayList ».
