IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs

Noter ce billet
par , 08/11/2020 à 20h33 (7113 Affichages)
Salut.

En 2020, on travaille évidemment les données au sein d'un tableau structuré (voir mon tuto à ce sujet). La manipulation des tableaux structurés par VBA permet d'écrire un code qui n'est pas lié à la position des données dans le classeur. Que ce soit par les références structurées ou par le tableau structuré lui-même (le Listobject), le code VBA qui doit être écrit peut être rendu générique en sortant du code les données "métier" (nom du tableau, noms des colonnes, position de la ligne ajoutée, ...) et en les faisant "entrer" dans la procédure par une liste d'arguments.

On manipulera donc un tableau structuré par code et le tableau structuré porte le doux nom de ListObject dans le modèle objet Excel.

Je vous propose ici une procédure générique d'ajout d'une ligne dans un tableau structuré. Ce code pourra être enregistré dans un module nommé par exemple TableManager qui contiendrait tous les codes génériques de manipulation des tableaux structurés. C'est une bonne pratique que de se créer des modules génériques.

Je baserai mes explications sur un tableau structuré nommé t_Personnel, illustré ci-dessous. Ce tableau structuré contient une ligne de total, dont la présence ici sert à illustrer que la méthode Add de l'objet ListObject supprime le besoin de savoir s'il y a une ligne de total présente ou non.


Avant de vous exposer le code, quelques mots d'explications s'imposent.

L'idée ici, est de travailler avec un objet ListRow de transférer les valeurs reçues dans les colonnes de la nouvelle plage créée.

Pour cela, on va d'abord récupérer le tableau structuré VBA (le ListObject) au sein d'une variable:
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
Set l = Range("NomTableau").ListObject

Un tableau structuré en VBA expose une méthode qui renvoie une nouvelle ligne de données du tableau structuré. Cette ligne est ajoutée soit à la fin du tableau (après la dernière ligne existante du tableau) ou insérée à un emplacement précis...
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
ListObject.ListRows.Add(Position)

Cette méthode Add de ListObject renvoie un objet de type ListRow qui pointe vers la ligne du tableau structuré ajoutée.

Un object ListObject expose également la liste des colonnes (des objets Listcolumn) que l'on peut adresser soit par le nom, soit par l'index (la position dans le tableau). L'idée ici est de déterminer la position de la cellule sur la ligne ajoutée grâce à la priorité Index d'un ListColumn("MonNom") qui permet de récupérer la position d'une colonne dans un tableau structuré.

L'idée ici est donc de créer une fonction qui reçoit le nom du tableau, ainsi qu'un array (un tableau VBA) qui contient des paires de valeurs constituées du nom de la colonne et de la valeur à y ajouter.

Voici donc la fonction générique qui reçoit le nom du tableau, la liste des paires "Clé/Valeur", c'est-à-dire la les paires NomDeColonne/Valeur à pousser sur la nouvelle ligne, et optionnellement la position de la nouvelle ligne. Si la position n'est pas spécifiée, on ajoutera la ligne à la fin du tableau.

Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub AddRow(TableName, Values, Optional Position As Long)
  Dim r As ListRow
  Dim l As ListObject
  Dim i As Long
 
  Set l = Range(TableName).ListObject
  If Position = 0 Or Position > l.ListRows.Count Then Position = l.ListRows.Count + 1
  Set r = l.ListRows.Add(Position)
  For i = LBound(Values) To UBound(Values) Step 2
    r.Range(l.ListColumns(Values(i)).Index).Value = Values(i + 1)
  Next
 
  Set r = Nothing
  Set l = Nothing
End Sub


On remarque que dans ce code, il n'y a aucune notion métier: Le nom du tableau n'est pas spécifié, les noms des colonnes non plus. Toutes ces notions sont passées par paramètres.

Lorsque l'on veut ajouter une ligne, on peut dès lors utiliser le code d'appel suivant pour ajouter une ligne et y placer des valeurs:
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
AddRow "t_Personnel", VBA.Array("Prénom", "Jean", "Nom", "Aymar", "DN", DateSerial(1967, 12, 14), "Actif", True, "Salaire", 123.45)

Si l'on veut insérer la ligne au début du tableau, on précisera la valeur 1 pour l'argument Position.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
AddRow "t_Personnel", VBA.Array("Prénom", "Jean", "Nom", "Aymar", "DN", DateSerial(1967, 12, 14), "Actif", True, "Salaire", 123.45), 1

On remarque avec le code suivant que l'on n'a pas à se soucier de la position des colonnes, ce sont les noms passés dans l'argument Array qui déterminent oµ l'on place les valeur. On peut également ne pas préciser certaines colonnes, ce qui donne une grande souplesse d'utilisation de la fonction générique.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
AddRow "t_Personnel", VBA.Array("Actif", True, "DN", DateSerial(1967, 12, 14), "Salaire", 123.45, "Prénom", "Jean")


Cette fonction est à stocker dans un module spécifique de gestion des tableaux structurés, par exemple nommé TableManager

Que pensez-vous de cette façon de programmer avec des fonctions génériques?
Utilisez-vous des codes génériques dans vos applications ou codez-vous toujours du spécifique?
Trouvez-vous plus simple de travailler avec les tableaux structurés dans vos codes VBA?
Miniatures attachées Fichiers attachés

Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Viadeo Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Twitter Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Google Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Facebook Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Digg Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Delicious Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog MySpace Envoyer le billet « Tableaux structurés VBA: AJouter une ligne dans un tableau structuré et y placer des valeurs » dans le blog Yahoo

Mis à jour 13/11/2020 à 10h37 par Pierre Fauconnier

Catégories
VBA , Excel , MS Office

Commentaires

  1. Avatar de MarcelG
    • |
    • permalink
    Salut Pierre,

    Intéressant.
    J'en étais resté, certes avec un formulaire, à ta fonction Update avec Data(...

    Juste une précision, s'il te plaît,

    Par la passé, j'ai déjà rencontré cette nécessité mai je ne m'en rappelle plus l'occasion.
    Sans cette précaution, le résultat était altéré.

    Quelle est-ele quant à précéder la déclinaison d'un tableau Array par VBA

    au lieu de

    Merci à toi
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Marcel,

    VBA.Array force l'indice à 0 pour la première ligne du tableau, indépendamment de la ligne Option Base en début de module (voir cette réponse de ma part dans une discussion sur le sujet).

    Perso, à part éventuellement dans un module de classe, et encore, je déconseille de jouer avec Option Base puisqu'un même code, copié d'un module à un autre, pourrait ne pas produire le même résultat si les deux modules n'ont pas le même Option Base.