Excel: Les tables de données en VBA

Ce tutoriel poursuit plusieurs objectifs :

1. vous donner les clés de la manipulation des tables de données ou tableaux structurés en VBA ;
2. vous permettre d’écrire du code propre, pérenne, s’adaptant aux ajouts/suppressions de données dans vos tables de données ;
3. ouvrir la porte à une généralisation des manipulations sur les tables de données, en vous exposant des snipets génériques à mettre en application dans vos codes.

Ce tutoriel se veut essentiellement pratique. Il ne couvrira pas l’ensemble des méthodes et propriétés du ListObject, ce qui en ferait un doublon de la documentation de Microsoft (https://docs.microsoft.com/fr-fr/office/vba/api/excel.listobject). C’est mon utilisation quotidienne de l’outil, tant en formation qu’en développement auprès de mes clients, qui m’a amené à réaliser cette synthèse pratique.

Toute personne devant manipuler en VBA des données sous forme de table trouvera ici une documentation et des techniques lui permettant de travailler de façon simple et efficace avec les tables de données.

Il me semble évident qu’à minima, les bases du langage VBA doivent être maîtrisées. Il est également nécessaire de maîtriser les tables des données en Excel. Mon tutoriel https://fauconnier.developpez.com/tutoriels/tableaux-structures/ vous dira tout sur les tables de données (tableaux structurés) en Excel.

J’ai déjà cité mon tuto sur les tables de données en Excel, qui constitue la porte d’entrée de cet apprentissage. Le présent tutoriel est le deuxième de la série. Le troisième exposera la création d’un framework (cadre de travail) pour interagir avec les tables de données en VBA. Il proposera donc une couche d’abstraction qui permettra au programmeur de se dégager de l’utilisation directe du ListObject et de ses objets, principalement ListRow et listColumn.

La version utilisée pour ce tutoriel est Office 365. A part des différences esthétiques au niveau du ruban notamment, il n’y a pas différences majeures de version à partir de la version 2010 pour le sujet qui nous occupe.

Je considère qu’à l’heure où j’écris ce tutoriel, la version XL2010 est la plus ancienne qui puisse être utilisée sur un plan professionnel. Si ces utilisateurs de la version 2007 constatent des anomalies par rapport à mon tutoriel, ils seront gentils de me les signaler.

Mise à part une timide tentative de Microsoft en 2003 avec les listes de données, les tables de données n’existaient pas avant la version 2007, ce qui signifie que ce tutoriel ne concerne pas les versions antérieures à Excel 2007.

Tables de donnés, listes de données, listes, tableaux structurés… Devant l’ambiguïté du « nom officiel » donné par Microsoft à l’outil, à savoir « Tableau », des terminologies diverses ont fleuri sur la toile.

Deux semblent faire consensus : Table de données et Tableau structuré. J’utiliserai indifféremment l’une ou l’autre dans le cadre de ce tutoriel.

Dans Excel, nous pouvons utiliser les références structurées pour pointer vers nos tableaux structurés. VBA permet d’utiliser ces références, à l’instar des plages nommées classiques. On veillera toutefois à utiliser les versions anglosaxonnes des zones du tableau structuré lorsque l’on adresse les zones d’entête, de total, ou l’entièreté du tableau (zone d’entête + zone de données + zone de total).

Avant de découvrir le modèle objet ListObject, il sera intéressant d’apprendre à manipuler les différentes plages nommées que l’on peut utiliser grâce aux références structurées.

Sur base du tableau ci-dessus nommé t_Contacts, nous pouvons utiliser les références nommées dans nos codes VBA :

• la zone de données : Range("t_Contacts") ;
• tout le tableau, en ce compris les lignes d’entête et de total : Range("t_Contacts[#All]") ;
• les données d’une colonne : Range("t_Contacts[Prénom]") ;
• toute une colonne, en ce compris les lignes d’entête et de total : Range("t_Contacts[[#All],[Salaire]]") ;
• la ligne d’entête⁽¹⁾ : Range("t_Contacts[#Headers]") ;
• la ligne des totaux⁽¹⁾ : Range("t_Contacts[#Totals]") ;
• une ligne du tableau : Impossible de l’adresser directement grâce aux références structurées. Nous verrons plus loin comment adresser une ligne de données du tableau ⁽²⁾.

⁽¹⁾ Ces plages ne sont accessibles en VBA que lorsque les lignes correspondantes sont affichées dans le tableau. Si vous les adressez alors qu’elles ne sont pas accessibles, une erreur est générée. Nous verrons plus loin comment tester que ces lignes sont présentes pour un tableau donné.

⁽²⁾ A la différence d’Excel qui permet l’utilisation de t_Contacts[@] (Dans les faits, je n’en vois pas l’utilité), le VBA ne permet pas d’adresser la ligne complète d’un tableau située à même hauteur que la cellule courante de la feuille. Ce n’est pas pénalisant car jamais utilisé dans le cadre d’une conception correcte des classeurs.

Les références structurées sont utilisées comme des alias, déléguant ainsi à Excel le soin de déterminer vers quelle plage pointe la référence dont vous utilisez le nom. Le nom d’un tableau étant unique dans le périmètre du classeur, vous avez ainsi à votre disposition un outil puissant permettant d’écrire du code VBA qui tient compte des modifications structurelles du tableau sans avoir à être adapté.

En travaillant avec les références structurées, vous faites abstraction de la taille du tableau et de sa situation dans le classeur. Nous verrons également plus loin que l’ordre des colonnes n’est plus important lorsque l’on travaille avec les références structurées.

Les exemples suivants illustrent la stabilité de votre code VBA malgré les modifications structurelles du classeur. Ils s’appuient sur un tableau nommé t_Datas.

II-C-1. Le même code fonctionne quel que soit le nombre de lignes du tableau▲

Le code VBA calcule la somme des qtés pour un département donné sur les trois lignes existantes du tableau.

L’ajout de lignes dans le tableau ne nécessite aucune modification du code pour récupérer le nouveau résultat.

II-C-2. Le code est inchangé en cas de déplacement du tableau, même sur une autre feuille▲

Le déplacement du tableau au sein de la feuille, voire sur une autre feuille, ne pose aucun problème au code VBA qui continue à fonctionner de manière correcte.

II-C-3. Le même code peut fonctionner quel que soit l’ordre des colonnes dans le tableau▲

II-D-1. Référence structurée <> « plage nommée »▲

II-D-2. Supprimer les lignes d’un tableau structuré (vider la table de donnée)▲

Lorsque la table de données n’est pas vide, il est possible de supprimer rapidement toutes les lignes de données du tableau via la méthode Delete de l’objet Range.

Mais si le tableau structuré est vide, la méthode Delete va provoquer une erreur d’exécution 1004, de sorte que cette méthode n’est pas fiable. Lorsque nous étudierons le ListObject (l’objet Table de données de l’Object Model d’Excel), je vous donnerai un code générique et fiable pour la vidange d’une table de données.

 

Sélectionnez

Range("Mon Tableau").Delete

Notez que cette technique appliquée à la plage nommée du tableau fait remonter les cellules placées sous le tableau (et uniquement celles-là). Il n’est donc pas nécessaire de préciser le Shift, a contrario de Range.Delete qui, par défaut, opère un décalage vers la gauche des cellules situées à droite du tableau.

Vous remarquez la simplicité du code par rapport à une plage classique qu’il aurait fallu déterminer avec des fonctions telles que Range("A1048576").End(XlUp) et autres joyeusetés.

Grâce aux références structurées, vous acquérez la même simplicité de codage que lorsque vous utilisez des plages nommées, sans la complexité de la mise en place de ces dernières pour des plages dynamiques.

Puisqu’une référence structurée pointe vers un objet Range (Plage de cellules), vous avez à votre disposition toutes les propriétés et méthodes de cet objet.

Notez toutefois que certaines méthodes liées à un objet Range agissent différemment lorsque le Range est lié à un tableau structuré. C’est notamment le cas pour la suppression ou l’ajout de lignes ou de colonnes dans le tableau.

II-D-3. Référence structurée et tableau vide▲

Lorsque le tableau structuré est vide, la plage de données du tableau Range("NomTableau") contient une ligne. Dénombrer les lignes de la plage utilisant la référence structurée pose donc un problème. Nous verrons comment déterminer qu’un tableau est vide lorsque nous étudierons l’Object Model du tableau structuré.

II-D-4. Référence structurée et ListObject▲

II-D-5. Comportement différent des méthodes liées aux Range▲

II-D-6. Si la référence structurée n’existe pas▲

Rappel : Le modèle objet d’Excel est disponible via le menu Affichage du VBE ou le raccourci F2. En cherchant ListObject, l’explorateur d’objet vous affichera les propriétés et méthodes du ListObject.

On notera qu’il existe peu de méthodes sur un ListObject. On notera également que certaines propriétés ne sont présentes que sur les versions les plus récentes (Slicers, par exemple, qui permet la manipulation des segments et qui n’est pas disponible sur toutes les versions).

III-B-1. Position dans le modèle objet EXCEL▲

Un ListObject fait partie de la collection des Listobjects d’une feuille de calcul. Dès lors, bien que le nom d’un listobject soit unique dans le classeur, un ListObject est un élément qui dépend d’une feuille de calcul et en est donc un sous-objet.

III-B-2. Déclaration d’une variable ListObject▲

Un ListObject faisant partie du modèle objet Excel, il dépend de la bibliothèque Microsoft Excel et il n’y a donc aucune référence externe à inclure à votre projet si vous codez derrière un classeur Excel. Il est donc plus intéressant de travailler en Early Binding et de typer la variable lors de sa déclaration.

Si vous programmez en VBA dans une autre application d’Excel, vous devrez :

• Soit référencer la bibliothèque Excel ;
• Soit travailler en Late Binding.

 

Sélectionnez

Dim tContacts As ListObject

Bien entendu, la saisie semi-automatique vous aide.

Notez que, a priori, il n’y a pas d’autre bibliothèque exposant la classe ListObject. Si c’était le cas ou pour lever toute ambiguïté, vous pouvez préfixer ListObjet du nom de sa bibliothèque, à savoir Excel.

Il n’est pas possible de créer un ListObject lors de la déclaration. Vous ne pouvez donc pas utiliser le code Dim tContacts As New ListObject. La saisie est possible et syntaxiquement correcte (sans la saisie semi-automatique, ce qui devrait déjà vous alerter sur le problème potentiel) mais la ligne sera rejetée par le compilateur.

C’est normal puisque la création d’un ListObject impose l’utilisation de données que la déclaration ne permet pas de préciser.

III-B-3. Instanciation▲

Malgré le fait qu’ils soient visibles dans le gestionnaire des noms, les tableaux structurés ne sont pas des noms (au sens Excel) et ne sont donc pas repris dans la collection des noms du classeur ou de la feuille (Workbook.Names ou Worksheet.Name). Il n’est donc pas possible d’itérer sur cette collection pour vérifier l’existence d’un tableau structuré (ou d’une colonne d’une table de données) dans un classeur.

III-B-3-a. Via la feuille de calcul▲

Vous pouvez affecter un tableau structuré à une variable soit par son index au sein de la collection ListObjects de la feuille de calcul, soit par son nom au sein de cette collection.

 

Sélectionnez

  Set t = Worksheets("Datas").ListObjects(1)
  Debug.Print t.Name & " : " & t.Range.Address
  Set t = shDatas.ListObjects("t_Datas")
  Debug.Print t.Name & " : " & t.Range.Address
End Sub

L’index d’un tableau structuré dans la collection ListObjects d’une feuille est attribué à la création. Le premier créé reçoit l’index 1, le deuxième 2, etc. Toutefois, un décalage des index s’opère lors de la suppression. Dès lors, l’utilisation de l’index pour récupérer un ListObject est sujette à caution, la suppression d’un tableau d’une feuille amenant un décalage des index dans les objets de la collection ListObjects. Vous serez donc très prudent en utilisant cette technique dans vos codes. Dans les faits, ce n’est pas pénalisant puisque, sauf cas exceptionnels, vous ne placerez qu’une table de données par feuille.

Si la collection est vide (pas de tableaux structurés sur la feuille), que vous utilisez un index plus élevé que le nombre de tableaux structurés dans la collection ou qu’aucun tableau structuré ne porte le nom utilisé, une erreur 9 se produit (L’indice n’appartient pas à la sélection). Vous trouverez en fin de tutoriel une fonction permettant de récupérer un tableau sans générer d’erreur.

On remarque que ce code impose que l’on sache sur quelle feuille est placé le tableau structuré. Si la table de données est déplacée sur une autre feuille, ce code, parce qu’il précise la feuille soutenant le tableau structuré, sera caduque. Je déconseille donc cette approche.

III-B-3-b. Via une cellule qui fait partie du tableau structuré▲

Il est possible d’instancier un ListObject au départ d’une cellule de son objet Range. Depuis XL2007, l’objet Range dispose d’une propriété ListObject qui pointe vers le tableau structuré contenant cette cellule. Cette propriété « vaut » Nothing si la cellule est en dehors du tableau structuré.

Il est dès lors plus sécurisant, lorsque l’on connait le nom du tableau structuré à manipuler, de l’instancier via la référence structurée éponyme, car cette technique permet de faire abstraction de la feuille qui contient le tableau structuré, ce qui autorise le déplacement du tableau structuré sur une autre feuille sans modification de code.

Cette technique génère toutefois une erreur si le nom du tableau n’existe pas. Je propose à la fin du tutoriel une fonction pour trouver un listobject sans générer d’erreur.

III-B-4. Création par VBA▲

III-B-5. Transformation d’un ListObject en plage classique▲

A priori, il n’est pas utile de devoir transformer un ListObject en plage de données. Néanmoins, l’examen du Modèle Objet vu plus tôt dans le cours montre qu’un ListObject expose la méthode UnList qui le transforme en plage classique.

Tout comme lorsque cette opération se déroule dans Excel, les références structurées seront transformées en plages classiques en utilisant les références absolues.

Notez que, tout comme dans Excel, le format du tableau (lignes alternées, …) est maintenu, mais les couleurs de fond et de police deviennent des "vraies" couleurs, qui peuvent par exemple servir au filtre automatique alors que les couleurs d’alternance ne sont pas utilisables par les filtres. Notez également que les outils de filtre automatique sont supprimés lors de la transformation de la table en plage.

Dans les faits, je n’ai jamais transformé de table de données en plage de données et ne vois pas dans quel cas il serait utile de procéder de la sorte.

III-B-6. Propriétés intéressantes▲

III-B-7. Points d’attention▲

III-B-7-a. Plage d’une table de données et référence structurée▲

Le ListObject expose une propriété Range, qui renvoie la plage correspondant à Tableau[#All] et qui comprend donc :

• La ligne d’entête ;
• Les lignes de données ;
• La ligne de total.

Cette plage contient toujours au moins une ligne, même pour un tableau vide. De plus, l’objet Range d’un ListObject vide peut contenir jusqu’à 3 lignes :

• 1 ligne lorsque les lignes d’entête et de total sont invisibles ;
• 2 lorsqu’une de ces lignes est visible ;
• 3 si les deux lignes sont visible.

On voit donc que l’on ne peut pas compter sur ListObject.Range.Rows.Count pour déterminer qu’un ListObject est vide.

La plage d’un ListObject est différente de la plage éponyme ! La plage du ListObject reprend les lignes d’entête et de total si elles sont visibles, la plage éponyme ne reprenant que la zone de données.

C’est la plage DataBodyRange du ListObject qui correspond à la plage éponyme.

Chaque feuille de calcul du classeur possède sa « collection » ListObjets, même si aucun tableau structuré n’est présent sur la feuille. Dans ce cas, la collection ListObjects contient 0 éléments.

III-B-7-b. Listrows.Count et Range(« NomTableau »).Rows.Count▲

Lorsque le tableau est vide, il ne contient aucune ListRow, ce qui en soi est assez normal. Pourtant, la plage éponyme du tableau contient une ligne. Si on saisit une donnée dans la première ligne d’un tableau, le tableau contient une ListRow et la plage éponyme du tableau contient toujours 1 ligne. Tester le nombre de lignes de la plage nommée ne permet donc pas de tester que le tableau est vide. Pour tester que le tableau est vide, il faut vérifier que ListRows.Count est égal à 0 ou que le DataBodyRange est Nothing.

III-B-7-c. Vider une table de données▲

Aussi étrange que cela puisse paraître, ListObject n’expose pas de méthode permettant de supprimer toutes les lignes d’une table de données, autrement dit de la vider. C’est pour moi une lacune de l’objet qui va nous obliger à passer par un test alors que cette action est régulièrement effectuée.

Pour info, cette possibilité n’existe pas, en tant que telle, en Excel (sinon elle existerait en VBA). Il faut, en Excel, sélectionner toutes les lignes du tableau structuré puis les supprimer. Notez que cela peut se faire facilement et rapidement de deux façons par la sélection :

• Soit du tableau entier par CTRL+A dans le tableau structuré (attention à ne pas être sur une ligne d’entête ou de total car vous sélectionneriez toute la table de données ;
• Soit par la sélection d’une colonne entière par CTRL+ESPACE (même remarque pour les lignes d’entête et de total).

Pour vider une table de données, il faut supprimer sa plage de données (voir DataBodyRange au point suivant).

Ne confondez pas Supprimer toutes les lignes de données et Supprimer le tableau. La méthode Delete de ListObject supprime tout le tableau.

III-C-1. Range▲

III-C-2. DataBodyRange▲

III-D-1. Index et nom▲

III-D-2. Range et DataBodyRange▲

III-D-3. Colonne formulée▲

Dans le troisième opus dédié à la généralisation des manipulations courantes d’un tableau structuré, je vous parlerai de la création d’un framework (cadre de travail) qui créera une couche d’abstraction entre vos applications et le ListObject et qui permettra de créer des objets personnalisés liés aux ListObjects. Il simplifiera les manipulations de la table, mais aussi des objets listrow (lignes de la table) qui le composent.

Les codes donnés dans ci-dessous sont écrits en programmation procédurale et permettent déjà une simplification des manipulations d’un ListObject par le code. Cette approche systématique et générique du codage vous permettra de gagner du temps, tant dans l’écriture du code proprement dit que durant la maintenance de votre projet.

Pour faciliter la réutilisation du code dans vos différents projets, je vous propose de placer ces codes dans un module spécifique que vous pourrez insérer tel quel dans vos projets.

V-B-1. Récupérer un tableau structuré sur base de son nom▲

V-B-2. Créer une table de données ex nihilo▲

V-B-3. Lister les tableaux structurés d’un classeur▲

V-B-4. Déterminer qu’un tableau structuré est vide▲

V-B-5. Vider une table de données▲