Pierre Fauconnier

Salut

Sur le forum, une question revient régulièrement: Comment transférer des données d'une feuille (formulaire) vers un tableau de données? Reviennent alors les solutions obsolètes à coup de End(xlUp) et litanie de lignes de code pour transférer chaque cellule du formulaire vers la cellule correspondante du tableau, en faisant fi de l'utilisation des tableaux structurés et en codant comme nos aïeux, c'est-à-dire à la bourrin.

Heureusement, ce temps est révolu et Excel propose depuis la version 2007 (14 ans donc à l'heure où j'écris ces lignes) des solutions qui permettent une approche à la fois plus simple et plus rigoureuse.

Je note en préambule que l'utilisation d'une feuille de calcul comme formulaire est parfois plus intéressante que la programmation d'un userform et présente, parmi d'autres, les avantages suivants:

• Moins de code;
• Transfert des types de données sans altérations (dates, valeurs numériques);
• Validation de la saisie dans les cellules grâce aux outils Excel;
• ...

Dans ce billet, nous allons voir que:

• l'utilisation du tableau structuré et des cellules nommées permet de découpler totalement notre code de l'emplacement des données dans le classeur;
• l'ajout de paires cellule/Colonne impacte très peu notre code*;
• quelques lignes de code suffisent pour effectuer le transfert, quel que soit le nombre de données à transférer

Dans cet autre billet, nous verrons le transfert dans l'autre sens, c'est-à-dire de la table vers la feuille formulaire.


Au départ, on dispose d'un formulaire tout simple de 4 cellules permettant de saisir les données d'un contact: prénom, nom, date de naissance et statut (actif ou non). Ces données, une fois saisies, seront ajoutées à un tableau structuré disposant des colonnes correspondantes. Dans un premier temps, je conçois le formulaire et le tableau sur la même feuille de saisie, par souci de clarté d'illustration.

L'idée est ici de nommer les cellules en les préfixant par exemple de fc_ (formulaire de contact) et de les transférer dans le tableau structuré nommé t_Contacts.




Pour réaliser cela, on va s'appuyer sur une fonction générique qui recevra les infos de transfert en arguments sous forme d'un array (tableau en VBA), en plus du nom du tableau cible.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Function AddData(TableName As String, Map)
  Dim r As Long
  Dim i As Long
  Dim t As ListObject
 
  Set t = Range(TableName).ListObject
  r = t.ListRows.Add.Index
  For i = LBound(Map) To UBound(Map) Step 2
    t.ListColumns(Map(i + 1)).DataBodyRange(r).Value = Range(Map(i)).Value
  Next i
  Set t = Nothing
End Function

Si le premier argument se passe de commentaires, le second, lui, demande une petite explication. Nous avons nommé les cellules du formulaire, et nous avons donc besoin de transférer les valeurs de fc_Nom dans la colonne Nom, fc_prénom dans la colonne prénom, et pareillement pour les deux autres données. Ce sont ces notions que nous allons passer à la fonction grâce à l'array. La boucle For i = LBound(Map) To UBound(Map) Step 2 va parcourir les valeurs par paire (step 2). Bien entendu, l'utilisateur de la fonction doit savoir dans quel ordre passer les valeurs de chaque paire. Ici, j'ai choisi la paire plage nommée / Colonne du tableau pour correspondre à la logique Source / Cible.

La fonction ajoute une ligne au tableau et en mémorise l'index. Après, en adressant les DataBodyRange des colonnes nommées du tableau, on peut transférer facilement les données en se servant de l'index. On pointe vers le listobject en utilisant sa plage éponyme. On remarque que, mis à part les noms des cellules et des colonnes du tableau, ce code ne reprend aucune notion de "localisation" du formulaire ou du tableau. Cela permet de déplacer ou de réorganiser les colonnes du tableau et les cellules nommées sans devoir toucher au code qui continue à fonctionner.

Dès lors, transférer les données du formulaire vers le tableau revient à appeler la fonction de la manière suivante:

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

AddData "t_Contacts", VBA.Array("fc_Prénom", "Prénom", "fc_Nom", "Nom", "fc_DN", "Date naissance", "fc_Actif", "Actif")

.

Si l'on ajoute dans le fichier une paire formulaire / Tableau, par exemple pour saisir des données de facturation, on réutilise la fonction générique avec une ligne d'appel qui reprend les paires plage nommée / Colonne en arguments.



La ligne de commande pour transférer les données du formulaire vers le tableau devient la suivante, et vous pouvez déduire les noms des cellules du formulaire et du tableau cible par cette simple ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AddData "t_FacturesVente", VBA.Array("fv_Date", "Date", "fv_Numéro", "Numéro", "fv_Client", "Client", "fv_htva", "HTVA", "fv_Tva", "TVA", "fv_Tvac", "TVAC")

Si l'on doit modifier le formulaire et le tableau, par exemple pour ajouter le service dans lequel travaille le contact, nous devrons bien sûr modifier le "formulaire" en ajoutant la cellule nommée fc_Service et le tableau t_Contacts sera modifié par l'ajout de la colonne Service. La seule ligne de code à modifier sera celle qui appelle AddData avec l'array modifié. La fonction AddData elle-même n'est pas modifiée, puisque générique et donc indépendante du formulaire et du tableau. On constatera aussi que l'ordre des paires dans l'array n'a pas d'importance.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Function AddContact()
  AddData "t_Contacts", VBA.Array("fc_Prénom", "Prénom", "fc_Nom", "Nom", "fc_DN", "Date naissance", "fc_Actif", "Actif", "fc_Service", "Service")
End Function

Comme on le voit, la systématisation de votre code et l'approche par fonctions génériques va vous faire gagner énormément de temps dans la création de vos applications Excel.

Pour transférer les données de la table vers le formulaire, c'est presque pareil et c'est dans ce billet que j'explique comment il faut s'y prendre.