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.

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.

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-A-1. Tableau structuré en Excel▲

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.

 

Sélectionnez

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.

 

Sélectionnez

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.

 

Sélectionnez

  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 :

 

Sélectionnez

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 :

 

Sélectionnez

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 :

 

Sélectionnez

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 (2^ième cellule de la ligne), je peux récupérer l’index correspondant en récupérant celui de la ListColumn nommée « Nom ».

 

Sélectionnez

t.ListColumns("Nom").Index

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

 

Sélectionnez

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é.

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▲

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-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.

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.

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▲

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.

IV-B-1. Variables privées▲

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

IV-B-2. Initialisation▲

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

Range("t_Contacts").ListObject

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

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▲

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▲

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é▲

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

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

Function GoToPrevious() As Long
  If mIndex > 1 Then
    mIndex = mIndex - 1
    updateUserform
  Else
    GoToPrevious = 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

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 GotoNew()
  mIndex = 0
End Sub

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

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-1. Aller à l’enregistrement suivant▲

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▲

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▲

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▲

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▲

Private Sub btnNew_Click()
  ClearForm
  mDataManager.GotoNew
End Sub

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

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.

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.

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▲

V-F-2. GotoLast▲

V-F-3. Modifications du DataUsfManager▲

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

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

V-F-4. Modifications du userform▲

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▲

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.

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-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 ?▲

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▲

VII-B-5. Et le DataUsfManager ?▲

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.

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

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.

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.

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

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.

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.

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.

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

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.

 

Sélectionnez

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.

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▲

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▲

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▲

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▲