Ce didacticiel montrera comment travailler avec des tables et des objets de liste dans VBA.
Tables VBA et ListObjects
Les tableaux sont l'une des fonctionnalités les plus utiles et les plus puissantes d'Excel. Dans ce didacticiel, nous expliquerons 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 peut ajouter un tableau à une feuille de calcul, en fonction d'une plage dans cette feuille de calcul. Nous avons la plage indiquée dans ($A$1:$B$8) sur une feuille de calcul appelée Sheet1.
Le code suivant ajoutera une table, appelée Table1 à votre feuille de calcul, en fonction de la plage ($A$1:$B$8) en utilisant le style de table par défaut :
| 123456 | Sous CreateTableInExcel()ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _"Tableau 1"Fin du sous-marin |
Le résultat est:
Insérer 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. Nous avons notre table appelée Table1 ci-dessous.
Vous pouvez ajouter une colonne à votre tableau en utilisant le code suivant, qui ajoutera toujours une colonne à la fin du tableau :
| 12345 | Sub AddColumnToTheEndOfTheTable()ActiveWorkbook.Sheets("Feuille1").ListObjects("Table1").ListColumns.AddFin du sous-marin |
Le résultat est:
Insérer une ligne en bas du tableau avec VBA
Vous pouvez utiliser la méthode ListRows.Add pour ajouter une ligne au bas de votre tableau. Nous avons notre table appelée Table1 ci-dessous.
Le code suivant ajoutera toujours une ligne au bas de votre tableau.
| 12345 | Sub AddRowToTheBottomOfTheTable()ActiveSheet.ListObjects("Table1").ListRows.AddFin du sous-marin |
Le résultat est:
Ajout d'un tri simple avec VBA
Vous pouvez trier un tableau avec VBA. Nous avons notre table appelée Table1 ci-dessous et nous pouvons utiliser VBA pour trier la colonne des ventes du plus bas au plus élevé.
Le code suivant triera la colonne Ventes par ordre croissant.
| 12345678910111213141516171819 | Sous-TrierSimpleSurLaTable()Plage("Table1[[#En-têtes],[Ventes]]").SélectionnezActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _Clé :=Plage("Table1[[#All],[Sales]]"), SortOn :=xlSortOnValues, Order := _xlAscending, DataOption:=xlSortNormalAvec ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.En-tête = xlOui.MatchCase = Faux.Orientation = xlHautVersBas.SortMethod = xlPinYin.AppliquerTerminer parFin du sous-marin |
Le résultat est:
Filtrer une table avec VBA
Vous pouvez également filtrer un tableau Excel à l'aide de VBA. Nous avons notre table appelée Table1 et nous aimerions filtrer la table afin que seules les ventes supérieures à 1500 soient affichées.
Nous pouvons utiliser la méthode Autofilter, qui a cinq paramètres facultatifs. Puisque nous aimerions filtrer la colonne Sales qui est la deuxième colonne, nous définissons le champ sur 2 et nous utilisons le paramètre d'opérateur xlAnd, qui est utilisé pour les dates et les nombres.
| 123456 | Sous-filtre simple()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field :=2, Criteria1 := _">1500", Opérateur :=xlAndFin du sous-marin |
Le résultat est:
Effacer le filtre avec la méthode ShowAllData dans VBA
Vous pouvez accéder à la méthode ShowAllData de la classe Worksheet afin d'effacer le filtre. Si vous souhaitez effacer le ou les filtres d'un tableau, vous devez d'abord sélectionner une cellule du tableau, ce que vous pouvez faire dans VBA.
La méthode ShowAllData générera une erreur si l'on n'utilise pas de 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 :
| 123456789 | Sous ClearingTheFilter()Plage("Table1[[#En-têtes],[Ventes]]").SélectionnezSi ActiveWorkbook.Worksheets("Sheet1").FilterMode = True ThenActiveSheet.ShowAllDataFin siFin du sous-marin |
Effacer tous les filtres d'un tableau Excel
Vous pouvez accéder à la méthode ShowAllData de la classe ListObject sans avoir à sélectionner au préalable une cellule dans le tableau. Le code suivant vous montre comment procéder :
| 123 | Sous ClearAllTableFilters()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllDataFin du sous-marin |
Supprimer une ligne avec VBA
Vous pouvez supprimer une ligne dans le corps de données de votre table à l'aide de la méthode ListRows.Delete. Vous devez spécifier quelle ligne en utilisant le numéro de ligne. Nous avons le tableau suivant appelé Tableau1.
Supposons que vous vouliez supprimer la deuxième ligne du corps de données de votre table, le code suivant vous permettrait de le faire :
| 12345 | Sous SupprimerARow()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows(2).DeleteFin du sous-marin |
Le résultat est:
Supprimer une colonne avec VBA
Vous pouvez supprimer une colonne de votre table à l'aide de la méthode ListColumns.Delete. Nous avons le tableau suivant appelé Tableau1 ci-dessous :
Pour supprimer la première colonne, vous utiliseriez le code suivant :
| 12345 | Sub DeleteAColumn()ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).DeleteFin du sous-marin |
Le résultat est:
Reconversion d'une table en plage dans VBA
Vous pouvez reconvertir une table en une plage normale à l'aide de VBA. Le code suivant vous montre comment reconvertir une table appelée Table1 en plage :
| 12345 | Sous-conversionATableBackToANormalRange()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").UnlistFin du sous-marin |
Ajout de colonnes à bandes et formatage à tous les tableaux d'une feuille de calcul à l'aide de VBA
Vous pouvez accéder à toutes les tables de votre feuille de calcul à l'aide de la collection ListObjects. Dans la feuille ci-dessous, nous avons deux tableaux et nous aimerions ajouter une colonne à bandes aux deux tableaux à la fois et changer la police de la section de données des deux tableaux en gras, à l'aide de VBA.
| 12345678910111213 | Sub AddingBandedColumns()Dim tbl As ListObjectDim sht comme feuille de travailDéfinir sht = ThisWorkbook.ActiveSheetPour chaque tbl dans sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueTableau suivantFin du sous-marin |
Le résultat est:
Création d'une table dans Access en VBA à l'aide de DoCmd.RunSQL
L'un des principaux moyens de créer une table 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 exemple de formulaire et lorsque nous cliquons sur le bouton, nous aimerions créer une table appelée ProductsTable avec deux champs ou colonnes, l'un serait le champ de clé primaire appelé ProductsID et l'autre serait un champ appelé Sales.
Pour créer ce tableau, nous utiliserons le code suivant :
| 123456 | Sous-cmdCreateProductsTable_Click () privéDoCmd.RunSQL "CREATE TABLE ProductsTable " _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Fin du sous-marin |
Le résultat est:
Filtrage d'une table dans Access à l'aide de VBA
Vous pouvez également filtrer une table dans Access à l'aide de la méthode DoCmd.ApplyFilter. Nous avons notre table simple montrée ci-dessous dans Access appelée ProductsTable.
Nous aimerions appuyer sur ce bouton sur notre formulaire et ensuite ne voir que les ventes supérieures à 1500.
Donc, nous utiliserions le code suivant pour ce faire :
| 1234567 | Sous-cmdFilter_Click () privéDoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter , "[Ventes]>1500"Fin du sous-marin |
Le résultat est:
