Excel: Les tables de données en VBA

Pierre Fauconnier

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

- Titre : Excel: Les tables de données en VBA

- Auteur : Pierre Fauconnier

- Parution : 30 décembre 2018

- Mise à jour : 14 avril 2021

- Public visé :

- Licence : 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 et 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.

Sommaire

- I. Introduction

- I-A. Objectifs de ce tutoriel

- I-B. Visée pratique

- I-C. Public visé

- I-D. Prérequis

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

- I-F. Versions utilisées pour ce tutoriel

- I-G. Terminologie

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

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

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

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

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

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

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

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

- 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)

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

- 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

- III. Le modèle objet

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

- III-B. ListObject

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

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

- III-B-3. Instanciation

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

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

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

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

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

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

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

- III-B-6-a. Range et DataBodyRange

- III-B-6-b. ListColumns

- III-B-6-c. ListRows

- III-B-7. Points d’attention

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

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

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

- III-C. Range et DataBodyRange

- III-C-1. Range

- III-C-2. DataBodyRange

- III-D. ListColumn

- III-D-1. Index et nom

- III-D-2. Range et DataBodyRange

- III-D-3. Colonne formulée

- III-E. ListRow

- IV. Données externes et ListObject

- IV-A. Power Query

- IV-B. Connexions « ancienne méthode »

- V. Snipets (Procédures et fonctions utiles)

- V-A. Préambule

- V-B. Tableaux structurés

- 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

- VI. Conclusions

- VII. Lexique

- VIII. Remerciements

I - Introduction

I-A - Objectifs de ce tutoriel

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.

I-B - Visée pratique

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.

I-C - Public visé

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.

I-D - 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-E - 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, principalement ListRow et listColumn.

I-F - Versions utilisées pour ce tutoriel

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.

I-G - Terminologie

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.

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

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

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 directement 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. Ce n’est pas pénalisant car jamais utilisé dans le cadre d’une conception correcte des classeurs.

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 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 - « 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 formules nommées pointant vers des plages, les références structurées ne sont pas des formules 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 - 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.

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

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 !

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

Certaines méthodes ne se comportent pas de la même façon selon que la plage est liée ou non à un tableau structuré. Ainsi, comme nous l’avons vu plus haut, la méthode Delete de l’objet Range, qui décale par défaut les cellules situées à droite du tableau, décale les cellules situées sous la table de données lorsqu’elle porte sur une plage liée à un tableau structuré.

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

Lorsque la référence structurée n’existe pas, une exception est levée. Il conviendrait donc de prévoir ce cas au travers :

- Soit d’une gestion d’erreurs (On Error…), ce qui est loin d’être la solution idéale ;

- Soit d’une fonction qui teste que la référence structurée est valide.

Normalement, les noms des tableaux et colonnes ne sont pas modifiés durant la vie de l’application, et donc le code écrit ne devrait pas buter sur une référence inexistante. On se souviendra que l’on est en Excel et que l’utilisateur garde la main sur l’outil. Il conviendra donc de mettre en place une vérification systématique qu’un tableau est bien structuré comme on l’attend avant de le manipuler. Dans les annexes du tutoriel, j’illustrerai les codes génériques à utiliser pour cela.

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.

Ce tutoriel n’a pas du tout pour but d’établir un inventaire exhaustif des méthodes et propriétés du ListObject ni des objets qui le composent. Vous trouverez déjà cela sur les sites de Microsoft. 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 pas disponible sur toutes les versions).

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

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.

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-4-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.
Dans les faits, je ne crée donc jamais une table de données par VBA.

III-B-4-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-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-6-a - Range et DataBodyRange

Range : plage du tableau structuré complet, en ce compris la ligne d’entête et la ligne de total. Cette plage contient toujours au moins une ligne, même pour un tableau structuré vide ;

DataBodyRange : plage de données du tableau. Cette plage est Nothing si la table de données est vide.

III-B-6-b - ListColumns

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

III-B-6-c - ListRows

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

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 - Range et DataBodyRange

III-C-1 - Range

III-C-2 - DataBodyRange

DataBodyRange est un objet Range (plage de données) qui correspond à la plage de données du tableau, c’est-à-dire à l’ensemble des lignes de données composant le tableau. Lorsque le tableau n’est pas vide, DataBodyrange correspond à la plage portant le nom du tableau.

Lorsque le tableau est vide, qu’il ne contient donc aucune ligne de données, DataBodyRange est Nothing. Dans ce cas, cette plage ne correspond plus à la plage nommée du nom du tableau, puisque celle-ci contient toujours au moins une ligne, même lorsque le tableau est vide !

III-D - ListColumn

III-D-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 de la table de données.

L’index définit la position de la colonne dans la table, en partant bien sûr de la gauche. Il est volatile dans la mesure où le déplacement, la suppression ou l’ajout d’une colonne modifie l’index de plusieurs colonnes.

Le nom est bien entendu l’intitulé Excel de la colonne. Tant que la colonne n’est pas renommée, son nom peut être utilisé dans le code VBA qui manipule le ListObject.

Attention ! Votre code VBA n’est pas impacté par la modification du nom d’une colonne via l’interface Excel.

III-D-2 - Range et DataBodyRange

Comme pour un tableau, les proprités Range et DataBodyRange pointent vers des plages différentes :

- Range pointe vers la plage utilisée par toute la colonne, en ce compris la cellule d’entête et la cellule de total

- DataBodyRange, comme son nom l’indique, pointe vers la plage de données de la colonne, en excluant donc la cellule d’entête et la cellule de total.

III-D-3 - Colonne formulée

III-E - ListRow

IV - Données externes et ListObject

IV-A - Power Query

IV-B - Connexions « ancienne méthode »

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 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 - Tableaux structurés

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

VI - Conclusions

VII - Lexique

Early Binding :

Late Binding :

VIII - Remerciements