Voir le flux RSS

Pierre Fauconnier

Excel VBA: Modifier les données d'un tableau par formulaire

Note : 2 votes pour une moyenne de 3,00.
par , 07/08/2018 à 10h58 (2522 Affichages)
Salut.

Question récurrente sur le forum Excel-VBA: Comment modifier les données d'un tableau par formulaire?

Si l'on travaille avec une version récente d'Excel (au moins la 2007, pas si récente ), on s'appuiera évidemment sur les tableaux structurés, qui permettent une gestion aisée des données, tant avec Excel qu'en VBA.


Si l'on respecte les standards de la programmation (une fonction pour chaque chose, le userform uniquement pour collecter les données, …), il devient très simple de créer du code qui permet de modifier les données d'un tableau par formulaire.


Pour l'exemple, je m'appuierai sur un tableau structuré t_Contacts contenant trois colonnes

Nom : 2018-08-07 08_54_48-Window.png
Affichages : 1427
Taille : 12,0 Ko



Un userform contenant les contrôles tboID, tboFirstName et tboLastName pour les textbox, btnValidate et btnCancel pour les deux boutons vient compléter l'arsenal.

Nom : 2018-08-07 08_53_44-Window.png
Affichages : 1288
Taille : 5,7 Ko



Fidèle au principe que le userform, à part vérifier la cohérence des donnés saisies, ne fait rien (pas de récupération de données, pas de transfert dans le tableau, … RIEN!), je propose donc le code très minimaliste suivant, qui modifie la valeur de la propriété Result du userform pour que le code appelant puisse identifier le bouton sur lequel l'utilisateur a cliqué.

Code VB : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Option Explicit
 
Public Result As Long
 
 
Private Sub btnCancel_Click()
  Result = 1
  Me.Hide
End Sub
 
Private Sub btnValidate_Click()
  Result = -1
  Me.Hide
End Sub

Pour alimenter le userform avec les données, on utilisera dans un module standard une fonction qui reçoit un tableau (array) en argument ByRef (ca permet de recevoir le tableau passé et non une copie de celui-ci), et on chargera les textbox et autres contrôles avec les données du tableau. Après que le userform ait été masqué et en fonction du bouton cliqué, on replacera dans le tableau les valeurs des textbox et on valorisera la fonction de mise à jour.

Code VB : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function UpdateContact(ByRef Data) As Long
  Unload usfContact
  With usfContact
    .tboID.Value = Data(1, 1)
    .tboFirstName.Value = Data(1, 2)
    .tboLastName.Value = Data(1, 3)
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      Data(1, 1) = .tboID.Value
      Data(1, 2) = .tboFirstName.Value
      Data(1, 3) = .tboLastName.Value
    End If
  End With
  Unload usfContact
End Function


Toujours dans le module standard, on a besoin de récupérer la ligne du tableau structuré qui contient les données à modifier par userform. C'est une fonction qui s'en chargera, en utilisant la fonction VBA/Excel Evaluate qui renvoie la position d'une valeur grâce à Match (fonction Excel EQUIV).

Code VB : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
Function getRow(ID As Long) As ListRow
  Dim Row
 
  Row = Evaluate("match(" & ID & ",t_Contacts[ID],0)")
  If Not IsError(Row) Then
    Set getRow = Range("t_Contacts").ListObject.ListRows(Row)
  End If
End Function

Il reste à faire prendre la mayonnaise avec une procédure placée elle aussi dans un module standard et qui:
  • récupère la ligne du tableau;
  • transfère les données de la ligne dans un array;
  • appelle la fonction de mise à jour de l'array;
  • en fonction du résultat de cette fonction, transfère l'array à la ligne du tableau structuré.


On remarquera ici que le transfert des donnés entre la ligne du tableau et un array s'effectue en une ligne, quel que soit le nombre de colonnes du tableau…

Code VB : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Test()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
  ID = 68
  Set lr = getRow(ID)
  If Not lr Is Nothing Then
    Data = lr.Range.Value
    If UpdateContact(Data) = -1 Then
      lr.Range.Value = Data
      MsgBox "Mise à jour effectuée"
    Else
      MsgBox "Erreur durant la mise à jour"
    End If
  Else
    MsgBox "La fiche n'a pas été trouvée"
  End If
End Sub

Et voilà le travail. Vous voyez, ce n'est pas compliqué, si vous découpez votre code en fonctions dont la tâche est bien délimitée, et si vous travaillez avec les tableaux structurés qui facilitent énormément la gestion des données avec Excel et VBA (je ne le répéterai jamais assez… )

Bon travail avec nos outils préférés...
Miniatures attachées Fichiers attachés

Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Viadeo Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Twitter Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Google Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Facebook Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Digg Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Delicious Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog MySpace Envoyer le billet « Excel VBA: Modifier les données d'un tableau par formulaire » dans le blog Yahoo

Commentaires

  1. Avatar de papyxy
    • |
    • permalink
    Bonjour
    ces 2 instructions me laissent perplexe :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
      Row = Evaluate("match(" & ID & ",t_Contacts[ID],0)")
     
        Set getRow = Range("t_Contacts").ListObject.ListRows(Row)
    le terme "t_Contacts" est déclaré nulle part.
    Quant a trouver la ligne concernée: il y-a plus simple.
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonjour.

    Citation Envoyé par papyxy
    [...]
    le terme "t_Contacts" est déclaré nulle part.[...]
    t_Contacts fait référence à un tableau structuré (table de données, liste de données...)... La première image du billet illustre cela

    Pour le "il y a plus simple"... N'ayant pas ta grille de critère pour déterminer ce qui est simple de ce qui l'est moins, "il y a plus simple" ne veut rien dire pour moi. Il y a effectivement d'autres techniques, mais chacun verra la simplicité là où il a envie de la voir Personnellement, je n'aime pas les "Find" et les trouve plus compliqués à manipuler et moins souples que les formules utilisées par Evaluate...
    Mis à jour 18/10/2019 à 11h23 par Pierre Fauconnier
  3. Avatar de papyxy
    • |
    • permalink
    Citation Envoyé par Pierre Fauconnier
    Bonjour.

    t_Contacts fait référence à un tableau structuré (table de données, liste de données...)...

    Pour le "il y a plus simple"... N'ayant pas ta grille de critère pour déterminer ce qui est simple de ce qui l'est moins, "il y a plus simple" ne veut rien dire pour moi. Il y a effectivement d'autres techniques, mais chacun verra la simplicité là où il a envie de la voir Personnellement, je n'aime pas les "Find" et les trouve plus compliqués à manipuler et moins souples que les formules utilisées par Evaluate...
    Pour mes "critères de simplicité", je pensais a Une liste modifiable, par exemple ou encore passer par un dictionnaire; sans utiliser "Find".
    Parce que l' instruction "Evaluate" est super-compliquée dans cette application, et n' est pas a la portée d' un débutant.
  4. Avatar de Pierre Fauconnier
    • |
    • permalink
    Citation Envoyé par papyxy
    Pour mes "critères de simplicité", je pensais a Une liste modifiable, par exemple ou encore passer par un dictionnaire; sans utiliser "Find".
    Parce que l' instruction "Evaluate" est super-compliquée dans cette application, et n' est pas a la portée d' un débutant.
    La liste déroulante ne dispense pas de devoir trouver la ligne. Elle peut être appliquée pour de petites listes et pour autant que le choix soit fait par l'utilisateur, car si j'ai un millier d'articles, j'ai des doutes. De plus, la liste déroulante demande une intervention de l'utilisateur, ce qui n'est pas toujours le cas dans lequel on se trouvera pour récupérer la ligne d'une table de donnée. Si je suis par exemple sur la ligne de détails d'une facture dans une feuille de calcul qui contient un id d'article, je peux avoir besoin, par double clic par exemple, d'afficher la fiche de l'article dans un userform. Dans ce cas, il n'y aura pas de liste déroulante pour le choix de l'article et il faudra utiliser une autre technique (la mienne, un Find, ...). Reviendra alors la notion subjective de "plus simple"... A un "c'est plus simple" qui ne veut rien dire, je préfère la proposition d'une autre solution technique de manière à ce que chacun puisse évaluer, à sa propre subjectivité, les avantages et inconvénients des solutions proposées et à faire son propre choix.

    "Super-compliquée", c'est comme "simple", c'est subjectif, donc ça ne veut objectivement rien dire. Evaluate ne fait qu'évaluer une formule, en l'occurrence un EQUIV. Si on a des problèmes en Excel avec EQUIV, il vaut peut-être mieux qu'on ne se lance pas dans la programmation d'un userform de type CRUD

    La simplicité étant chose subjective, je trouve que utiliser un Dictionary n'est pas plus simple que d'utiliser un Evaluate.

    Cela étant dit, normalement, la recherche d'une ligne dans un tableau structuré devrait s'effectuer au travers d'une fonction générique qui renvoie la ligne du tableau concernée. On passe à cette fonction le tableau, le nom de la colonne et l'ID, et la fonction générique fait le travail en renvoyant soit la ListRow voulue, soit Nothing. Comme elle est générique, on l'écrit une fois, on se la garde au chaud dans un module et on la réutilise pour n'importe quel tableau sans plus trop se préoccuper de comment elle fonctionne.

    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Function GetListRow(Table As ListObject, ColumnName As String, Value As Variant) As ListRow
      Dim Formula As String
      Dim Index As Long
     
      If TypeName(Value) = "String" Then Value = """" & Value & """" Else Value = Value * 1
      Formula = "iferror(match({value},{table}[{column}],0),0)"
      Formula = Replace(Formula, "{value}", Value)
      Formula = Replace(Formula, "{table}", Table.Name)
      Formula = Replace(Formula, "{column}", ColumnName)
      Index = Evaluate(Formula)
      If Index > 0 Then Set GetListRow = Table.ListRows(Index)
    End Function

    Ce code permet de retrouver n'importe quelle ligne de n'importe quel tableau structuré muni d'une colonne nommée comme bon vous semble et contenant des valeurs uniques qui peuvent être du texte, une valeur numérique (a priori un long) ou une date. Je pense que normalement, ça couvre les besoins. Il suffit alors de la décortiquer pour la comprendre (si on en a envie), puis de l'utiliser sans plus se tracasser de la façon dont elle travaille. Et franchement, utiliser une fonction à laquelle on passe le tableau, le nom de la colonne et la valeur cherchée, je pense que ce n'est pas compliqué. On fait cela en Excel avec RechercheV, Equiv ou Index-Equiv, les fonctions BD telles que BDLIRE, BDSOMME, ...

    On pourrait l'écrire avec moins de lignes: Formula = Replace(Replace(Replace(...))) mais c'est à mon sens moins lisible. De plus, vu que j'ai intégré ces fonctions génériques depuis longtemps dans mes développements, j'utilise une fonction générique pour remplacer rapidement plusieurs parties de chaine.

    Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    Public Function ReplaceStrings(Source As String, Parameters) As String
      Dim I As Long
     
      ReplaceStrings = Source
      For I = LBound(Parameters) To UBound(Parameters) Step 2
        ReplaceStrings = Replace(ReplaceStrings, Parameters(I), Parameters(I + 1), 1, -1, vbTextCompare)
      Next I
    End Function

    Dès lors, la fonction générique qui renvoie la ListRow devient:
    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Function GetListRow(Table As ListObject, ColumnName As String, Value As Variant) As ListRow
      Dim Formula As String
      Dim Index As Long
     
      If TypeName(Value) = "String" Then Value = """" & Value & """" Else Value = Value * 1
      Formula = "iferror(match({value},{table}[{column}],0),0)"
      Formula = ReplaceStrings(Formula, Array("{value}", Value, "{table}", Table.Name, "{column}", ColumnName))
      Index = Evaluate(Formula)
      If Index > 0 Then Set GetListRow = Table.ListRows(Index)
    End Function

    Voici un exemple qui utilise cette fonction. Avec ma définition de la simplicité, je trouve que c'est simple à utiliser
    Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub TestListRow()
      Dim r As ListRow
     
      Set r = GetListRow(Range("tableau2").ListObject, "id", "U")
      If Not r Is Nothing Then
       MsgBox r.Range.Address
      Else
        MsgBox "L'enregistrement n'a pas été trouvé"
      End If
    End Sub

    Si demain je dois pointer vers un tableau nommé t_Vehicules contenant la colonne Immatriculation et chercher la ligne du véhicule immatriculé 1-ABC-123 (plaque belge ), j'utilise GetListRow comme ceci: Set r = getlistrow(range("t_Vehicules").ListObject,"Immatriculation","1-ABC-123"). La fonction générique, qui constitue en fait une "couche d'abstraction", est alors utilisée comme une fonction "native" du langage, et me permet de faire abstraction de Evaluate et de la façon dont elle fonctionne pour me renvoyer la valeur qui m'intéresse.


    En systématisant son approche et en se créant des codes génériques, on crée un code que je qualifierai personnellement et de façon subjective de plus simple à écrire, plus lisible, pérenne, évolutif, facilement testable, bref, plus professionnel
    Mis à jour 18/10/2019 à 11h27 par Pierre Fauconnier