Travailler avec les Tableaux Excel

Un tableau Excel — ce que VBA appelle un ListObject — est une plage nommée et auto-extensible avec des flèches de filtre intégrées, des lignes alternées et des références de colonnes structurées. Si vos données ne sont pas déjà sous forme de tableau, sélectionnez n'importe quelle cellule à l'intérieur et appuyez sur Ctrl+T, ou laissez VBA en créer un avec ListObjects.Add.

Référence à un ListObject

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Déclarer tbl As ListObject (plutôt que simplement As Range) permet d'accéder à toutes les fonctionnalités spécifiques aux tableaux utilisées dans le reste de cette section — ListRows, ListColumns et la Total Row proviennent toutes du fait que l'objet est correctement typé. Notez la distinction entre les deux lignes Debug.Print : tbl.Range couvre l'ensemble du tableau, y compris la ligne d'en-tête, tandis que tbl.DataBodyRange ne couvre que les données situées en dessous.

Presque toutes les opérations — ajout d'une ligne, somme d'une colonne, boucle sur les enregistrements — doivent utiliser DataBodyRange, précisément pour éviter que le texte de l'en-tête ne soit accidentellement traité comme une ligne de données.

Ajout de lignes

ListRows.Add ajoute une nouvelle ligne directement sous le tableau — et surtout, toutes les formules à référence structurée dans les autres colonnes s'étendent automatiquement à cette nouvelle ligne, ce qui constitue l'un des plus grands avantages pratiques d'un tableau par rapport à une plage classique.

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add crée la ligne vide et la retourne sous forme d'objet ListRow, c'est pourquoi les six lignes suivantes écrivent dans newRow plutôt que dans tbl. newRow.Range(1, 1) signifie « ligne 1 de cette nouvelle ligne spécifique, colonne 1 » — l'indexation recommence à 1 pour la nouvelle ligne elle-même, elle ne compte pas depuis le haut du tableau. C'est une bien meilleure pratique que de chercher la dernière ligne de la feuille avec End(xlUp) et d'écrire manuellement une colonne après : ListRows.Add place toujours la nouvelle ligne à l'intérieur des limites du tableau, de sorte que toute ligne de total, formule à référence structurée ou règle de mise en forme conditionnelle appliquée au tableau s'étend automatiquement pour l'inclure.

Mise à jour des enregistrements

Pour mettre à jour une ligne existante, parcourez DataBodyRange et faites une correspondance sur une colonne clé — ici, mise à jour de la cible de la région Central pour février après une révision budgétaire :

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

Il s'agit du même schéma de haut en bas, arrêt à la première correspondance, que pour la logique conditionnelle, appliqué ici à de vraies lignes au lieu de valeurs codées en dur : la boucle vérifie le mois et la région ensemble avec And, et dès que les deux correspondent, elle met à jour la colonne Target et appelle Exit For pour ne pas continuer à parcourir inutilement les lignes restantes. Utiliser tbl.DataBodyRange.Cells(r, 1) plutôt qu'une référence Cells au niveau de la feuille permet de limiter la numérotation des lignes aux seules données du tableau — la ligne 1 ici signifie la première ligne de données, quel que soit le numéro de ligne réel où commence le tableau sur la feuille.

Référence aux colonnes du tableau

Les références structurées — ListColumns("Name") — sont plus lisibles et plus robustes que le comptage des colonnes par numéro, surtout lorsqu'un tableau est modifié et que les colonnes bougent :

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") trouve la colonne par son en-tête plutôt que par sa position, donc le code continue de fonctionner même si Profit passe plus tard de la colonne E à la colonne F — compter Cells(r, 5) à la main échouerait silencieusement dans ce cas. Application.WorksheetFunction permet à VBA d'appeler directement les fonctions Excel classiques comme SOMME et MOYENNE sur un objet Range, au lieu d'écrire une boucle manuelle avec un total cumulatif, ce qui réduit le code et limite les risques d'erreur d'index.

Tâche

1. Ouvrir Section_4_Reports.xlsx, l’enregistrer sous Section_4_Reports.xlsm, et confirmer que les données de la feuille Reports sont une Table nommée tblReports (cliquez sur n’importe quelle cellule à l’intérieur — l’onglet Création de tableau doit apparaître).
2. Écrire une macro qui ajoute une ligne pour avril pour chacune des cinq régions en utilisant ListRows.Add (cinq nouvelles lignes au total, chiffres inventés acceptés).
3. Écrire une seconde macro utilisant ListColumns("Sales").DataBodyRange et WorksheetFunction.Sum pour afficher le total des ventes de toutes les lignes dans la fenêtre Exécution immédiate.