Excel VBA: Modélisation des échanges entre table de données et userform

Pierre Fauconnier

Ce tutoriel propose de développer une solution technique générique pour l’échange de données entre un userform et une table de données Excel, aussi appelée tableau structuré.

- Titre : Excel VBA: Modélisation des échanges entre table de données et userform

- Auteur : Pierre Fauconnier

- Parution : 2 janvier 2020

- Mise à jour : 2 janvier 2020

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2020 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Introduction

- I-A. Pourquoi ce tutoriel ?

- I-B. Objectifs

- I-C. Prérequis

- II. Notions utilisées dans ce tutoriel

- II-A. Rappel sur les tableaux structurés

- II-A-1. Tableau structuré en Excel

- II-A-2. Tableau structuré en VBA (ListObject)

- II-A-2-a. ListObject

- II-A-2-b. ListColumn

- II-A-2-c. ListRow

- II-B. Classe personnalisée

- II-B-1. Création du module de classe

- II-B-2. Encapsulation

- II-C. Formulaire (Userform)

- III. Architecture

- III-A. Tables de données utilisées dans ce tutoriel

- III-A-1. Contacts

- III-A-2. Fonctions

- III-A-3. Localités

- III-B. Formulaire de gestion d’un enregistrement

- IV. DataUsfManager, la classe d’échange des données

- IV-A. Préambule

- IV-A-1. Rôles du DataUsfManager

- IV-B. Données manipulées par le DataUsfManager

- IV-B-1. Variables privées

- IV-B-2. Initialisation

- IV-B-2-a. Table de données

- IV-B-2-b. Userform

- IV-B-2-c. Array de mappage

- IV-C. Fonctionnalités du DataUsfManager

- IV-C-1. Mise à jour de la table avec les données du formulaire

- IV-C-2. Mise à jour du formulaire avec les données de la table

- IV-C-3. Déplacement de l’index dans les lignes du tableau structuré

- IV-C-3-a. Récupération de la ligne courante du DataUsfManager

- IV-C-3-b. Avancer d’un enregistrement

- IV-C-3-c. Enregistrement précédent

- IV-C-3-d. Aller à l’enregistrement x

- IV-C-3-e. Supprimer une ligne de la table

- IV-C-3-f. Préparer un nouvel enregistrement

- IV-D. Code complet

- V. Utilisation du formulaire pour gérer les enregistrements

- V-A. Initialisation du DataUsfManager

- V-B. Déplacements dans la table et récupération des valeurs

- V-B-1. Aller à l’enregistrement suivant

- V-B-2. Aller à l’enregistrement précédent

- V-B-3. Aller au énième enregistrement

- V-B-4. Supprimer un enregistrement

- V-B-5. Se positionner sur un nouvel enregistrement

- V-C. Code complet du userform à ce stade

- V-D. Appel du formulaire, initialisation des composants

- V-E. Constatations

- V-F. Deux améliorations, parmi d’autres

- V-F-1. GotoFirst

- V-F-2. GotoLast

- V-F-3. Modifications du DataUsfManager

- V-F-4. Modifications du userform

- V-F-5. Constatations

- VI. Utilisation d’une table de mappage

- VI-A. Table de mappage

- VI-B. Fonction de création de l’array de mappage

- VII. Modifications structurelles de la table et du userform

- VII-A. Modification du nom d’une colonne

- VII-A-1. Utilisation d’un array de mappage sans table liée

- VII-A-2. Utilisation d’un array de mappage sur table liée

- VII-A-3. Et le DataUsfManager ?

- VII-B. Ajout d’une colonne supplémentaire

- VII-B-1. Modification de la table

- VII-B-2. Modification du formulaire

- VII-B-3. Ajout de la paire Colonne/Contrôle dans la table de mappage

- VII-B-4. Modification de la fonction qui renvoie l’array de mappage

- VII-B-5. Et le DataUsfManager ?

- VII-B-6. Utilisation du userform

- VIII. Utilisation du DataUsfManager connecté à une autre paire Table/Formulaire

- VIII-A. Table des localités

- VIII-B. Formulaire de gestion d’une localité

- VIII-C. Table de mappage

- VIII-D. Procédure d’appel du formulaire

- VIII-E. Et le DataUsfManager ?

- IX. Utilisation du DataUsfManager connecté à un formulaire proposant une autre ergonomie

- IX-A. Table de données

- IX-B. Userform

- IX-C. Table de mappage

- IX-D. Procédure d’appel et de gestion du userform

- IX-D-1. Visualisation d’une entreprise existante

- IX-D-2. Création d’une société

- IX-E. Autre possibilité d’architecture

- IX-E-1. Modification du DataUsfManager

- IX-E-2. Modification au niveau de la feuille de calcul

- IX-E-3. Modification de la fonction d’appel

- IX-E-4. Conclusions

- X. Fichiers

- XI. Conclusions

- XII. Remerciements

I - Introduction

I-A - Pourquoi ce tutoriel ?

En parcourant les questions sur les forums Excel de www.developpez.com, j’ai constaté que les problèmes de liaisons entre une feuille de données et un formulaire de saisie revenaient régulièrement. J’ai donc décidé de proposer une solution générique de liaison entre une table de données et un userform, de manière à systématiser la gestion de données.

I-B - Objectifs

Ce tutoriel poursuit plusieurs objectifs :

- Offrir une solution générique de liaison Tableau structuré/Formulaire ;

- Proposer une réflexion sur la manière de manipuler les lignes et colonnes d’une table de données en VBA pour alléger son code et le systématiser ;

- Exposer une architecture et une manière de coder qui permettent une maintenance aisée et une évolution rapide et économique du code ;

- Illustrer comment il faut séparer le code des événements d’un userform, uniquement ergonomique, du code applicatif qui ne doit jamais se trouver dans le userform ;

- Insister sur une architecture basée sur des procédures/fonctions courtes qui ont une seule responsabilité, ce qui facilite les tests, la maintenance, l’évolution et la réutilisation du code.

I-C - Prérequis

Il me semble nécessaire, pour tirer le meilleur profit de ce tutoriel, de posséder des connaissances, au moins de base, des tables de données (tableaux structurés), des formulaires, mais également de ce qu’est une classe personnalisée, comment la mettre en place et manipuler des objets s’appuyant sur cette classe.

Mon tuto sur les tableaux structurés en Excel vous permettra d’appréhender les notions liées à cet outil. Celui sur les classes personnalisées en VB(A) vous permettra d’acquérir ou de rafraîchir les notions de base nécessaire à la construction et la manipulation d’objets personnalisés.

II - Notions utilisées dans ce tutoriel

II-A - Rappel sur les tableaux structurés

II-A-1 - Tableau structuré en Excel

Celles et ceux qui ne maîtrisent pas bien les tables de données ou tableaux structurés liront avec intérêt mon tuto sur le sujet.

Dans le cadre du présent tutoriel, la notion fondamentale à maîtriser est celle des références structurées, qui permettent d’utiliser les noms du tableau et de ses colonnes « comme des plages nommées ».

Je me limiterai ici à rappeler qu’il faut simplement savoir que l’on peut utiliser le nom du tableau structuré comme si c’était une « plage nommée ». Le tutoriel étant uniquement axé sur le VBA, c’est la seule notion que nous devons connaître.

Je rappelle au passage que "plage nommée" est un abus de langage. On devrait plutôt parler de formule nommée pointant vers une plage.

II-A-2 - Tableau structuré en VBA (ListObject)

En VBA, il est possible d’utiliser les références structurées « comme des plages nommées », à l’instar de ce qui se passe en Excel. Mais le ListObject (l’objet « Tableau structuré » en VBA) permet de manipuler d’autres notions, telles que la ListColumn (colonne d’une table de données) et ListRow (Ligne d’une table de données).

Sans entrer dans les détails de la manipulation de ces objets en VBA, je détaille ci-dessous comment le DataUsfManager va les utiliser.

II-A-2-a - ListObject

Le tableau structuré est en fait un ListObject, à bien différencier de la plage éponyme. Dans ce tutoriel, j’utilise le tableau structuré t_Contacts. Il y a donc la "plage nommée" t_Contacts appelée en VBA par range("t_Contacts") et le listobject t_Contacts appelé en VBA par Listobjects("t_Contacts").

Le listobject est associé à une feuille, au travers de la collection ListObjects de cette feuille, ce qui nécessiterait de connaître la feuille pour atteindre le ListObject.

Mais on peut atteindre le ListObject par sa "plage nommée", ce qui ne nécessite pas de savoir où il se trouve.

Range("t_Contacts").ListObject

On peut bien sûr utiliser une variable ListObject pour en manipuler les propriétés, notamment les collections ListColumns et ListRows.

Sub Test()
Dim t As ListObject

Set t = Range("t_contacts").ListObject
Debug.Print "Le tableau contient " & t.ListColumns.Count & " colonnes."
Debug.Print "Le tableau contient " & t.ListRows.Count & " lignes."
End Sub

II-A-2-b - ListColumn

Une ListColumn matérialise une colonne du ListObject et fait partie de la collection des ListColums du ListObject. Un objet ListColumn expose bien sûr plusieurs méthodes et propriétés, mais les deux qui nous intéressent ici sont Index et Name, qui permettent d’atteindre une colonne particulière de la collection par sa position (Index) ou son nom (Name). On peut donc connaître le nom d’une colonne dont on connaît la position, ainsi que la position d’une colonne dont on connaît le nom.

Debug.Print Range("t_Contacts").ListObject.ListColumns(2).Name
Debug.Print Range("t_Contacts").ListObject.ListColumns("Nom").Index

Pour les échanges entre Table et userform, nous nous limiterons à ces notions, puisque nous allons avoir besoin de l’index d’une colonne dont on connaît le nom.

On remarquera que cette possibilité d’obtenir l’index d’une colonne de table par son nom permet à notre code de fonctionner sans se soucier de l’organisation des colonnes dans le tableau structuré, du moment que les noms des colonnes ne soient pas modifiés.

Il est plus courant de modifier l’ordre des colonnes (permutation, insertion) que de modifier les intitulés de colonne, ce qui permet de dire que notre code est pérenne quelle que soit l’organisation des colonnes au sein de la table.

II-A-2-c - ListRow

Une ListRow matérialise une ligne du ListObject. On peut atteindre une ListRow grâce à son index, c’est-à-dire sa position dans la table. Elle expose essentiellement la plage de données constituée des cellules de la ligne.

Dans le tableau ci-dessus, la deuxième ListRow pointe vers la plage A3:C3. On peut atteindre la plage de cette ListRow avec le code suivant si l’on ne dispose pas directement du ListObject :

range("t_contacts").ListObject.ListRows(2).Range

On peut bien entendu, pour faciliter le code, utiliser une variable de type ListObject et manipuler alors directement les éléments de sa collection ListRows :

Sub Test()
Dim t As ListObject

Set t = Range("t_contacts").ListObject
Debug.Print t.ListRows(2).Range.Address
End Sub

On peut itérer sur les cellules de la plage récupérée et, pour autant que l’on ne sorte pas des limites de la plage, il suffit de boucler sur les cellules de ladite plage :

Sub Test()
Dim Counter As Long
Dim r As Range
Dim t As ListObject

Set t = Range("t_contacts").ListObject
Set r = t.ListRows(2).Range
For Counter = 1 To r.Count
Debug.Print r(Counter).Value
Next
End Sub

Le résultat est le suivant dans la fenêtre d’exécution :

Au niveau de la plage de données, il n’y a pas de notions de « nom de colonne » ou de « nom de champ ». On atteint une cellule de la plage par son index. Mais nous avons vu, lors du rappel sur la manipulation des ListColumns, que l’on pouvait récupérer l’index d’une colonne par son nom. Dès lors, si je souhaite récupérer le nom du contact (2ième cellule de la ligne), je peux récupérer l’index correspondant en récupérant celui de la ListColumn nommée « Nom ».

t.ListColumns("Nom").Index

Dans l’exemple suivant, je vais pouvoir afficher le nom du 2ième contact de ma table en ne me préoccupant pas du tout de l’ordre des colonnes dans mon tableau structuré.

Sub Test1()
Dim Counter As Long
Dim r As Range
Dim t As ListObject

Set t = Range("t_contacts").ListObject
Set r = t.ListRows(2).Range
Debug.Print r(t.ListColumns("Nom").Index)
End Sub

C’est cette technique qui sera utilisée lors des échanges Table/Userform.

Il est primordial de bien observer que, dans ce code, je ne me tracasse à aucun moment de la feuille sur laquelle se trouve ma table de données, de l’emplacement de la table au sein de la feuille ou de la position de la colonne dans le tableau structuré.

Cela signifie que mon code continue à fonctionner sans aucune modification, même si je déplace la table de données, sur la même feuille ou sur une autre, ou que je modifie l’organisation des colonnes au sein du tableau structuré.

II-B - Classe personnalisée

Une classe personnalisée permet de créer des objets qui n’existent pas nativement dans le langage, ici le VBA. Un objet informatique expose des méthodes et des propriétés qui permettent d’agir sur lui ou avec lui. Après avoir écrit le code de sa classe , il est possible de créer plusieurs objets issus de cette classe, de sorte qu’il est aisé de réutiliser le code créé à chaque fois que c’est nécessaire.

Le lecteur peu familiarisé avec ces notions pourra lire mon tutoriel sur la création de classes personnalisées en VB(A).

II-B-1 - Création du module de classe

Pour créer une classe personnalisée, il faut ajouter un module de classe au projet et lui donner un nom. Pour notre projet, j’ai appelé cette classe DataUsfManager.

II-B-2 - Encapsulation

Une notion très importante de la POO (Programmation Orientée Objet) est l’encapsulation. Cette notion signifie qu’un objet ne peut pas manipuler ou interférer avec des objets ou variables qui ne sont pas ses propriétés. Cette obligation de ne manipuler que des objets ou variables qu’on a passés à un objet permet de le rendre indépendant du reste de l’application. Il reçoit des données, les traite et restitue éventuellement ces données ou d’autres. Cette indépendance par rapport au reste du code permet une réutilisation de la classe dans un autre contexte du même projet, voire bien entendu d’un autre projet informatique.

Nous veillerons systématiquement à respecter scrupuleusement cette notion d’encapsulation dans la création de nos classes personnalisées.

Cette notion, liée à celle de couplage faible (une procédure/fonction ne doit pas utiliser des notions qui ne lui ont pas été passées en arguments) garantit un code stable, et surtout réutilisable, ce qui amène gain de temps et d’argent. On comprend dès lors la raison qui nous pousse à ne jamais utiliser de variables publiques dans nos développements VBA (voir mon billet de blog à ce sujet).

II-C - Formulaire (Userform)

Ce tutoriel n’a pas pour vocation d’apprendre à manipuler un userform. Les notions fondamentales de manipulation d’un formulaire VBA font partie des prérequis pour aborder ce cours. Il n’y a d’ailleurs au niveau de la manipulation d’un userform rien de très évolué dans la suite du tutoriel.

Il faut bien noter que je n’aborde pas ici les notions de vérification technique ou "métier" des données. Mon billet de blog à ce sujet vous renseignera sur l’architecture à mettre en place, qui peut venir se greffer sur ce qui est développé ici.

III - Architecture

III-A - Tables de données utilisées dans ce tutoriel

III-A-1 - Contacts

Dans la première partie du tutoriel, nous utiliserons la table de données suivante pour la gestion des contacts.

Par la suite, nous y ajouterons une colonne, afin d’observer ce qui doit être modifié dans notre application pour prendre en compte les nouvelles informations de la table.

III-A-2 - Fonctions

Afin d’alimenter la combobox des fonctions, nous utiliserons une table monocolonne reprenant les fonctions disponibles.

III-A-3 - Localités

Afin d’observer ce qui doit être mis en place lorsque l’on veut utiliser le DataUsfManager avec une nouvelle paire Table/Userform, nous utiliserons la table suivante pour les localités. Afin de bien illustrer que la table peut se trouver n’importe où, celle-ci n’est pas calée sur la cellule A1.

III-B - Formulaire de gestion d’un enregistrement

Voici le formulaire de gestion d’un contact. Nous y voyons les zones de texte pour les prénom et nom, ainsi qu’une combobox pour le choix de la fonction. Le bas du formulaire contient les boutons de navigation et de gestion d’un enregistrement, dont un textbox qui affiche l’index de l’enregistrement courant et qui permet également d’aller au énième enregistrement en saisissant son index.

IV - DataUsfManager, la classe d’échange des données

IV-A - Préambule

La classe générique d’échange de données entre formulaire et table de données va devoir remplir deux rôles :

- transférer les données d’une ligne de la table vers les contrôles du formulaire ;

- transférer les données des contrôles du formulaire vers une ligne de la table de données.

Pour ce faire, nous créerons deux procédures :

- UpdateTable qui mettra à jour les données de la table avec ce qui a été saisi dans le formulaire ;

- UpdateUserForm qui transférera les données de la table vers le formulaire.

C’est aussi le DataUsfManager qui pilotera les déplacements dans la table vers l’enregistrement précédent ou suivant, vers le premier ou le dernier, ainsi que vers le énième enregistrement de la table. C’est lui aussi qui ajoutera un nouvel enregistrement à la table ou qui supprimera une ligne du tableau structuré.

IV-A-1 - Rôles du DataUsfManager

Il faut noter ici qu’à aucun moment le DataUsfManager ne doit dialoguer avec l’utilisateur. Ce n’est pas son rôle. C’est au userform qu’il appartient d’avertir l’utilisateur au moyen de messages. Le DataUsfManager devra donc renvoyer au userform le résultat du traitement qui lui a été demandé.

Pour ce qui est des interactions avec le userform, elles seront strictement limitées aux échanges avec les contrôles renseignés dans la table de mappage. Il n’appartient pas au DataUsfManager de modifier par exemple la valeur du textbox tboIndex. En effet, on pourrait très bien piloter les échanges sans ce textbox. Le DataUsfManager n’impose donc rien d’autre au userform que de disposer des contrôles dont la liste lui est passé dans l’array de mappage. Le DataUsfManager n’impose donc pas non plus au userform de disposer des boutons de déplacements ou autre pour réaliser l’échange.

Le DataUsfManager remplit donc 3 rôles :

1. Faciliter les échanges Table/userform et userform/Table grâce à une table de mappage Colonne/Contrôle ;

2. Faciliter et rendre générique les déplacements dans la table et les mises à jour du formulaire conséquentes à ces déplacements ;

3. Proposer une couche d’abstraction permettant au programmeur de ne pas manipuler directement les listobjects.

IV-B - Données manipulées par le DataUsfManager

Le DataUsfManager doit échanger des données entre une table de données et un userform en se basant sur un tableau de mappage faisant la liaison entre les colonnes de la table et les contrôles du userform. Il doit savoir sur quelle ligne de la table il est positionné pour permettre les échanges bidirectionnels entre la table et le userform.

Par choix d’architecture, le tableau structuré, le userform et l’array de mappage seront passés au DataUsfManager par une procédure d’initialisation. Cette technique permet l’initialisation de l’objet en une seule ligne et nous dispense des propriétés en écriture. Elle nous garantit également la pérennité de la paire Table/userform puisque les deux doivent être passés au même moment lors de l’initialisation.

On notera que le DataUsfManager recevra la plage du tableau structuré et non le listobject. L’objet ListObject est ainsi manipulé uniquement au niveau de la classe DataUsfManager, et la notion même de ListObject est ainsi étrangère au reste du code.

Il y a donc bien une dématérialisation du listobject au niveau du code applicatif grâce à la couche d’abstraction que représente le DataUsfManager.

IV-B-1 - Variables privées

Nous allons créer les variables privées qui sont, par convention d’écriture, préfixées de la minuscule « m ».

Private mIndex As Long
Private mTable As ListObject
Private mUsf As UserForm
Private mMap

Nous verrons en développant le code quand et comment nous utiliserons ces variables.

IV-B-2 - Initialisation

L’initialisation est une procédure très simple puisqu’elle consiste simplement à passer les trois arguments reçus aux variables privées. La procédure Init constitue donc le sas par lequel on renseigne au DataUsfManager les notions qu’il va manipuler.

Public Sub Init(Rng As Range, Usf As UserForm, Map)
Set mTable = Rng.ListObject
Set mUsf = Usf
mMap = Map
End Sub

IV-B-2-a - Table de données

Comme nous l’avons vu lors du rappel sur les tables de données, nous retrouvons le ListObject grâce à sa référence structurée qui fait office de « plage nommée ». Comme nous le voyons dans la procédure d’initialisation, il suffit de passer la « plage nommée » du tableau structuré comme, et c’est le DataUsfManager qui en déduit le ListObject correspondant.

Range("t_Contacts").ListObject

IV-B-2-b - Userform

Ici, aucune manipulation particulière ne doit être effectuée, il suffit de passer le userform lié à la table de données.

IV-B-2-c - Array de mappage

Par choix, j’ai décidé que cet array serait de base 1. Cela signifie que son premier élément sera d’indice 1. Ce choix est dicté par le fait que nous travaillons avec Excel, et qu’il se pourrait, comme nous le verrons plus loin, que l’array soit issu d’une plage de données Excel, dont la transformation en array nous donne toujours un array de base 1.

Nous pourrions ne pas nous préoccuper de la base de l’array, mais ce serait au prix d’une petite complexification du code.

Par choix d’architecture, pour ne pas polluer la procédure d’initialisation du userform, l’array sera le résultat d’une fonction. Dans un premier temps, l’array sera créé en hard coding à l’intérieur de cette fonction. Lorsque nous manipulerons plusieurs paires Table/Userform au sein d’un même projet, il sera intéressant de fonctionner différemment, et nous verrons alors une technique générique de création de l’array en fonction de la paire Table/Userform manipulée par le DataUsfManager.

Sur base de la table des contacts et du userform usfContact, la fonction getMap() crée l’array de mappage. Cette fonction sera placée dans un module standard. Elle servira à alimenter le DataUsfManager avec l’array de mappage.

Function getMap()
Dim Map(1 To 3, 1 To 2)

Map(1, 1) = "tboFirstname"
Map(1, 2) = "Prénom"
Map(2, 1) = "tboLastName"
Map(2, 2) = "Nom"
Map(3, 1) = "cboFunction"
Map(3, 2) = "Fonction"
getMap = Map
End Function

Normalement, cette fonction ne doit poser aucun problème de compréhension.

IV-C - Fonctionnalités du DataUsfManager

Plus haut dans le tutoriel, nous avons vu comment l’on pouvait gérer les cellules d’une ligne table de données. Pour rappel :

Une ListRow met à disposition la plage des cellules qui la composent ;

On peut atteindre une ListColumn par son index ou par son nom, l’un permettant de récupérer l’autre.

Les index des cellules de la plage de la ListRow correspondent aux index des ListColumns du ListObject. On connaît les noms des ListColumns puisqu’elles sont renseignées dans l’array de mappage. Pour valoriser une cellule ou pour en récupérer la valeur, on va donc :

- boucler sur l’array de mappage ;

- dans la boucle, récupérer l’index de la listcolumn à chaque boucle ;

- utiliser la cellule de la plage de la ListRow dont l’index correspond à celui récupéré.

IV-C-1 - Mise à jour de la table avec les données du formulaire

Par convention, l’array de mappage est de base 1. On va donc boucler sur l’array de mappage, récupérer l’index de la ListColumn pour savoir quelle cellule de la ListRow courante il faut valoriser, et transvaser la valeur du contrôle du userform à la cellule concernée.

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(mMap)
r.Range(mTable.ListColumns(mMap(Counter, 2)).Index).Value = mUsf.Controls(mMap(Counter, 1)).Value
Next
End Sub

IV-C-2 - Mise à jour du formulaire avec les données de la table

Dans l’autre sens, on fonctionne exactement de la même façon, en transvasant la valeur de la cellule à celle du contrôle correspondant.

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(mMap)
mUsf.Controls(mMap(Counter, 1)).Value = r.Range(mTable.ListColumns(mMap(Counter, 2)).Index).Value
Next
End Subpo

IV-C-3 - Déplacement de l’index dans les lignes du tableau structuré

IV-C-3-a - Récupération de la ligne courante du DataUsfManager

Comme on l’a vu, le DataUsfManager se déplace dans la table et mémorise donc la ligne de la table sur laquelle il est positionné. C’est la variable privée mIndex qui mémorise cette position et c’est la propriété Index permet de récupérer la variable privée. Elle est en lecture seule pour empêcher une modification de l’index, ce qui aurait bien sûr des conséquences funestes sur notre table de données.

Property Get Index() As Long
Index = mIndex
End Property

IV-C-3-b - Avancer d’un enregistrement

Pour avancer d’un enregistrement, il suffit d’ajouter 1 à l’index, puis de rafraîchir le userform. Il faut toutefois tester que l’on n’est pas au-delà de la dernière ligne de la table. Si l’on a pu avancer d’une ligne, la fonction renvoie 0, sinon 1. Le userform pourra alors éventuellement afficher un message « dernier enregistrement ».

Function GoToNext() As Long
If mIndex < mTable.ListRows.Count Then
mIndex = mIndex + 1
updateUserform
Else
GoToNext = 1
End If
End Function

IV-C-3-c - Enregistrement précédent

On procède de même pour remonter d’une ligne, en testant qu’on n’est pas sur la première ligne de la table. Elle aussi renvoie 0 (tout est ok) ou 1 (on est sur le premier enregistrement) pour permettre au userform de gérer la situation.

Function GoToPrevious() As Long
If mIndex > 1 Then
mIndex = mIndex - 1
updateUserform
Else
GoToPrevious = 1
End If
End Function

IV-C-3-d - Aller à l’enregistrement x

Il devrait être également utile de pouvoir aller directement au énième enregistrement de la table. Il suffit de prévoir une fonction qui va recevoir l’index à atteindre et qui, elle aussi, renverra une valeur permettant au userform de gérer la situation. Il faut noter ici qu’il est important de tester que l’enregistrement à atteindre n’est pas hors limite.

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

IV-C-3-e - Supprimer une ligne de la table

Le DataUsfManager doit aussi pouvoir supprimer l’enregistrement sur lequel il se trouve, et se repositionner sur la ligne qui est remontée. Toutefois, si l’on a supprimé le dernier enregistrement, il faut se repositionner sur le « nouveau » dernier enregistrement.

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

IV-C-3-f - Préparer un nouvel enregistrement

Ici, je rappelle que, mis à part la mise à jour des contrôles dont les noms sont présents dans l’array de mappage, le DatausfManager ne peut pas modifier le userform. Il n’est pas donc pas capable de vider les champs. Il suffit donc de prévoir qu’un index bien précis indiquera que le DataUsfManager est préparé pour recevoir un nouvel enregistrement. Par convention, j’ai choisi l’index de valeur 0, puisqu’il n’y a pas de ligne 0 dans la table.

Sub GotoNew()
mIndex = 0
End Sub

IV-D - Code complet

Voici le code complet, à ce stade du tutoriel, du DataUsfManager. Nous verrons tout à l’heure deux améliorations qui faciliteront l’ergonomie du userform.

Option Explicit

Private mIndex As Long
Private mTable As ListObject
Private mUsf As UserForm
Private mMap

Public Sub Init(Rng As Range, Usf As UserForm, Map)
Set mTable = Rng.ListObject
Set mUsf = Usf
mMap = Map
End Sub

Property Get Index() As Long
Index = mIndex
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(mMap)
mUsf.Controls(mMap(Counter, 1)).Value = r.Range(mTable.ListColumns(mMap(Counter, 2)).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(mMap)
r.Range(mTable.ListColumns(mMap(Counter, 2)).Index).Value = mUsf.Controls(mMap(Counter, 1)).Value
Next
End Sub

V - Utilisation du formulaire pour gérer les enregistrements

L’architecture que je propose amène à ce que le userform dispose d’une propriété de type DataUsfManager, qu’il faudra donc ajouter au userform puisque celui n’en dispose pas nativement. Ce DataUsfManager sera manipulé lors des actions sur les boutons du userform.

V-A - Initialisation du DataUsfManager

Maintenant que le DataUsfManager est construit, nous allons le greffer au userform pour que ce dernier puisse le piloter selon l’ergonomie mise en place dans le userform

Je ne suis pas fan de l’événement initialize d’un userform. Cet événement doit être utilisé uniquement pour ce qui n’est pas susceptible de devoir être paramétré. Dans ce cas, on aurait pu créer le DataUsfManager au moment de l’initialisation, mais je vais vous proposer une autre technique.

On travaillera avec une variable privée qui sera exposée hors du userform au travers d’une propriété en lecture. Lorsque la propriété sera appelée, le DataUserManager sera créé s’il n’existe pas.

Private mDataManager As DataUSFManager

Property Get DataManager() As DataUSFManager
If mDataManager Is Nothing Then Set mDataManager = New DataUSFManager
Set DataManager = mDataManager
End Property

Cette propriété publique permettra à procédure qui initialise et affiche le userform d’initialiser correctement le DataUsfManager en lui passant les arguments dont il a besoin.

V-B - Déplacements dans la table et récupération des valeurs

V-B-1 - Aller à l’enregistrement suivant

En cliquant sur le bouton >, on avance d’un enregistrement grâce au code suivant. On remarque qu’on utilise la fonction GoToNext du DataUsfManager pour avancer dans la table et actualiser les contrôles, puis l’index courant est récupéré grâce à la propriété Index du DataUsfManager pour valoriser le textbox tboIndex. On remarque également que, dans ce cas-ci, le userform se sert du résultat de la fonction GoToNext pour informer l’utilisateur qu’il est à la fin de la table.

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

V-B-2 - Aller à l’enregistrement précédent

Bien sûr, remonter dans la table s’opère de façon identique, en utilisant les propriétés ad hoc du DataUsfManager après avoir pressé sur <.

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

V-B-3 - Aller au énième enregistrement

Le userform expose un textbox tboIndex qui indique sur quel enregistrement on est positionné, mais il permet également de choisir sur quel enregistrement l’on souhaite se positionner. Il suffit de saisir le numéro de l’enregistrement souhaité, puis d’utiliser la fonction GoToRecord du DataUsfManager. A nouveau, la valeur retournée par la fonction permettra d’informer l’utilisateur s’il est hors limites.

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

V-B-4 - Supprimer un enregistrement

Le DataUsfManager expose la possibilité de supprimer l’enregistrement courant et de se positionner sur celui qui a pris sa place. Au niveau du userform, c’est le bouton Delete (X) qui permettra l’opération. S’il reste au moins un enregistrement après la suppression, la fonction Delete du DataUsfManager renverra 0, sinon 1.

Private Sub btnDelete_Click()
If mDataManager.Delete() = 1 Then
ClearForm
MsgBox "La table est vide"
End If
End Sub

V-B-5 - Se positionner sur un nouvel enregistrement

On l’a vu, se positionner sur un nouvel enregistrement revient, pour le DataUsfManager, à se positionner sur l’index 0. Au niveau du userform, il faut vider le textbox tboIndex et vider les champs, grâce à la procédure ClearForm déjà utilisée pour le Delete. On le voit, le code, bien architecturé, est minimaliste.

Private Sub btnNew_Click()
ClearForm
mDataManager.GotoNew
End Sub

V-C - Code complet du userform à ce stade

Voici le code complet du userform à ce stade du tutoriel. Il sera enrichi tout à l’heure par deux possibilités ergonomiques supplémentaires permettant de se positionner sur le premier ou le dernier enregistrement de la table.

Option Explicit

Private mDataManager As DataUSFManager

Property Get DataManager() As DataUSFManager
If mDataManager Is Nothing Then Set mDataManager = New 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

Sub ClearForm()
tboFirstName.Value = ""
tboLastName.Value = ""
tboIndex = ""
cboFunction.ListIndex = -1
End Sub

V-D - Appel du formulaire, initialisation des composants

Une procédure d’initialisation et de visualisation du formulaire sera créée dans un module standard. Elle initialisera le userform et son DataUsfManager. Nous allons voir que cette procédure est très simple à mettre en place.

Sub ShowContactForm()

With usfContact
.DataManager.Init Range("t_Contacts").ListObject, usfContact, getMap()
.cboFunction.List = Range("t_Fonctions").Value
If .DataManager.GotoRecord(1) = 0 Then
.tboIndex = .DataManager.Index
End If
.Show
End With
Unload usfContact
End Sub

Pour rappel, si le userform n’est pas déjà chargé, le simple fait de l’utiliser dans un code le charge de manière implicite. Il n’est donc pas nécessaire, sauf à être puriste, de le charger avant de le manipuler. La ligne d’initialisation du DataUsfManager du userform est simple, la procédure Init reçoit les trois éléments dont elle a besoin. La combobox est alimentée en une fois et l’on positionne le DataUsfManager sur le premier enregistrement avant d’afficher le userform. Il convient aussi, puisque le userform dispose du textbox tboIndex, de passer à celui-ci l’index de la ligne de la table vers laquelle pointe le DataUsfManager.

Notons au passage que c’est bien le DataUsfManager qui gère le transfert Table=>Userform grâce à sa fonction GoToRecord.

V-E - Constatations

On constate ici que l’architecture proposée amène à créer des fonctions courtes, notamment parce que le code applicatif est déporté dans le DataUsfManager. Le code du userform appelle le code applicatif, met à jour les valeurs qui lui sont propres (tboIndex) et informe éventuellement l’utilisateur en fonction des réponses du DataUsfManager.

Ce qui a été mis en place résulte d’un choix conceptuel. On aurait pu, à l’instar de ce qui fait en ADODB, prévoir des propriétés BOF et EOF pour déterminer que le DataUsfManager est positionné sur la première ou la dernière ligne.

On constate aussi que le DataUsfManager dématérialise l’utilisation du ListObject au niveau du userform, mais également au niveau de la fonction appelante, puisque c’est une plage, définie par la référence structurée, qui est passée en argument de l’initialisation du DataUsfManager, et non le ListObject lié à cette plage. Nulle part dans le userform il n’est question de manipuler le ListObject. C’est un point majeur de l’utilisation d’objets génériques tels que le DataUsfManager : ajouter une couche d’abstraction qui permet au programmeur de manipuler un objet, ici un listobject, au travers d’un objet qui simplifie et rend génériques les manipulations.

V-F - Deux améliorations, parmi d’autres

Le DataUsfManager peut évidemment être enrichi par des fonctionnalités génériques. J’ai abordé tout à l’heure les propriétés BOF et EOF. Ici , on va ajouter deux boutons dans le userform pour atteindre le premier ou le dernier enregistrement.

V-F-1 - GotoFirst

Pour aller au premier enregistrement, c’est en fait assez simple, puisqu’il suffit de tester que le tableau structuré n’est pas vide, et s’il ne l’est pas, d’aller au premier enregistrement.

V-F-2 - GotoLast

Pour aller au dernier enregistrement, il va falloir tester que la table de données n’est pas vide et, si elle ne l’est pas, aller au dernier enregistrement. Ici, on va se servir de la propriété Count de la collection ListRows du tableau structuré pour savoir comment se positionner.

V-F-3 - Modifications du DataUsfManager

Tant pour aller au premier qu’au dernier, on va se servir de ce qui existe déjà. En effet, le DataUsfManager dispose de GoToRecord qui attend le numéro de l’enregistrement. Il suffit donc de lui passer 1 ou le nombre de lignes du tableau structuré pour aller respectivement au premier ou dernier enregistrement. La variable privée mTable, sa collection ListRows et la propriété Count de cette collection permettent d’obtenir facilement le nombre de lignes de la table de données.

Function GotoFirst()
GotoFirst = GotoRecord(1)
End Function

Function GoToLast()
GoToLast = GotoRecord(mTable.ListRows.Count)
End Function

V-F-4 - Modifications du userform

Au niveau du userform, il faut ajouter les deux boutons btnFirst et btnLast, et sur clic, gérer les fonctions correspondantes du DataUsfManager.

On constate que c’est à nouveau le userform et non le DataUsfManager qui affiche les messages à l’utilisateur.

On constate également que c’est le DataUsfManager qui gère le ListObject, le userform se contentant d’appeler la fonction GoToLast du DataUsfManager.

Private Sub btnFirst_Click()
With mDataManager
If .GotoFirst() = 0 Then
tboIndex = .Index
Else
MsgBox "Il n'y a pas d'enregistrement", vbExclamation
End If
End With
End Sub

Private Sub btnLast_Click()
With mDataManager
If .GoToLast = 0 Then
tboIndex = .Index
Else
MsgBox "Il n’y a pas d’enregistrement", vbExclamation
End If
End With
End Sub

V-F-5 - Constatations

On remarquera ici que les modifications se font sur deux plans :

- DataUsfManager, où les modifications sont très légères puisque nous réutilisons l’existant ;

- Userform, où les modifications consistent à ajouter les boutons puis le code d’appel des nouvelles fonctionnalités du DatausfManager.

VI - Utilisation d’une table de mappage

La fonction GetRow crée actuellement un array en hard coding.

Function getMap()
Dim Map(1 To 3, 1 To 2)

Map(1, 1) = "tboFirstname"
Map(1, 2) = "Prénom"
Map(2, 1) = "tboLastName"
Map(2, 2) = "Nom"
Map(3, 1) = "cboFunction"
Map(3, 2) = "Fonction"
getMap = Map
End Function

Si cette solution tient la route pour une paire Table/Userform avec peu de paires Colonne/Contrôle, on se rend vite compte qu’elle n’est pas pertinente dès lors que l’on utilise plusieurs paires Table/Userform au sein d’une même application et/ou que le nombre de paires Colonne/Contrôle devient plus important.

Toujours dans le but de rendre notre code le plus générique possible, ce qui permettra de devoir le modifier le moins (souvent) possible, il serait intéressant de créer une table de mappage en Excel et de récupérer l’array de mappage voulu sur base d’une fonction.

VI-A - Table de mappage

La table de mappage va reprendre sans surprise deux colonnes, l’une pour les noms des contrôles, l’autre pour les noms de colonnes correspondantes de la table mappée, et l’on y ajoutera une colonne reprenant le nom du userform.

VI-B - Fonction de création de l’array de mappage

La fonction de création de l’array va donc parcourir les lignes de la table et reprendre les paires en fonction du userform concerné.

Nous ne connaissons pas au départ le nombre de lignes que l’array va devoir contenir. Inutile d’imaginer prévoir un array avec un très grand nombre de lignes, ce n’est pas professionnel. Puisque nous sommes en Excel, nous allons pouvoir compter combien de lignes concernent notre userform et dimensionner l’array en fonction de cette information.

La technique consiste donc à retrouver le nombre de lignes nécessaires, dimensionner l’array puis parcourir la table de mappage pour injecter dans l’array les infos souhaitées. Le dimensionnement d’un array en utilisant des variables passe par l’instruction ReDim.

Function getMap1(UsfName As String)
Dim CountOf As Long
Dim r As Range
Dim Counter As Long: Counter = 1
CountOf = Application.CountIfs(Range("t_MapUsf[Usf]"), UsfName)
ReDim Map(1 To CountOf, 1 To 2)
For Each r In Range("t_MapUsf[Usf]")
If r.Value = UsfName Then
Map(Counter, 1) = r(1, 2).Value
Map(Counter, 2) = r(1, 3).Value
Counter = Counter + 1
End If
Next
getMap1 = Map
End Function

Il existe d’autres techniques pour « aller plus vite » selon leurs défenseurs (charger la table dans un tableau puis boucler sur le tableau, filtrer la table, ….). Je laisse aux esprits tortueux le soin de perdre leur temps à essayer de gagner un centième de seconde avec leurs techniques « plus rapides » et j’attends la démo qui illustrait et quantifierait le « gain de temps ».

Avec cette fonction, nous généralisons le processus puisque la même fonction va pouvoir renvoyer les infos pour n’importe quelle paire Table/Userform. Dans le tableau structuré illustré ci-dessus, nous voyons que la table contient déjà les paires de valeurs pour un nouveau useform, et l’array de mappage concernant ce nouveau userform sera créé avec la fonction que nous venons de voir.

VII - Modifications structurelles de la table et du userform

VII-A - Modification du nom d’une colonne

VII-A-1 - Utilisation d’un array de mappage sans table liée

Avec un array créé en hard coding, tel qu’illustré dans la fonction GetMap(),il est nécessaire de modifier le code.

VII-A-2 - Utilisation d’un array de mappage sur table liée

Si c’est un tableau structuré qui contient les paires Colonne/Contrôle, il suffit de modifier le nom de la colonne au sein de la table de données. On constate dès lors qu’il n’y a pas de modifications à apporter dans le code.

VII-A-3 - Et le DataUsfManager ?

Nous constatons qu’aucune modification de code n’a été opérée sur le DataUsfManager. Il a été conçu et codé pour remplir son rôle d’échangeur entre n’importe quel formulaire et n’importe quelle table liée, pour peu qu’on lui fournisse une table de mappage qui établit les paires Colonne/Contrôle. Il semble bien que notre objectif soit atteint.

Dès lors, si nous utilisons une table de mappage, il n’y a aucune modification de code à apporter à notre projet VBA.

VII-B - Ajout d’une colonne supplémentaire

En cas d’ajout d’une colonne à la table de données, il y a aura peu de modifications à réaliser. Il faudra ajouter la colonne à la table, ajouter le contrôle au userform et ajouter la ligne de mappage.

VII-B-1 - Modification de la table

Il faut bien entendu ajouter (ou insérer) la nouvelle colonne dans la table de données. Ici, nous allons ajouter l’adresse mail à nos contacts.

VII-B-2 - Modification du formulaire

Le formulaire doit bien entendu disposer du contrôle associé à la nouvelle colonne du tablau structuré. Nous allons donc ajouter le textbox tboMail à usfContact.

VII-B-3 - Ajout de la paire Colonne/Contrôle dans la table de mappage

Il faut également ajouter la paire de valeurs dans la table de mappage. Je présente ici la table triée, mais ce n’est pas nécessaire. C’est simplement plus facile à maintenir.

VII-B-4 - Modification de la fonction qui renvoie l’array de mappage

A nouveau, selon que l’array est créé en hard coding ou pas, il faudra modifier ou non la fonction GetMap de création de l’array. Puisque nous partons du principe que nous gérons plusieurs paires Table/Userform au sein d’un même projet, nous disposerons d’une table de mappage dans Excel, de sorte qu’il n’y aura aucune modification à réaliser à ce niveau-là.

VII-B-5 - Et le DataUsfManager ?

Nous constatons à nouveau que le DataUsfManager n’a pas dû être modifié suite aux modifications structurelles de la table et du formulaire. Il joue parfaitement son rôle de brique logicielle générique.

VII-B-6 - Utilisation du userform

A nouveau, il n’y a rien à modifier au niveau de la fonction d’appel du userform, l’email du contact est bien récupéré au niveau du userform, et il est bien transféré dans la table lors d’une modification.

Nous constatons donc qu’aucune modification de code n’a dû être réalisée. Le DataUsfManager associé à la table de mappage joue parfaitement son rôle de brique logicielle générique. Il est initialisé avec certaines valeurs et les utilise au travers d’un code générique.

VIII - Utilisation du DataUsfManager connecté à une autre paire Table/Formulaire

Afin de tester le caractère générique de notre DataUsfManager, nous allons tester l’outil avec une nouvelle table.

VIII-A - Table des localités

Au début du tuto, je vous ai parlé de la table des localités. Je la remets ici.

VIII-B - Formulaire de gestion d’une localité

Histoire de ne pas réinventer la roue, nous allons reprendre le formulaire usfContact et en supprimer les contrôles superflus. Il ne faut pas oublier de renommer les contrôles.

VIII-C - Table de mappage

Ici, la table de mappage est déjà prête. Si ce n’était pas le cas, il faudrait bien sûr ajouter les lignes reprenant les paires de valeurs Colonne/Contrôle.

VIII-D - Procédure d’appel du formulaire

La procédure d’appel est identique à celle appelant usfContact. Il faut bien entendu modifier les argument du DataUsfManager.Init et supprimer l’alimentation du combobox qui a été supprimé.

Sub ShowCityForm()
With usfCity
.DataManager.Init Range("t_Localités").ListObject, usfCity, getMap1("usfCity")
If .DataManager.GotoRecord(1) = 0 Then
.tboIndex = 1
End If
.Show
End With
Unload usfCity
End Sub

VIII-E - Et le DataUsfManager ?

Ici, nous constatons encore que le DataUsfManager n’a pas dû être modifié. Nous pouvons établir que c’est vraiment une brique logicielle générique qui se connecte d’un côté à une table et de l’autre à un formulaire pour réaliser les échanges bidirectionnels de données.

IX - Utilisation du DataUsfManager connecté à un formulaire proposant une autre ergonomie

Notre DataUsfManager permet plusieurs choses, mais certaines peuvent s’avérer inutiles dans certains cas. Nous allons voir un cas où le DataUsfManager n’est pas placé dans le UserForm, et où ce dernier « ne fait rien », à part dire si l’on a cliqué sur le bouton de validation lorsque la main est rendue au code qui l’appelle. Et pourtant, nous allons récupérer les données dans le userform et en transférer les modifications dans la table après validation.

Ici, nous allons les données de différentes entreprises.

IX-A - Table de données

La table de données reprend le nom, le site web, l’émail général et le fait que l’entreprise est active ou pas.

IX-B - Userform

Bien sûr, le userform nomme usfEnterprise est calqué sur cette structure.

Le code proposé ici est minimaliste, servant simplement à illustrer l’utilisation du DataUsfManager. Il conviendrait d’utiliser des fonctions de validation permettant de tester que ce qui a été rempli est conforme aux attentes, mais ce n’est pas le but ici. J’ai écrit un billet de blog qui illustre comment mettre ces vérifications en place.

Option Explicit

Public Result As String

Private Sub btnCancel_Click()
Result = "Cancel"
Me.Hide
End Sub

Private Sub btnValidate_Click()
Result = "Validate"
Me.Hide
End Sub

Une propriété publique récupère le bouton sur lequel on a cliqué, puis le userform est masqué et la main est rendue au code appelant.

IX-C - Table de mappage

La table de mappage est enrichie de quatre nouvelles lignes reprenant le nom du userform et les paires de valeurs Colonne/Contrôle.

IX-D - Procédure d’appel et de gestion du userform

Dans ce cas, le choix ergonomique est le suivant :

- Soit on doubleclique sur la ligne d’une entreprise dans le tableau structuré pour afficher et éventuellement modifier les valeurs ;

- Soit on clique sur le bouton de création placé au dessus de la table, sur la feuille de travail pour créer une nouvelle société.

Dans les deux cas, la procédure appelée sera la même et utilisera un argument optionnel. Pour l’affichage des données d’une société existante, on passera l’index de la ligne en argument, alors que pour une nouvelle société, on ne le mentionnera tout simplement pas.

Sub ShowEnterpriseForm(Optional Target As Range)
Dim dum As New DataUSFManager

With dum
.Init Range("t_Entreprises"), usfEnterprise, getMap1("usfEnterprise")
If Not Target Is Nothing Then .GotoIndexFromCell Target
End With
With usfEnterprise
.Show
If .Result = "Validate" Then dum.UpdateTable
End With
Unload usfEnterprise
End Sub

Nous allons voir comment utiliser cette procédure selon le cas que nous gérons.

On observe qu’à nouveau, il n’y a aucune manipulation de ListObject. C’est le DataUsfManager qui retrouvera le ListObject sur base de la plage qui lui passée lors de son initialisation.

IX-D-1 - Visualisation d’une entreprise existante

Le double-clic sur une ligne de la table va permettre de calculer l’index de la ligne et d’appeler la procédure d’appel en lui passant l’argument souhaité. Nous allons donc gérer l’événement au niveau de la feuille qui contient la table des entreprises.

Le calcul de l’index s’effectue en utilisant la cellule Target et la référence structurée qui pointe vers le tableau.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Index As Long

If Not Intersect(Target, Range("t_Entreprises")) Is Nothing Then
Cancel = True
Index = Target.Row - Range("t_Entreprises").Row + 1
ShowEnterpriseForm Index
End If
End Sub

Nous aurions pu utiliser la syntaxe suivante pour calculer l’index :

Index = Target.Row - Range("t_Entreprises[#Headers]").Row

Il faut cependant noter deux choses :

- En VBA, la référence structurée doit être rédigée en anglais ;

- Il faut que la ligne d’entête soit visible sous peine de plantage. Dans les faits, je ne rencontre que très rarement, voire jamais, un tableau structuré dont on aurait masqué la ligne d’entête.

Comme on le voit, on a ici sorti le DataUsfManager du userform et il est piloté par la procédure d’appel. Un double-clic sur une ligne de la table des entreprises permet l’affichage du userform avec les données de l’entreprise choisie.

Si l’on modifie une donnée, il suffira de cliquer sur Valider pour rendre la main à la procédure appelante qui demandera au DataUsfManager de transférer les données dans la table, sur la bonne ligne.

IX-D-2 - Création d’une société

Pour créer une société, on peut placer un bouton sur la feuille, par exemple au dessus du tableau, et ce bouton appellera la procédure ShowEnterpriseForm. Attention, cette procédure requérant un argument (même optionnel), elle n’apparaît pas dans la liste et il faut en saisir le nom à la main.

En cliquant sur ce bouton, le formulaire apparaîtra vide. Il suffira alors de saisir les infos puis de cliquer sur Valider pour créer la nouvelle ligne dans la table.

A nouveau, le DataUsfManager n’a subi aucune modification de code pour fonctionner comme nous l’attendions. Les modifications ergonomiques, tant au niveau du formulaire que de l’affichage du formulaire pour la modification ou la création, n’influencent pas son fonctionnement.

Nous avons bien une brique logicielle générique qui peut se greffer sur notre code et notre ergonomie pour systématiser les échanges entre un formulaire et une table de donnés.

IX-E - Autre possibilité d’architecture

Dans le code que nous venons de voir, nous devons manipuler les références structurées pour calculer l’index de la ligne sur laquelle le double-clic est effectué. Nous aurions pu choisir une architecture qui dématérialisait encore plus l’utilisation d’un listobject et d’une référence structurée.

Nous aurions pu passer non pas l’index calculé au niveau du double-clic, mais la cellule qui subit le double-clic à la fonction d’appel du userform, et passer également la cellule à une nouvelle fonction du DataUsfManager qui aurait alors eu la responsabilité de calculer l’index. Les codes appelant seraient alors allégés puisque le calcul de l’index serait déporté sur le DataUsfManager.

IX-E-1 - Modification du DataUsfManager

Le DataUsfManager doit exposer une fonction qui reçoit une cellule pour atteindre l’enregistrement, et non un entier long comme la fonction GoToRecord. On va créer la fonction .GotoIndexFromCell. Cette fonction calculera l’index puis le passera à GoToRecord.

Function GotoIndexFromCell(Cell As Range)
Dim Index As Long

Index = Cell.Row - mTable.Range.Row
GotoIndexFromCell = GotoRecord(Index)
End Function

IX-E-2 - Modification au niveau de la feuille de calcul

Au lieu de calculer l’index ici, on délègue ce traitement au DataUsfManager et on passe la cellule, et non l’index, à la procédure d’appel.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Index As Long

If Not Intersect(Target, Range("t_Entreprises")) Is Nothing Then
Cancel = True
ShowEnterpriseForm Target
End If
End Sub

IX-E-3 - Modification de la fonction d’appel

Bien sûr, la fonction d’appel ne doit plus recevoir un entier long, mais une cellule, et passer cette cellule à la nouvelle fonction du DataUsfManager.

Sub ShowEnterpriseForm(Optional Target As Range)
Dim dum As New DataUSFManager

With dum
.Init Range("t_Entreprises").ListObject, usfEnterprise, getMap1("usfEnterprise")
If Not Target Is Nothing Then .GotoIndexFromCell Target
End With
With usfEnterprise
.Show
If .Result = "Validate" Then dum.UpdateTable
End With
Unload usfEnterprise
End Sub

IX-E-4 - Conclusions

Avec cette technique, on a dématérialisé encore plus les manipulations du ListObject, puisque l’on a déporté le calcul de l’index dans le DataUsfManager. Le code qui le manipule au niveau de la fonction d’appel et/ou du userform n’utilise à aucun moment la notion de listobject. La seule notion liée à un listobject est celle de la référence structurée.

Je ne dis pas que tout doit toujours être poussé à ce point, mais il est intéressant de constater que l’utilisation d’un objet comme le DataUsfManager, issu d’une classe personnalisée que nous avons créée, permet de simplifier l’utilisation du code par un transfert du code au sein d’une classe qui expose des méthodes et propriétés qui font abstraction d’un objet complexe dont on simplifie certaines manipulations par la création de fonctions qui, paramétrées, permettent de manipuler des objets « sans savoir qu’on les manipule ». Un peu comme monsieur Jourdain qui faisait de la prose dans le savoir…

Le fichier proposé avec ce tutoriel ne reprend cette façon de faire.

X - Fichiers

Vous pouvez télécharger les fichiers utilisés dans ce tutoriel :

- Fichier Excel ;

- Fichier cls du DataUsfManager à intégrer à vos propres projets.

XI - Conclusions

Comme nous l’avons vu, il est possible, à moindres frais et sans grandes procédures, de :

- Généraliser une approche informatique ;

- Systématiser notre code pour le rendre sûr, pérenne, maintenable sans grands chamboulements, évolutif par petites touches ;

- Simplifier l’accès aux donnés d’une table grâce à l’utilisation judicieuse des ListRows et ListColumns d’un ListObject ;

- Rendre notre code le plus indépendant possible de la situation de notre classeur, puisque nous avons vu que nos tableaux structurés peuvent se trouver n’importe où sur n’importe quelle feuille sans aucun impact sur notre code, le déplacement ou la permutation de colonnes n’ayant aucune incidence sur notre code ;

- Dématérialiser l’utilisation d’un ListObject au travers d’un objet qui prend les manipulations du listobject en charge en exposant des fonctions simples qui masquent le listobject ;

- Gagner du temps, et donc de l’argent (surtout si le développement est votre profession).

Cette approche vaut bien entendu pour le VBA, mais peut être transposée à tout projet de développement informatique.

Essayez TOUJOURS de rendre votre code générique. Avec cette approche, vous allez gagner du temps, produire un code sûr et réutilisable. Si votre boulot est de développer des projets VBA pour Excel, cette approche vous fera gagner de l’argent… et de la sérénité 😉

XII - Remerciements