Salut.
Pour répondre à la question fréquente sur nos forums "Comment lier un userform à un tableau structuré?" et pour faire écho à la contribution de Qwazerty, je propose ici une modélisation des interactions entre un userform et une table de données (appelée aussi Tableau structuré Excel).
Pour celles et ceux qui ne voient pas bien ce qu'est un tableau structuré, je propose la lecture de mon tuto sur le sujet. Je précise qu'en 2019, il me semble aberrant de continuer à travailler avec Excel sans tableaux structurés. Je vois encore passer trop souvent des codes qui utilisent des lignes telles que TblBD = f.Range("D8:H" & f.[D65000].End(xlUp).Row) alors que cela fait 12 ans que les tableaux structurés existent et permettent de s'affranchir d'un code lourd, d'une liaison des données à une feuille et d'un bornage des données sur une colonne bien précise. C'est du passé, tout cela, et il est temps d'utiliser les outils actuels d'Excel qui allègent l'écriture du code et facilitent la maintenance et l'évolution de l'application.
Cette contribution découle du fait que l'on réinvente souvent la roue lorsqu'il s'agit de lier userform et tableau structuré, alors qu'en fait, le processus est toujours le même:
- Dans le sens Table vers Userform, il faut alimenter les contrôles du userform avec les données d'une ligne précise de la table;
- Dans le sens Userform vers Table, il faut alimenter les cellules d'une ligne précise de la table avec les données du userform.
Vous trouverez le fichier joint qui contient la classe personnalisée DataUsfManager ainsi que le module de classe. C'est elle qui va piloter les actions entre userform et table. Il vous suffira d'ajouter ce module de classe à votre projet Excel pour pouvoir lier facilement et de manière générique vos userforms et vos tables de données, en vous appuyant sur les explications ci-dessous pour "faire prendre la mayonnaise".
Le principe est de lier le userform à la table de données grâce à un array qui mappe les contrôles du userform et les colonnes de la table. Lors des transferts de données Usf=>table ou table=>Usf, cet array est parcouru pour transvaser les valeurs. Il faudra donc, pour chaque paire Usf/Table, créer l'array de mappage. Dans un premier temps, je créerai l'array "en dur", mais je développerai plus loin une technique pour créer l'array sur base d'un tableau structuré Excel.
Pour expliquer le fonctionnement de cette classe, je vais m'appuyer dans un premier temps sur le tableau suivant, qui reprend des données de contact. il est illustré ici avec le userform qui lui est lié et qui permettra d'ajouter, de modifier ou de supprimer des données de contact dans la table de données. Nous verrons par la suite comment utiliser la classe avec un autre tableau.
Dans ce userform, nous voyons une liste déroulante qui s'appuie sur une table de données monocolonne contenant les fonctions qu'il est possible d'attribuer à un contact.
Nous devons pouvoir, à l'aide du userform:
- créer un nouvel enregistrement;
- supprimer un enregistrement;
- aller à l'enregistrement suivant;
- aller à l'enregistrement précédent;
- aller à l'enregistrement qui se trouve à la position x;
- enregistrer les modifications du contact ou les données du nouveau contact;
- quitter le userform.
Ces 7 actions correspondent aux 7 contrôles du bas du userform, et, sauf l'action de quitter le userform, nous allons les retrouver au niveau de notre classe perso, dont je donne le code ci-dessous. Cette classe contiendra également la fonction qui transfère les données entre la table et le userform.
- GotoNew;
- Delete;
- GotoNext;
- GotoPrevious;
- GotoRecord;
- UpdateTable;
- UpdateUsf.
Dans le cadre d'une contribution, je n'explique pas en détails chaque procédure. Normalement, la lecture du code suffit à le comprendre. On remarquera que les actions sont réalisées par des fonctions, car le userform pourra se servir des valeurs renvoyées par les fonctions du DataUsfManager pour informer l'utilisateur, notamment. Je précise toutefois que l'array de mappage est créé "transposé". cela veut dire qu'il contient deux lignes (une pour le nom du contrôle du usf, une pour le nom de la colonne de la table) et autant de colonnes qu'il y a de paires contrôle/colonne. Je vous expliquerai plus loin pourquoi j'ai procédé ainsi. Vous pouvez vous rendre compte de cela dans les deux procédures UpdateTable et UpdateUsf. Vous remarquez aussi qu'il n'y a qu'une fois la procédure de transfert usf=>table.
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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88 Option Explicit Public Map Private mIndex As Long Private mTable As ListObject Private mUsf As UserForm Property Get Index() As Long Index = mIndex End Property Property Let Table(Value As ListObject) Set mTable = Value End Property Property Let Usf(Value As UserForm) Set mUsf = Value End Property Function GoToPrevious() As Long If mIndex > 1 Then mIndex = mIndex - 1 updateUserform Else GoToPrevious = 1 End If End Function Function GoToNext() As Long If mIndex < mTable.ListRows.Count Then mIndex = mIndex + 1 updateUserform Else GoToNext = 1 End If End Function Function GotoRecord(Index As Long) As Long If mTable.ListRows.Count >= Index And mTable.ListRows.Count > 0 And Index >= 1 Then mIndex = Index updateUserform Else GotoRecord = 1 End If End Function Sub GotoNew() mIndex = 0 End Sub Function Delete() As Long mTable.ListRows(mIndex).Delete If mTable.ListRows.Count > 0 Then If mIndex > mTable.ListRows.Count Then mIndex = mTable.ListRows.Count updateUserform Else Delete = 1 End If End Function Sub updateUserform() Dim r As ListRow Dim Counter As Long Dim ControlName As String Dim ColumnName As String Set r = mTable.ListRows(mIndex) For Counter = 1 To UBound(Map, 2) mUsf.Controls(Map(1, Counter)).Value = r.Range(mTable.ListColumns(Map(2, Counter)).Index).Value Next End Sub Sub UpdateTable() Dim r As ListRow Dim Counter As Long Dim ControlName As String Dim ColumnName As String If mIndex = 0 Then mTable.ListRows.Add mIndex = mTable.ListRows.Count End If Set r = mTable.ListRows(mIndex) For Counter = 1 To UBound(Map, 2) r.Range(mTable.ListColumns(Map(2, Counter)).Index).Value = mUsf.Controls(Map(1, Counter)).Value Next End Sub
Le userform va embarquer un objet de type DataUsfManager et ce dernier sera appelé et manipulé lors des clics sur les différents boutons ou lors de la modification du numéro de l'enregistrement que l'on souhaite atteindre. Ici aussi, le code est à mon avis assez parlant. J'utilise ici l'évènement Initialize ud userform pour créer l'objet de type DataUsfManager. On remarque que les codes évènementiels des boutons pour les actions liées à la table se servent des valeurs renvoyées par le DataUsfManager pour afficher des messages à l'utilisateur.
Je pense que, d'une manière générale, le code se passe de commentaires. Il faut toutefois bien observer ce qui suit: Le DataUsfManager n'utilise pas en hard coding la structure du formulaire. Il ne fait qu'utiliser les contrôles qui lui ont été passés dans l'array de mappage. Cela signifie, et c'est important de le signaler, que mis à part le fait d'embarquer un objet DataUsfManager, le userform ne doit respecter aucune contrainte de structure. Par exemple, ce n'est pas le DataUsfManager qui doit mettre à jour le textbox tboIndex, car il "ne sait pas" que ce contrôle existe dans le userform et ce contrôle pourrait très bien ne pas être présent sans que cela nuise au fonctionnement du DataUsfManager. Ce n'est pas le DataUsfManager qui affiche les messages à l'utilisateur, car ce n'est pas son rôle. Par contre, il envoie des infos au userform sous forme de résultat de fonctions pour que le userform puisse éventuellement afficher un message.
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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79 Option Explicit Private mDataManager As DataUSFManager Property Get DataManager() As DataUSFManager Set DataManager = mDataManager End Property Private Sub btnDelete_Click() If mDataManager.Delete() = 1 Then ClearForm MsgBox "La table est vide" End If End Sub Private Sub btnNew_Click() ClearForm mDataManager.GotoNew End Sub Private Sub btnNext_Click() Dim Result As Long With mDataManager Result = .GoToNext() tboIndex = .Index End With If Result = 1 Then MsgBox "Vous êtes au dernier enregistrement" End Sub Private Sub btnPrevious_Click() Dim Result As Long With mDataManager Result = .GoToPrevious() tboIndex = .Index End With If Result = 1 Then MsgBox "Vous êtes au premier enregistrement" End Sub Private Sub btnQuit_Click() Me.Hide End Sub Private Sub btnSave_Click() With mDataManager .UpdateTable tboIndex = .Index End With End Sub Private Sub tboIndex_AfterUpdate() Dim Result As Long With mDataManager Result = .GotoRecord(tboIndex.Value) If Result = 1 Then MsgBox "Vous êtes hors limites de la table" If .Index <> 0 Then tboIndex = .Index Else tboIndex = "" End If End If End With End Sub Private Sub UserForm_Initialize() Set mDataManager = New DataUSFManager mDataManager.Usf = Me End Sub Sub ClearForm() tboFirstName.Value = "" tboLastName.Value = "" tboIndex = "" cboFunction.ListIndex = -1 End Sub
On remarque également le code minimaliste des procédures évènementielles des boutons. Un code évènementiel ne peut normalement rien faire d'autre que d'appeler du code applicatif. C'est une règle à respecter sans aucune exception pour créer du code maintenable, évolutif, non redondant et facilement maintenable. Les codes évènementiels des boutons passent simplement la main au DataUsfManager, affichent des éventuellement des messages selon les réponses du DataUsfManager et ajustent le contenu de certains contrôles. C'est tout. Ce n'est pas le userform qui gère les interactions avec la table, mais bien le DataUsfManager.
Le userform est piloté par une procédure d'un module standard, qui utilise une fonction pour créer le mappage. Nous verrons plus loin pourquoi.
A nouveau, le code se passe de commentaires et est normalement compréhensible à la seule lecture. On charge le userform, on passe au DataUsfManager intégré au userform l'array de mappage et le tableau structuré (listobject) qu'il devra piloter; on décide sur quel enregistrement on se positionne au départ; on met à jour les données dans le userform sur base de l'enregistrement choisi et on affiche le userform. Le reste est piloté par les boutons du userform qui vont appeler les fonctions correspondante du DataUsfManager.
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
23
24
25
26 Sub ShowUserform() With UserForm1 .DataManager.Map = getMap() .cboFunction.List = Range("t_Fonctions").Value .DataManager.Table = Range("t_Contacts").ListObject If .DataManager.GotoRecord(1) = 0 Then .tboIndex = 1 .DataManager.updateUserform End If .Show End With Unload UserForm1 End Sub Function getMap() Dim Map(1 To 2, 1 To 3) Map(1, 1) = "tboFirstname" Map(2, 1) = "Prénom" Map(1, 2) = "tboLastName" Map(2, 2) = "Nom" Map(1, 3) = "cboFunction" Map(2, 3) = "Fonction" getMap = Map End Function
Dans le cadre d'une application qui utiliserait plusieurs userforms liés à des données, on pourrait vouloir créer la table de mappage en Excel, qui contiendrait alors les paires contrôle/colonne pour chaque paire Usf/Tableau structuré.
On pourra alors modifier la fonction GetMap pour charger les paires contrôle/Colonne en fonction du nom du userform passé en paramètres. On comprend ici que le ReDim ne pouvant s'effectuer que sur la dernière dimension de l'array, il était nécessaire de le prévoir sur base de la structure 2 lignes et x colonnes (x étant le nombre de paires contrôle/colonne pour le userform considéré).
Si vous souhaitez piloter un userform "Localités", il vous suffit de:
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 Function getMap1(UsfName As String) Dim t ReDim Map(1 To 2, 1 To 1) Dim Counter As Long Dim Index As Long t = Range("t_MapUsf").Value For Counter = 1 To UBound(t) If t(Counter, 1) = UsfName Then If IsEmpty(Map(1, 1)) Then Index = 1 Else Index = UBound(Map, 2) + 1 ReDim Preserve Map(1 To 2, 1 To Index) End If Map(1, Index) = t(Counter, 2) Map(2, Index) = t(Counter, 3) End If Next getMap1 = Map End Function
- Créer le tableau structuré pour les localités;
- Créer le userform correspondant;
- Ajouter les paires dans la table de mappage;
- Créer la fonction d'appel de ce userform.
Voici le code du module standard qui appelle le userform de gestion d'une localité. A part les noms du mappage et du tableau qui ont changé et la ligne d'alimentation de la combo qui a disparu, c'est le même code que celui qui prépare et affiche usfContact. Le code du userform est quasi identique à celui de usfContact.
Comme on le voit, l'écriture d'un code générique permet un gain de temps considérable dans la gestion des échanges Usf-Table, puisque l'on ne réinvente pas la roue à chaque fois. Une approche systématique permet également des tests plus simples, une maintenance aisée et une évolution de l'application en s'appuyant sur du code solide, pérenne et unique. Si vous aviez 30 userforms liés à 30 tables de données, vous auriez évidemment les 30 fonctions de préparation et d'appel et les 30 codes dans les userforms (que l'on pourrait également rendre +/- génériques, bien que le gain soit minime), mais tous utiliseraient le même code, contenu dans la classe DataUsfManager. Bien entendu, vous pouvez enrichir cette classe et tous les userforms profiteraient alors de ces ajouts.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 Sub ShowCityForm() With usfCity .DataManager.Map = getMap1("usfCity") .DataManager.Table = Range("t_Localités").ListObject If .DataManager.GotoRecord(1) = 0 Then .tboIndex = 1 .DataManager.updateUserform End If .Show End With Unload usfCity End Sub
Vos remarques sont bienvenues, dans l'esprit DVP
Bon travail avec Excel et VBA...
Attention: Il faut supprimer l'extension .txt du fichier DataUsfManager...
Partager