Discussion :

[Berquin]

Bonjour,
Suite à une discussion précédente, Pierre Fauconnier m'a fait découvrir les tableaux structurés et je l'en remercie encore.

Je ne connais pas le VBA et voici mon soucis pour faire une macro qui fonctionne:

• En gros, j'ai une feuille de listing ou des milliers de lignes doivent s'empiler les unes sous les autres.

• J'ai donc une feuille appelée "Listing" et une feuille appellée "Formulaire" (exemple fichier ci-joint).

• Ne connaissant pas VBA, à l'aide du formulaire d'entrée (feuille formulaire), j'encode les cellules nécessaires et je valide (j'aimerais valider) en cliquant sur le bouton jaune pour démarrer la macro.

• A ce moment, j'aimerais que la macro puisse:

1. Ajouter une ligne au bas du tableau de la feuille "Listing" (fichier ci-joint).
2. Incrémenter d'une unité la colonne A (ça c'est pas très compliqué).
3. Copier toutes les cellules du Formulaire vers la nouvelle ligne de la feuille "Listing".
4. Revenir sur la feuille "Formulaire" pour effacer le contenu des cellules remplies et être prêt pour le prochain encodage.

J'ai réussi à créer une macro qui fonctionne pour une seule ligne supplémentaire (un seul encodage) mais ensuite plus rien ne va.
J'ai aussi réussi à faire une macro en ajoutant à chaque fois une ligne dans le haut du tableau mais ce n'est pas pratique car ensuite je dois tout re-trier.

Pourriez-vous m'aider à créer cette macro svp?

[galopin01]

bonjour,
Une possibilité en pièce jointe.
EDIT : Autre possibilité (plus orthodoxe)
ici
A+

[Pierre Fauconnier]

Salut.

Avec un peu de mise en place, c'est l'affaire de quelques lignes. Voici comment je procéderais en pareil cas:

• Un tableau structuré nommé t_Contacts;
• Une plage de saisie nommée Saisie qui reprend toutes les cellules de saisie du formulaire;
• Chaque cellule de saisie du formulaire est nommée individuellement.

Ici, j'ai mis le tableau et le formulaire sur la même feuille, mais je pourrais réorganiser le tableau et le formulaire sans toucher au code.




Le code de transfert est le suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub SaveData()
  Dim Target As Range
  Dim t As ListObject
 
  Set t = Range("t_Contacts").ListObject
  Set Target = t.ListRows.Add().Range
  Target(t.ListColumns("ID").Index).Value = Application.Max(Range("t_Contacts[ID]")) + 1
  Target(t.ListColumns("Prénom").Index).Value = Range("Prénom").Value
  Target(t.ListColumns("Nom").Index).Value = Range("Prénom").Value
  Target(t.ListColumns("Date naissance").Index).Value = Range("DN").Value
  Range("saisie").ClearContents
End Sub

Cette solution ne fait appel à aucun moment à la place des données dans la feuille ni à la place des colonnes dans le tableau structuré. Je peux réorganiser les colonnes du tableau ou les cellules du formulaire, le code continue à fonctionner sans aucun besoin de changement. De plus, je risque moins de me tromper sur les lignes de transfert puisque le lien tableau/formulaire est évident grâce aux noms des colonnes et cellules.


Si je disposais de plusieurs paires tableau/formulaire dans le classeur, je généraliserais le code avec une table de mappage pour éviter de créer autant de procédures que de paires tableau/formulaire. Ce code pourrait alors être réutilisé de classeur en classeur. J'aurais dès lors un code générique "auquel je ne toucherais jamais" alors même que je pourrais modifier tableaux et formulaires à mon gré.

[Pierre Fauconnier]

Pour généraliser l'approche, j'utiliserais une table de mappage, par exemple comme celle-ci:




Je pourrais alors avoir le code générique suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Function SaveData(Tablename As String, Index As Long)
  Dim Target As Range
  Dim t As ListObject
  Dim m As ListRow
 
  Set t = Range(Tablename).ListObject
  Set Target = t.ListRows(Index).Range
  For Each m In Range("t_Mappage").ListObject.ListRows
    If m.Range(1).Value = Tablename Then Target(t.ListColumns(m.Range(2).Value).Index).Value = Range(m.Range(3).Value).Value
  Next
End Function

Je pourrais alors appeler ce code soit pour ajouter une nouvelle ligne, soit pour mettre à jour une ligne existante. Ici, je l'utilise pour ajouter un nouveau contact mais aussi une nouvelle facture.
Je pourrais peaufiner en triant le tableau de mappage pour permettre de ne boucler que sur les lignes du tableau concerné par le transfert, mais c'est du détail. L'important est ici de comprendre que l'on peut rendre le code générique, ce qui fait gagner du temps dans les développements, actuels ou futurs.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function AddNewInvoice()
  Dim Index As Long
 
  Index = Range("t_Factures").ListObject.ListRows.Add().Index
  SaveData "t_Factures", Index
  Range("facture").ClearContents
End Function
 
Function AddNewContact()
  Dim Index As Long
 
  Index = Range("t_Contacts").ListObject.ListRows.Add().Index
  Range("t_Contacts[ID]")(Index).Value = Application.Max(Range("t_Contacts[ID]")) + 1
  SaveData "t_Contacts", Index
  Range("saisie").ClearContents
End Function

Le tableau de mappage permettra également de restituer au sein d'un formulaire les données de n'importe quel ligne de "son" tableau":

Code :

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

1
2
3
4
5
6
7
8
9
Function ReadData(TableName As String, Index As Long)
  Dim t As ListObject
  Dim m As ListRow
 
  Set t = Range(TableName).ListObject
  For Each m In Range("t_Mappage").ListObject.ListRows
    If m.Range(1).Value = TableName Then Range(m.Range(3).Value).Value = t.ListColumns(m.Range(2).Value).DataBodyRange(Index).Value
  Next
End Function

Je remarque dès lors que la seule donnée "en dur" dans mon code est le nom de la table de mappage. TOUT LE RESTE fait partie du paramétrage est peut être modifié sans toucher au code. Et si vraiment on voulait, on pourrait détacher la table de mappage du code en passant son nom, voire sa structure, en paramètre, même si je ne vois pas grand intérêt à la chose

[Philippe Tulliez]

Bonjour Pierre,
Il y a moyen de faire plus court et je ne sais pas si dans ce cas précis, une table de correspondance soit bien nécessaire
Soit

• Un tableau structuré nommé t_Contact
• Une plage nommée DataForm (partie verte dans l'illustration)
• la première colonne de cette dernière plage faisant référence à l'étiquette idoine (Exemple pour la première ligne =t_Contact[[#En-têtes];[Nom]] ce qui permet de conserver l'orthographe exacte en cas de modification
• La première colonne de la table structuré avec la formule =LIGNE()-@LIGNE(t_Contact)+1

Le code de la procédure

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub PutData(FormRange As Range, oList As ListObject)
  Dim label As String
  Dim Cell As Range
  Dim Target As Range
  Set Target = oList.ListRows.Add().Range
  '
  For Each Cell In FormRange.Resize(columnSize:=1)
    With oList
     label = .ListColumns(Cell.Value)
     Target(.ListColumns(label).Index).Value = Cell.Offset(0, 1).Resize(1, 1).Value
    End With
  Next
End Sub

Exemple d'une procédure l'invoquant

Code :

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

1
2
3
Sub TestPutData()
  PutData Range("DataForm"), Range("t_contact").ListObject
End Sub

Illustration

[Pierre Fauconnier]

Salut Philippe,

je ne sais pas ce que tu appelles "plus court" entre 13 lignes et 10 lignes, et je n'ai jamais aimé jouer à qui a la plus courte

Cell.Offset(0, 1) impose un ordre de colonnes. Donc oui, dans ce cas-ci, on peut fonctionner avec ton code. Le code générique avec une table de mappage permet de s’affranchir de "dans ce cas-ci" (c'est le but d'un code générique, il me semble)...

Je ne vois pas comment on pourrait écrire du générique (donc hors "dans ce cas-ci") sans table de mappage, sauf à considérer que les colonnes du tableau sont:

• soit mises dans le même ordre que les cellules du formulaires... => tu rêves!;
• soit que les colonnes soient nommées comme les cellules du formulaire... => tu rêves, ne serait-ce qu'à cause des espaces, interdits dans le nom des plages.

[Philippe Tulliez]

Bonjour Pierre,
J'avais commencé la rédaction de ma réponse lors de ta première intervention où les étiquettes de colonnes étaient en dur

soit mises dans le même ordre que les cellules du formulaires...

On peut parfaitement placer les lignes dans un ordre différent, c'est une simple référence

[Berquin]

Bonjour,

Un grand merci pour toutes vos réponses qui sont peut-être un peu trop "pointues" pour ma petite tête.
Je vais essayer de comprendre vos suggestions pour les mettre en pratique et suis certain que cela va m'aider.
En tout cas je suis impressionné par vos réponses car en jetant un coup d'oeil sur ce que vous avez écrit cela parait simple mais pas facile à comprendre pour moi.

Un tout grand merci à vous tous.

[Pierre Fauconnier]

Bonsoir Philippe,

[...]
On peut parfaitement placer les lignes dans un ordre différent, c'est une simple référence

L'avantage d'une table de mappage, c'est que tu ne dois pas obligatoirement mettre les cellules de saisie à droite de l'étiquette, ni créer les étiquettes de ton formulaire à l'identique des noms des colonnes de la table. Ce n'est pas toujours possible ou souhaitable. Dans le cas suivant, je doute que l'on puisse faire correspondre les labels du formulaire et les colonnes du tableau





Avec la table de mappage, tu dois juste être attentif aux libellés que tu places dans ta table de mappage, mais tes intitulés peuvent être différents entre le tableau et le formulaire, et tes formulaires peuvent avoir des configurations Label-Cellule de saisie différentes. C'est l'intérêt du générique qui permet de se détacher au maximum de la conception du tableau et du formulaire.



Perso, j'aime bien la table de mappage, mais on pourrait passer un array clés/Valeurs, à charge de le remplir en amont... Je trouve cela moins pratique, cependant. Mais l'important est selon moi de découpler au maximum le code des positions des colonnes et cellules dans le classeur

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Function AddNewWorker()
  Dim index As Long
 
  index = Range("t_Personnel").ListObject.ListRows.Add().index
  SaveData1 "t_Personnel", index, _
    VBA.Array("Prénom", "per_Prénom", "Nom", "per_Nom", "Service", _
    "per_Service", "Entré le", "per_DateEntrée", "Salaire", _
    "per_Salaire", "gsm", "per_Gsm")
  Range("per_Saisie").ClearContents
End Function
 
Function SaveData1(TableName As String, index As Long, Mapping)
  Dim Target As Range
  Dim t As ListObject
  Dim i As Long
 
  Set t = Range(TableName).ListObject
  Set Target = t.ListRows(index).Range
  For i = LBound(Mapping) To UBound(Mapping) Step 2
    Target(t.ListColumns(Mapping(i)).index).Value = Range(Mapping(i + 1)).Value
  Next
End Function

Le problème avec l'array, c'est qu'on le construira souvent sur base d'une table pour éviter la saisie en dur dans le code, et on revient donc à la table de mappage