Les objets tableaux (du menu Insertion \ tableau) sont une notion centrale et incontournable dans Excel. C’est pourquoi il est important de savoir les manipuler aussi en VBA.
Dans cet article, je vous partage des techniques de programmation éprouvées et puissantes pour manipuler les tableaux en VBA.
Créer un tableau Excel en VBA
En VBA, un tableau est modélisé par la classe ListObject, dont vous trouverez la description sur cette page de doc Microsoft.
Une feuille Excel gère les tableaux qu’elle contient via une collection de type ListObjects. Pour créer un nouveau tableau dans la feuille, il suffit d’ajouter un item dans cette collection :
Dim table As ListObject
Set table = ActiveSheet.ListObjects.Add(sourceType:=xlSrcRange, Source:=rg, XlListObjectHasHeaders:=xlYes)ActiveSheet désigne la feuille active du classeur Excel.
Tous les paramètres de la méthode Add de ListObjects sont facultatifs, mais l’exemple ci-dessus présente le cas le plus typique où on crée le tableau à partir d’une plage (nommée ici rg), en spécifiant que la première ligne correspond aux en-têtes de colonnes.
Il y a 2 cas de figures :
- Soit la plage initiale contient la ligne d’en-tête + les données. Auquel cas, vous obtenez directement un tableau complet avec un corps déjà rempli.
- Soit la plage initiale ne contient que la ligne d’en-tête. Auquel cas, vous obtenez un tableau avec un corps vide (représenté par une ligne vide). Vous pourrez alors ensuite ajouter des lignes au tableau en VBA, comme nous le verrons plus loin.
Notez que la plage à partir de laquelle vous créez le tableau peut être obtenue de la façon suivante :
Dim rg As Range
Set rg = Cells(2, 1).CurrentRegion
Cells(2, 1) désigne la cellule au croisement de la 2ème ligne et de la première colonne, c’est-à dire A2.
CurrentRegion permet de récupérer automatiquement toutes les cellules adjacentes. Ainsi, si vous avez par exemple des données jusqu’à la cellule C12, CurrentRegion renvoie la plage A2:C12.
C’est une méthode très puissante pour récupérer une plage (Range) de façon dynamique, sans spécifier son étendue exacte !
Définir l’apparence du tableau
Une fois le tableau créé, voici comment définir son apparence :
With table
.Range.HorizontalAlignment = xlCenter ' Alignement horizontal du contenu des cellules
.ShowTableStyleRowStripes = False ' Lignes sur couleurs de fond alternées
.ShowTotals = True ' Affichage de la ligne de totaux
.ShowAutoFilterDropDown = False ' Affichage des boutons de filtres automatiques sur les en-têtes
.TableStyle = "TableStyleLight9" ' Style général (parmi la liste des styles prédéfinis fournis par Excel)
End With
Modifier le contenu d’un tableau existant
Par « modifier le contenu », j’entends ici ajouter des lignes ou des colonnes au tableau en VBA.
Ajouter des lignes
Vous aurez parfois besoin de compléter votre tableau ligne par ligne à partir de données générées par le code, saisies par l’utilisateur ou autre… Voici comment faire cela en VBA :
Dim row as ListRow
Set row = table.ListRows.Add()
row.Range.Cells(1).Value = ...
row.Range.Cells(2).Value = ...
row.Range.Cells(3).Value = ...
Une ligne de tableau est représentée par la classe ListRow. Pour en créer une, on utilise la méthode Add de la collection ListRows (notez la similitude avec la façon de créer le tableau lui-même).
Une fois la ligne créée, on peut spécifier les valeurs de ses cellules.
Notez que Cells(n) représente ici la nième cellule de la ligne, quel que soit l’emplacement du tableau dans la feuille. On n’a donc ici que des références dynamiques, aucune référence de cellule en dur, ce qui permet au code de continuer à fonctionner correctement même si on déplace le tableau dans la feuille. Génial, non !?
Ajouter une colonne
Voici comment ajouter une colonne vide à la fin du tableau :
Dim col as ListColumn
Set Col = table.ListColumns.Add
col.Name = "Prix"
col.DataBodyRange.NumberFormat = "0.00" ' Format des données
Là encore, vous reconnaissez le même schéma que précédemment pour la création de la colonne. Mais cette fois, vous devez en plus donner un nom à la colonne, qui s’affichera dans l’en-tête.
La dernière ligne définit le format des données de la colonne (ici est des nombres avec 2 chiffres après la virgule). La propriété DataBodyRange fait référence au corps de la colonne sans son en-tête. En effet, le format ne doit pas s’appliquer à l’en-tête.
Remarque : pour ajouter une colonne à un endroit précis su tableau, et non pas à la fin, il suffit de préciser l’indice souhaité pour cette colonne en argument de la méthode Add :
Set Col = table.ListColumns.Add 2 ' Ajout d’une colonne en 2de position
Les colonnes qui suivent seront automatiquement décalées vers la droite.
Vider le corps du tableau
Si le contenu du tableau est amené à être régénéré plusieurs fois, vous devez le vider avant de le remplir à nouveau. Pour cela, utilisez le code suivant :
If Not table.DataBodyRange Is Nothing Then
table.DataBodyRange.Delete
end if
On utilise la même propriété que précédemment, DataBodyRange, mais appliquée au tableau pour récupérer son corps. L’appel de la méthode Delete permet ensuite de supprimer toutes les données.
Notez que si le corps du tableau est déjà vide, la propriété DataBodyRange vaut Nothing. C’est pourquoi il faut tester ce cas avant de supprimer les données.
Supprimer un tableau en VBA
Pour supprimer un tableau en VBA, utilisez par exemple le code suivant :
On Error Resume Next
ActiveSheet.ListObjects("tabArticles").Delete
On Error GoTo 0
Pour obtenir la référence du tableau, on utilise la collection ListObjects vue précédemment. Le tableau à supprimer peut être désigné soit comme ici par son nom, soit par son indice dans la collection.
Si le tableau n’existe pas (car il a déjà été supprimé par exemple), la ligne provoque une erreur. C’est pourquoi on l’encadre par un code de gestion d’erreur qui fait en sorte que le programme se poursuive sans afficher d’erreur.
Parcourir un tableau en VBA
Les tableaux Excel permettent de stocker des données de différents types : données saisies par l’utilisateur, données importées, listes fixes, paramètres…etc.
Pour exploiter les données d’un tableau dans le code VBA, vous devez donc parcourir le tableau en VBA. Voici différentes méthodes pour le faire :
Méthode 1 :
Dim rg As Range
Dim r As Long
Set rg = ActiveSheet.ListObjects("tabArticles").DataBodyRange
For r = 1 To rg.Rows.Count
id = rg(r, 1).Value
nom = rg(r, 2).Value
Next
Remarque : comme vu plus haut, si le tableau peut être vide, vous devez tout d’abord vérifier si DataBodyRange ne faut pas nothing avant de le parcourir.
Vous reconnaissez une fois de plus la propriété DataBodyRange, qui fait référence au corps du tableau. On stocke cette plage dans une variable de type Range pour y accéder ensuite facilement. Puis on parcourt les lignes de cette plage au moyen d’une boucle for.
Rg(r, c).Value permet d’accéder à la valeur de la cellule à l’intersection de la ligne r et de la colonne c. r et c sont relatifs au corps du tableau, c’est-à-dire que Rg(1,1) désigne la première cellule du corps du tableau, quel que soit l’emplacement de ce dernier dans la page.
Dans l’exemple ci-dessus, on stocke les valeurs lues dans des variables nommées id et nom, qui pourront ensuite être utilisées dans le programme pour divers traitements.
Méthode 2 :
Une autre méthode de parcours tout-à-fait équivalente est la suivante :
Dim row As ListRow
For Each row In ActiveSheet.ListObjects("tabArticles").ListRows
id = row.Range(1, 1).Value
nom = row.Range(1, 2).Value
NextListRows représente la collection des lignes du corps du tableau. Chaque ligne est représentée par un objet de type ListRow (au singulier). On accède aux cellules de la ligne via sa propriété Range.
Parcourir seulement une colonne
Si vous n’avez besoin de parcourir qu’une colonne du tableau, le code suivant est plus optimisé, car il charge moins de données en mémoire :
Set rg = ActiveSheet.ListObjects("tabArticles").ListColumns("Prix").DataBodyRangeListColumns représente la collection des colonnes du tableau. On récupère la colonne souhaitée en indiquant entre parenthèses son nom (ici « Prix ») ou son indice. Le parcours des lignes de cette colonne est ensuite identique à ce que nous avons vu plus haut.
En synthèse
Vous avez vu dans cet article les techniques permettant de créer, modifier, supprimer et parcourir des tableaux Excel en VBA.
Je vous conseille vivement d’utiliser autant que possible les objets tableaux dans vos fichiers Excel, car ils sont bien plus souples à manipuler que de simples plages de cellules. Votre code VBA n’en sera que plus fiable et facile à maintenir.
Vous avez notamment pu voir dans les extraits de code que nous n’avons utilisé aucune référence de cellule en dur. Ce qui signifie que si vous déplacez vos tableaux dans la feuille, votre code fonctionnera toujours ! ?
N’hésitez pas à me laisser un petit commentaire pour me dire ce que vous pensez de ces techniques.
Partager la publication "5 techniques pour manipuler les tableaux Excel en VBA"
