5 techniques pour manipuler les tableaux Excel en VBA

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.
Excel tableaux plein et vide

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 !

Excel currentregion
CurrentRegion permet de désigner une région de données adjacentes de la feuille Excel

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.

Cet article devrait aussi vous intéresser :  Suivre sa consommation électrique avec Excel

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 

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 :

Cet article devrait aussi vous intéresser :  15 raccourcis clavier et souris hyper efficaces pour Excel et Calc

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    
Next

ListRows 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").DataBodyRange

ListColumns 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.

vote
Notez cet article
Si vous avez aimé cet article, un like et un partage me feraient très plaisir ;)
  •   
  •   
  •  
  •   
  •   
S’abonner
Notifier de
guest

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

0 Commentaires
Commentaires en ligne
Voir tous les commentaires