Les listes de données Excel en VBA

Pour commencer, nous allons créer un tableau, y accéder en VBA et voir comment il se compose.

Création

Nous partons de la plage normale suivante :

Pour créer notre liste de données, nous sélectionnons cette plage puis cliquons sur l’option "Tableau" du ruban dans le menu "Insertion". Une fenêtre de confirmation s’ouvre alors dans laquelle nous cochons avoir des en-têtes et validons.

Nous achevons cette étape en donnant un nom parlant et en ajoutant la ligne des totaux avec la coche, comme illustré ci-dessous :

Maintenant que notre tableau est créé, voyons comment il se compose.

Accès

Mais avant cela, nous allons y accéder en VBA. Pour ce faire, les objets feuilles possède une propriété ListObjects qui est une collection de tableaux.

Option Explicit

Public Const FEUILLE_CLIENTS_NOM As String = "Feuil1"
Public Const LO_CLIENTS_NOM As String = "TableauClients"

Public Sub Exemple_Acces_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Debug.Print (loClients.Name) ' TableauClients
End Sub

Le nom du tableau s’affiche bien lorsque nous exécutons ce code.

Structure

Comprendre comment un tableau est construit nous permet de savoir quelles propriétés utiliser pour faire les opérations voulues (ajouter une ligne dans le corps, masquer la ligne des totaux, …). Il y a deux façons de considérer la structure d’un tableau.

En-tête, Corps et Total

Premièrement, une liste de données est faite de trois blocs principaux : la ligne d’en-têtes, le corps et la ligne des totaux.

Ces blocs correspondent respectivement aux propriétés HeaderRowRange, DataBodyRange et TotalsRowRange comme montré ci-dessous :

L’ensemble fait partie de la plage globale accessible via la propriété Range.

Public Sub Exemple_Blocs_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Debug.Print (loClients.Range.Address) ' $A$1:$D$6
    Debug.Print (loClients.HeaderRowRange.Address) ' $A$1:$D$1
    Debug.Print (loClients.DataBodyRange.Address) ' $A$2:$D$5
    Debug.Print (loClients.TotalsRowRange.Address) ' $A$6:$D$6
End Sub

Lignes et colonnes

Deuxièmement, une liste de données est un ensemble de lignes et de colonnes.

Ces lignes correspondent à la propriété ListRows qui est une collection de ListRow. Comme vous pouvez le voir avec l’image ci-dessous, seules les lignes du corps du tableau font partie de la collection ListRows.

Ces colonnes quant à elles correspondent à la propriété ListColumns qui est une collection de ListColumn. Comme vous pouvez le voir avec l’image ci-dessous, la colonne entière est prise en compte.

Le code suivant nous confirme cela :

Public Sub Exemple_Lignes_et_colonnes_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)

    Dim lcColonneClient As ListColumn
    For Each lcColonneClient In loClients.ListColumns
        Debug.Print (lcColonneClient.Range.Address)
        ' $A$1:$A$6
        ' $B$1:$B$6
        ' $C$1:$C$6
        ' $D$1:$D$6
    Next lcColonneClient
    
    Dim lrLigneClient As ListRow
    For Each lrLigneClient In loClients.ListRows
        Debug.Print (lrLigneClient.Range.Address)
        ' $A$2:$D$2
        ' $A$3:$D$3
        ' $A$4:$D$4
        ' $A$5:$D$5
    Next lrLigneClient
End Sub

Colonnes

Même si un objet ListColumn englobe toute la colonne, nous pouvons obtenir les parties qui nous intéressent avec les propriétés adéquates : Name pour la valeur de l’en-tête, DataBodyRange pour la plage du corps, et Total pour la valeur de la ligne de totale :

Public Sub Exemple_Colonne_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)

    With loClients.ListColumns("Nombre de commandes")
        Debug.Print (.Name) ' Nombre de commandes
        Debug.Print (.DataBodyRange.Address) ' $D$2:$D$5
        Debug.Print (.Total) ' 20
    End With
End Sub

Pendant cette section, nous avons en appris plus sur la structure des liste de données.

Notre liste de données étant définie, il est temps d’apprendre à s’en servir.

Certaines opérations sont accessibles de manière haut niveau, c’est-à-dire que l’objet ListObject expose des propriétés ou des méthodes prêtes à l’usage pour faire directement ce que l’on souhaite.

D’autres en revanche nous demandent un peu plus de réflexion et de passer par des propriétés intermédiaires (comme celles étudiées dans la section précédente) voire de réaliser des opérations supplémentaires (tester que le corps du tableau n’est pas vide par exemple). Pour ces dernières, il sera préférable d’élaborer ses propres procédures hauts niveaux (procédure pour vider un objet liste de données par exemple) à l’avenir.

Lire, écrire et supprimer des données

Le but d’avoir une liste de données est de pouvoir manipuler des données.

Lecture des données

Bien souvent, nous voudrons lire des informations du ListObject.

Lecture d’une ligne

Si nous voulons lire une ligne en particulier, nous savons qu’il existe la collection ListRows vue plus tôt et c’est grâce à celle-ci que nous allons faire cela, en lui spécifiant un index.

Public Sub Exemple_Lire_Ligne()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Dim lIndexDerniereLigne As Long
    lIndexDerniereLigne = loClients.ListRows.Count ' 4
    
    If lIndexDerniereLigne > 0 Then
        ' Lecture dernière ligne
        Dim vContenuLigne As Variant
        vContenuLigne = loClients.ListRows(lIndexDerniereLigne).Range.Value
    
        Debug.Print (vContenuLigne(1, 1) & " " & vContenuLigne(1, 2)) ' DELANOÉ Michel
    Else
        Debug.Print ("Aucune Ligne")
    End If
End Sub

Lecture d’une colonne

Plus rarement, nous voudrons accéder aux données d’une colonne. Comme vu précédemment, nous utiliserons la collection ListColumns pour cela, avec un index ou un indice.

Public Sub Exemple_Lire_Colonne()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Dim lTotalCommandes As Long
    
    If loClients.ListRows.Count Then
        lTotalCommandes = WorksheetFunction.Sum(loClients.ListColumns("Nombre de commandes").DataBodyRange)
    End If
    
    Debug.Print ("Total de commandes : " & lTotalCommandes) ' Total de commandes : 20
End Sub

Lecture de l’intégralité du corps

Si maintenant je vous demande de lire l’intégralité du tableau, vous choisiriez sans doute de parcourir celui-ci ligne par ligne ou colonne par colonne et ça fonctionnerait très bien. Cependant, il existe une autre solution possible et qui est plus optimisée : celle de lire l’intégralité du tableau d’un coup !

Pour cela, nous allons utiliser la propriété DataBodyRange.

Public Sub Exemple_LireTout_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    If loClients.DataBodyRange Is Nothing Then
        Debug.Print ("Aucun contenu !")
        Exit Sub
    End If
    
    Dim vContenuTableau As Variant
    vContenuTableau = loClients.DataBodyRange.Value
    
    Dim lIndexLigne As Long
    For lIndexLigne = LBound(vContenuTableau, 1) To UBound(vContenuTableau, 1)
        Debug.Print (vContenuTableau(lIndexLigne, 1) & " " & vContenuTableau(lIndexLigne, 2))
        ' DUPONT Albert
        ' EIFFEL Bastien
        ' DELARUE Christine
        ' DELANOÉ Michel
    Next lIndexLigne
End Sub

Écriture des données

Ecrire des données, c’est soit en modifier, soit en ajouter.

Modification des données

Pour modifier des données en particulier, nous pouvons accéder aux différentes cellules et appliquer nos changements.

Si les modifications sont nombreuses, il peut être plus optimisé de charger le contenu dans un tableau en mémoire pour le modifier et ensuite l’écrire d’un coup à la place de l’ancien.

Ajout d’une ligne

Pour ajouter une ligne, nous allons ajouter un élément ListRow à la propriété ListRows à travers la méthode Add. Cette méthode peut prendre l’index de la position où insérer la ligne à créer, ce qui décale les autres lignes automatiquement.

Public Sub Exemple_AjouterLigne_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Dim vContenuLigne As Variant
    vContenuLigne = Array("ANDERSON", "Simon", CDate("25/08/1985"), 4)
    
    Dim lrLigne As ListRow
    Set lrLigne = loClients.ListRows.Add
    lrLigne.Range = vContenuLigne
End Sub

Ajout d’une colonne

Pour ajouter une colonne, nous utiliserons ici aussi la méthode Add cette fois ci-appliquée à la collection ListColumns.

Public Sub Exemple_AjouterColonne_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Dim lcColonne As ListColumn
    Set lcColonne = loClients.ListColumns.Add(3)
    
    lcColonne.Name = "Nom complet"
    lcColonne.DataBodyRange.FormulaR1C1 = "=TEXTJOIN("" "", TRUE, [@Nom], [@[Prénom]])"
End Sub

Ajout de plusieurs lignes

Là encore, dans un but d’optimisation, il est souhaitable d’écrire les plus grandes plages de données possibles en une fois.

Public Sub Exemple_AjouterLignes_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    Dim vContenuLignes(2, 4) As Variant
    vContenuLignes(0, 0) = "ANDERSON"
    vContenuLignes(0, 1) = "Simon"
    vContenuLignes(0, 2) = CDate("25/08/1985")
    vContenuLignes(0, 3) = 4
    vContenuLignes(1, 0) = "ZACHARIE"
    vContenuLignes(1, 1) = "Didier"
    vContenuLignes(1, 2) = CDate("25/08/1972")
    vContenuLignes(1, 3) = 0
    
    loClients.ListRows.Add.Range.Resize(UBound(vContenuLignes, 1), UBound(vContenuLignes, 2)).Value = vContenuLignes
End Sub

Suppression des données

Il faut parfois supprimer les données pour réinitialiser le tableau à un état de départ ou encore pour purger certaines données.

Par exemple, nous pouvons imaginer un tableau de commandes à préparer qui serait rempli chaque jour suite à une macro. Avant de le remplir, il faudrait alors supprimer les données de la veille.

De plus, nous pouvons aussi imaginer une macro qui supprimerait les clients inactifs de notre tableau en parcourant chaque ligne pour cela.

Suppression d’une ligne ou d’une colonne

La méthode Delete appliquée à une ligne ou une colonne supprime celle-ci. Il faut alors indiquer l’indice ou la clef de l’élément à supprimer.

loClients.ListRows(loClients.ListRows.Count).Delete ' Suppression de la dernière ligne
loClients.ListColumns("Nom complet").Delete ' Suppression de la colonne 'Nom complet'

Suppression du contenu du corps

Pour supprimer l’intégralité du corps du tableau, nous utiliserons la méthode Delete là-encore, cette-fois appliquée au DataBodyRange.

If Not loClients.DataBodyRange Is Nothing Then
    loClients.DataBodyRange.Delete
End If

Filtrer et trier

Microsoft Excel permet de filtrer, mais aussi de trier les données.

Pour le faire en VBA, j’utilise en général l’enregistreur de macro afin de récupérer la syntaxe.

Ajout et suppression filtres

Ajout filtre

Ajouter un filtre se fait via la méthode AutoFilter d’une plage.

Public Sub Exemple_AjoutFiltre_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
        
    loClients.Range.AutoFilter Field:=4, Criteria1:=">=5", Operator:=xlAnd
End Sub

Suppression filtres

La méthode ShowAllData appliquée à la propriété Autofilter enlève les filtres.

Public Sub Exemple_SuppressionFiltres_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    loClients.AutoFilter.ShowAllData
End Sub

Ajout tri

Nous pouvons ajouter un critère de tri SortFields de cette manière :

Public Sub Exemple_AjoutTri_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
        
    loClients.Sort.SortFields.Add2 Key:=Range( _
        "TableauClients[[#Headers],[#Data],[Nombre de commandes]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End Sub

Suppression tris

La méthode Clear sur les critères de tri SortFields les supprime.

Public Sub Exemple_SuppressionTri_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    loClients.Sort.SortFields.Clear
End Sub

Mettre en forme

Plusieurs propriétés sont à la disposition du développeur pour interagir avec la mise en forme du tableau.

Style du du tableau

La propriété TableStyle nous permet de lire ou modifier le style du tableau.

Public Sub Exemple_Style_ListObject()
    Dim loClients As ListObject
    Set loClients = Sheets(FEUILLE_CLIENTS_NOM).ListObjects(LO_CLIENTS_NOM)
    
    With loClients
        Debug.Print (.TableStyle) ' TableStyleMedium2
        .TableStyle = "TableStyleMedium3"
    End With
End Sub

Mise en avant colonnes

Il est possible de mettre en avant visuellement la première et la dernière colonne. Cela peut être utile pour distinguer visuellement la donnée du reste (identifiant, total, …)

Première colonne

La propriété de type booléenne ShowTableStyleFirstColumn nous permet de lire ou modifier la mise en avant de la première colonne.

loClients.ShowTableStyleFirstColumn = True

Dernière colonne

La propriété de type booléenne ShowTableStyleLastColumn nous permet de lire ou modifier la mise en avant de la dernière colonne.

loClients.ShowTableStyleLastColumn = True

Alternance mise en forme

Par défaut, l’alternance de la mise en forme se fait par ligne du corps du tableau, mais nous pouvons aussi changer cela. Le but est d’avoir une distinction plus nette des données.

Lignes à bandes

La propriété de type booléenne ShowTableStyleRowStripes nous permet de lire ou modifier l’alternance de mise en forme au niveau des lignes du corps du tableau.

loClients.ShowTableStyleRowStripes = False

Colonnes à bandes

La propriété de type booléenne ShowTableStyleColumnStripes nous permet de lire ou modifier l’alternance de mise en forme au niveau des colonnes du corps du tableau.

loClients.ShowTableStyleColumnStripes = True

Remarquons qu’il est possible de combiner les deux, l’un n’excluant pas l’autre. Toutefois, nous perdons en ergonomie et ça n’a guère de sens de le faire.

Masquer ou Afficher

Nous pouvons masquer ou afficher certaines parties de la liste de données en VBA.

Ligne d’entête

La propriété de type booléenne ShowHeaders nous permet de lire ou modifier la visibilité de la ligne des en-têtes.

loClients.ShowHeaders = Not loClients.ShowHeaders ' Toggle visibilité ligne d'en-tête

Ligne Total

La propriété de type booléenne ShowTotals nous permet de lire ou modifier la visibilité de la ligne des totaux.

loClients.ShowTotals = Not loClients.ShowTotals ' Toggle visibilité ligne Total

Au fil de cette section, nous avons vu différentes façons d’utiliser des ListObjects dans nos macros.