Excel: Les tables de données en VBA

Tutoriel d’exploration des tables de données (tableaux structurés) en VBA

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

I-A. Objectifs de ce tutoriel

Ce tutoriel poursuit plusieurs objectifs :

  1. vous donner les clés de la manipulation des tables dedonné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.

I-B. Public visé

Toute personne devant manipuler 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.

I-C. Prérequis

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.

I-D. Tutoriels liés à celui-ci

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 (ListRow et listColumn).

I-E. Versions utilisées pour ce tutoriel

J’ai commencé la rédaction du tuto en 2016 installée localement, et je l’ai poursuivi en Excel 365 installé localement. A part des différences esthétiques au niveau du ruban notamment, il n’y a pas de différences entre ces versions pour le sujet qui nous occupe.

Les utilisateurs d’une version 2007 seront attentifs au fait que les références structurées 2007 n’ont pas une syntaxe identique aux versions ultérieures. Si ces utilisateurs constatent des anomalies par rapport à mon tutoriel, ils seront gentils de me les signaler.

II. Manipulations du tableau grâce aux références structurées

II-A. Références structurées

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 de l’entièreté du tableau.

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.

II-B. Schéma des références structurées et utilisation en VBA

Image non disponible

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(1) : Range("t_Contacts[#Headers]") ;
  • la ligne des totaux(1) : Range("t_Contacts[#Totals]") ;
  • une ligne du tableau : Impossible de l’adresser grâce aux références structurées. Nous verrons plus loin comment adresser une ligne de données du tableau (2).

(1) 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é.

(2) 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. Dans les faits, ce n’est pas pénalisant.

II-C. Avantages de l’utilisation des références structurées

Les références structurées sont utilisées comme des alias, déléguant ainsi à Excel 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 permet de faire face aux modifications structurelles du tableau.

En travaillant avec les références structurées, vous faites abstraction de la taille du tableau et de sa situation dans le classeur.

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.

Image non disponible

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

Image non disponible

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.

Image non disponible

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

Image non disponible

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

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

II-D. « Pièges » des références structurées

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

Il est intéressant de noter ici que parler de « plage nommée » est un abus de langage. En fait, une plage nommée est une formule nommée qui pointe vers une plage.

Bien que vous les utilisiez dans votre code de la même manière que les plages nommées, les références structurées ne sont pas des plages nommées. Vous ne les retrouverez pas dans la collection des noms associée au classeur.

Nous verrons plus loin comment lister les tableaux structurés d’une feuille ou d’un classeur.

II-D-2. 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 problème. Nous verrons comment déterminer qu’un tableau est vide lorsque nous étudierons l’Object Model du tableau structuré.

E:\Temp\2018-12-25_082128.png

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

L’objet renvoyé lorsque vous utilisez une référence structurée pointant vers une table de données est une plage de données (un objet Range) et pas un ListObject ! Il en est de même pour les parties d’un tableau appelables par des références structurées.

Un Range pointant vers une référence structurée est toujours un objet Range et jamais un listobjet, un listrow ou un listcolum !

III. Le modèle objet

III-A. L’outil d’exploration d’un objet dans le VBE (Visual Basic Editor)

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.

Image non disponible
Image non disponible

Ce tutoriel n’a pas du tout pour but d’établir un inventaire exhaustif du ListObject et des objets qui le composent. Il m’a semblé plus intéressant de mettre en évidence les méthodes et propriétés utiles pour la plupart des utilisations professionnelles habituelles de cet outil.

Il me semble toutefois extrêmement intéressant que vous parcouriez de temps en temps ce model object pour découvrir, peut-être, des outils qui pourront vous être utiles. L’apprentissage de notions de ce genre passe aussi, et toujours, par une curiosité aiguisée…

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 donc disponible que depuis la version 2013)

III-B. ListObject

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 de celle-ci et en est donc un sous-objet.

E:\Temp\2018-12-30_192417.png

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. Il est donc plus intéressant de travailler en Early Binding et de typer la variable lors de sa déclaration.

 
Sélectionnez
Dim tContacts As ListObject

Bien entendu, la saisie semi-automatique vous aide.

E:\Temp\2018-12-30_201138.png

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.

E:\Temp\2018-12-30_201154.png

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.

E:\Temp\2018-12-30_201623.png

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é 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.

E:\Temp\2018-12-30_205658.png
 
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
E:\Temp\2018-12-30_205707.png

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.

Image non disponible

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.

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é.

E:\Temp\2018-12-30_214213.png
E:\Temp\2018-12-30_214244.png
E:\Temp\2018-12-30_214303.png
E:\Temp\2018-12-30_214320.png

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.

E:\Temp\2018-12-30_215514.png
E:\Temp\2018-12-30_214700.png

III-B-4. Propriétés intéressantes

III-B-4-a. Range

III-B-4-b. DataBodyRange

III-B-4-c. ListColumns

Cette propriété sera examinée plus en détails dans la suite du tutoriel.

III-B-4-d. ListRows

Cette propriété sera examinée plus en détails dans la suite du tutoriel.

III-B-5. Points d’attention

III-B-5-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]

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.

E:\Temp\2018-12-30_211014.png
E:\Temp\2018-12-30_211045.png

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-5-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.

Image non disponible
Image non disponible

III-B-6. Création par VBA

III-B-6-a. Création « ex nihilo »

Il est possible de créer un tableau structuré sur une feuille de calcul par programmation. La technique consiste à ajouter un objet à la collection ListObjects de la feuille concernée et en précisant certaines propriétés du nouveau ListObject lors de l’ajout.

Dans la pratique, ça me semble être un défaut de conception de devoir créer un tableau structuré par VBA. Si vous possédez les infos vous permettant de créer un tableau structuré par code (emplacement, nombre de colonnes, …), alors, créez-le en Excel et manipulez-le en VBA.

III-B-6-b. Transformation d’une plage en tableau structuré

A peine plus intéressante pourrait être la technique de transformer une plage existante en tableau structuré. La technique revient dans les faits à ajouter un ListObject à la collection des ListObjects en s’appuyant sur la plage existante.

III-B-7. Manipulations

III-B-7-a. 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 puis les supprimer. Notez que cela peut se faire facilement par la sélection :

  • CTRL+A dans le tableau pour en sélectionner toutes les données (attention à ne pas être sur une ligne d’entête ou de total car vous sélectionneriez tout le tableau.
  • Sélection d’une des données d’une colonne par CTRL+ESPACE (même remarque pour les entêtes)

Puis la suppression.

Dans le dernier chapitre, je donnerai un code qui permet de réaliser cela facilement.

La méthode Delete de ListObject supprime tout le tableau.

III-C. ListColumn

III-C-1. Index et nom

Toute ListColumn possède un index et un nom. Il est possible de trouver l’un lorsqu’on a l’autre, puisque les deux données sont uniques dans la collection ListColumns.

L’index définit sa position dans la table, en partant bien sûr de la gauche.

Le nom est bien entendu l’intitulé Excel de la colonne.

III-D. ListRow

IV. Données externes et ListObject

V. Snipets (Procédures et fonctions utiles)

V-A. Préambule

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. Il simplifiera les manipulations de la table, mais aussi des objets listrow (lignes de la table) qui le composent.

V-B. Tableaux structurés

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

V-B-2. Créer un tableau 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

VI. Conclusions

VII. Remerciements

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2018 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.