VBA – Tableaux et ListObjects

Ce tutoriel montre comment travailler avec des tableaux et des objets de liste (ListObject) en VBA.

Tableaux et ListObjects en VBA

Les tableaux sont l’une des fonctions les plus utiles et les plus puissantes d’Excel. Dans ce tutoriel, nous allons voir comment utiliser VBA pour créer un tableau, ajouter un tri simple à un tableau, filtrer un tableau et effectuer d’autres tâches liées aux tableaux.

Créer un Tableau avec VBA

La méthode ListObjects.Add permet d’ajouter un tableau à une feuille de calcul, en fonction d’une plage spécifiée. Nous avons la plage illustrée dans ($A$1:$B$8) sur une feuille de calcul appelée Feuil1.

Le code suivant ajoutera un tableau, appelé Tableau1, à votre feuille de calcul, basé sur la plage ($A$1:$B$8) en utilisant le style de tableau par défaut :

Sub CréerUnTableauDansExcel()

ActiveWorkbook.Sheets("Feuil1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _
"Tableau1"

End Sub

Le résultat est le suivant :

Insertion d’une Colonne à la Fin du Tableau avec VBA

Vous pouvez utiliser la méthode ListColumns.Add afin d’ajouter une colonne à la fin de votre tableau. Notre tableau appelé Tableau1 est illustré ci-dessous.

Vous pouvez ajouter une colonne à votre tableau en utilisant le code suivant, qui ajoutera toujours une colonne à la fin du tableau :

Sub AjouterUneColonneALaFinDuTableau()

ActiveWorkbook.Sheets("Feuil1").ListObjects("Tableau1").ListColumns.Add

End Sub

Le résultat est le suivant :

Insertion d’une Rangée au Bas du Tableau avec VBA

Vous pouvez utiliser la méthode ListRows.Add pour ajouter une ligne au bas de votre tableau. Notre tableau, appelé Tableau1, est illustré ci-dessous.

Le code suivant ajoutera toujours une ligne au bas de votre tableau.

Sub AjouterUneLigneALaFinDuTableau()

ActiveSheet.ListObjects("Tableau1").ListRows.Add

End Sub

Le résultat est le suivant :

Ajout d’un Tri Simple des Données avec VBA

Vous pouvez trier un tableau avec VBA. Nous avons notre tableau appelé Tableau1 ci-dessous et nous pouvons utiliser VBA pour trier la colonne Ventes en ordre croissant.

Le code suivant permet de trier la colonne Ventes en ordre croissant.

Sub TriSimpleDuTableau()

Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields.Clear
Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields.Add _
    Key:=Range("Tableau1[[#All],[Ventes]]"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Le résultat est le suivant :

Filtrer un Tableau avec VBA

Vous pouvez également filtrer un tableau Excel à l’aide de VBA. Notre tableau s’appelle Tableau1 et nous souhaitons filtrer les données afin que seules les ventes supérieures à 1500 soient affichées.

Nous pouvons utiliser la méthode Autofilter, qui comporte cinq paramètres facultatifs. Comme nous souhaitons filtrer la colonne Ventes, qui est la deuxième colonne, nous définissons le paramètre « field » sur 2 et nous définissons le paramètre « operator » à la valeur « xlAnd », qui est utilisé pour les dates et les nombres.

Sub FiltreSimple()

 ActiveWorkbook.Sheets("Feuil1").ListObjects("Tableau1").Range.AutoFilter _
    Field:=2, _
    Criteria1:=">1500", _
    Operator:=xlAnd

End Sub

Le résultat est le suivant :

Effacer les Filtres avec la Méthode ShowAllData en VBA

Vous pouvez accéder à la méthode ShowAllData de la classe Worksheet afin d’effacer le ou les filtres. Si vous souhaitez effacer les filtres d’un tableau, vous devez d’abord sélectionner une cellule du tableau, ce que vous pouvez faire en VBA.

La méthode ShowAllData génère une erreur si aucun filtre n’est présent. On doit donc utiliser une logique conditionnelle afin de vérifier si un filtre a été appliqué dans la feuille de calcul. Le code suivant vous montre comment procéder :

Sub EffacerLesFiltres()

Worksheets("Feuil1").Range("Tableau1[[#Headers],[Ventes]]").Select

If ActiveWorkbook.Worksheets("Feuil1").FilterMode = True Then
    ActiveSheet.ShowAllData
End If

End Sub

Effacer tous les Filtres d’un Tableau Excel

Alternativement, vous pouvez accéder à la méthode ShowAllData de la classe ListObject sans avoir à sélectionner au préalable une cellule du tableau. Le code suivant vous montre comment procéder :

Sub EffacerTousLesFiltres()

ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").AutoFilter.ShowAllData

End Sub

Suppression d’une Ligne avec VBA

Vous pouvez supprimer une ligne dans le corps de données de votre tableau à l’aide de la méthode ListRows.Delete. Vous devez spécifier quelle ligne en utilisant le numéro de ligne. Nous avons la table suivante appelée Tableau1.

Supposons que vous vouliez supprimer la deuxième ligne dans le corps de données de votre table, le code suivant vous permettra de le faire :

Sub SupprimerUneLigne()

Worksheets("Feuil1").ListObjects("Tableau1").ListRows(2).Delete

End Sub

Le résultat est le suivant :

Suppression d’une Colonne avec VBA

Vous pouvez supprimer une colonne de votre tableau en utilisant la méthode ListColumns.Delete. Nous disposons de la table suivante, appelée Tableau1, présentée ci-dessous :

Pour supprimer la première colonne, vous devez utiliser le code suivant :

Sub SupprimerUneColonne()

Worksheets("Feuil1").ListObjects("Tableau1").ListColumns(1).Delete

End Sub

Le résultat est le suivant :

Conversion d’un Tableau en Plage avec VBA

Vous pouvez reconvertir un tableau en une plage normale à l’aide de VBA. Le code suivant vous montre comment reconvertir un tableau appelé Tableau1 en une plage :

Sub ReconvertirUnTableauEnPlageNormale()

Sheets("Feuil1").ListObjects("Tableau1").Unlist

End Sub

Ajout de Colonnes à Bandes et Mise en Forme de tous les Tableaux d’une Feuille de Calcul à l’Aide de VBA

Vous pouvez accéder à tous les tableaux de votre feuille de calcul à l’aide de la collection ListObjects. Dans la feuille ci-dessous, nous avons deux tableaux et nous souhaitons ajouter une colonne à bandes aux deux tableaux en même temps et changer la police de la section des données des deux tableaux en gras, en utilisant VBA.

Sub AjouterColonnesABandes()

Dim tbl As ListObject
Dim fc As Worksheet

Set fc = Worksheets("Feuil1")

For Each tbl In fc.ListObjects
    tbl.ShowTableStyleColumnStripes = True
    tbl.DataBodyRange.Font.Bold = True
Next tbl

End Sub

Le résultat est le suivant :

Création d’un Tableau dans Access en VBA à l’Aide de DoCmd.RunSQL

L’une des principales façons de créer un tableau dans Access en VBA consiste à utiliser la méthode DoCmd.RunSQL pour exécuter une requête d’action avec une instruction SQL.

Nous avons un bouton sur notre formulaire nommé ExempleFormulaire et lorsque nous cliquons sur le bouton, nous aimerions créer une table appelée TableProduits avec deux champs ou colonnes. Le premier champ serait le champ clé primaire appelé IDProduits et l’autre serait un champ appelé Ventes, similaire à l’exemple Excel précédent.

Afin de créer cette table, nous utiliserons le code suivant :

Private Sub btnCréerTableProduit_Click()

DoCmd.RunSQL "CREATE TABLE TableProduits " _
& "(IDProduit INTEGER PRIMARY KEY, Ventes Integer) ;"

End Sub

Le résultat est le suivant :

Filtrer un Tableau dans Access à l’Aide de VBA

Vous pouvez également filtrer un tableau dans Access à l’aide de la méthode DoCmd.ApplyFilter. Nous avons notre tableau simple ci-dessous dans Access appelé TableProduits.

Nous aimerions appuyer sur ce bouton dans notre formulaire et n’afficher que les ventes supérieures à 1500.

Nous utiliserions donc le code suivant pour ce faire :

Private Sub btnFiltre_Click()

DoCmd.OpenTable "TableProduits"

DoCmd.ApplyFilter , "[Ventes]>1500"

End Sub

Le résultat est le suivant :