Discussion :

[Pierre Fauconnier]

Et si vraiment on veut travailler avec une seule procédure pour l'update et le create, par exemple en fonction du fait que activecell est dans le tableau ou pas, on peut aussi. Ca peut être plus souple.

Toujours dans l'idée de décomposer le code en procédures/Fonctions qui n'ont qu'un seul objectif, je propose alors une fonction pour récupérer la listrow qui contient activecell, et la procédure mixte Create/Update choisit la listrow en fonction de activecell. Et on ne touche pas du tout ni au userform ni même à la fonction qui le manipule. C'est l'énorme avantage du découpage en petits blocs de code.

Voici la fonction qui récupère la ligne du tableau structuré de la cellule passée en argument (idéalement, on la générise en la sécurisant par un test if not rng.listobject is Nothing then… puis on teste dans le code appelant que la fonction a bien renvoyé un objet ListRow et pas Nothing).

Code :

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

1
2
3
4
5
6
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function

Voici la procédure mixte

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set lr = getRowFromCell(ActiveCell) ' Modification de la ligne courante
  Else
    Set lr = getNewRow("t_Contacts") ' Nouvelle ligne du tableau (attention, elle est physiquement créée)
  End If
  Data = lr.Range.Value
  If UpdateContact(Data) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

On a alors un code très propre, notamment parce qu'il s'appuie sur les listobject et listrow et qui permet de limiter les modifs au minimum lors d'une modification structurelle du classeur. Je remets tout le code ici, ainsi que celui du userform contenant tboID, tboFirstName et tboLastName comme textboxes, ainsi que btnValidate et btnCancel comme commandbuttons, tel que celui-ci.



Code du userform

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

Code d'un module standard

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
 
Function getNewRow(TableName As String) As ListRow
  Set getNewRow = Range(TableName).ListObject.ListRows.Add()
End Function
 
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function
 
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set lr = getRowFromCell(ActiveCell)
  Else
    Set lr = getNewRow("t_Contacts")
  End If
  Data = lr.Range.Value
  If UpdateContact(Data) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

[Pierre Fauconnier]

On digresse un peu, mais bon

Sur base d'un tableau nommé t_Contacts:

• Première cellule de la plage de données du tableau: range("t_Contacts")(1).address
• Première cellule de la plage de données du tableau (autre méthode): range("t_Contacts").ListObject.DataBodyRange(1)
• Première cellule du tableau (autrement dit, cellule d'entête de la première colonne dudit tableau): range("t_Contacts[#All]")(1).address
• Première cellule du tableau (autrement dit, cellule d'entête de la première colonne du tableau) (autre méthode): range("t_Contacts").ListObject.Range(1)

Mais attention. Programmer avec les tableaux structurés, ce n'est pas juste transposer les anciennes manières de procéder. Par exemple, plus besoin de END(XLUP) pour déterminer la dernière ligne d'un tableau, tu peux récupérer la dernière ligne directement, soit en objet ListRow, soit en objet Range, et on crée (et récupère) une nouvelle ligne plus facilement:

• Dernière ListRow: range("t_Contacts").ListObject.ListRows(range("t_Contacts").ListObject.ListRows.Count)
• Plage de la dernière ListRow: range("t_Contacts").ListObject.ListRows(range("t_Contacts").ListObject.ListRows.Count).Range
• Nouvelle ligne du tableau, créée physiquement sur la feuille: Set lr = range("t_Contacts").ListObject.ListRows.add() (avec Dim lr As ListRow)

Evidemment, c'est plus simple de travailler avec des objets intermédiaires, au moins le listobject

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Demo()
  Dim lo As ListObject
  Dim LastRow As ListRow
  Dim LastRowRange As Range
  Dim NewRow As ListRow
 
  If Not Range("t_Contacts").ListObject Is Nothing Then
    Set lo = Range("t_Contacts").ListObject
    Set LastRow = lo.ListRows(lo.ListRows.Count)
    Set LastRowRange = LastRow.Range
    Set NewRow = lo.ListRows.Add()
  End If
End Sub

PS: Je propose que l'on ne pollue pas trop la discussion en cours…

[Qwazerty]

Salut Pierre

Pour rester dans la digression

On digresse un peu, mais bon

Sur base d'un tableau nommé t_Contacts:

• Première cellule de la plage de données du tableau: range("t_Contacts")(1).address
• Première cellule de la plage de données du tableau (autre méthode): range("t_Contacts").ListObject.DataBodyRange(1)
• Première cellule du tableau (autrement dit, cellule d'entête de la première colonne dudit tableau): range("t_Contacts[#All]")(1).address
• Première cellule du tableau (autrement dit, cellule d'entête de la première colonne du tableau) (autre méthode): range("t_Contacts").ListObject.Range(1)

Déjà, je viens de découvrir la balise "codeinline"... le nombre de fois où j'ai pesté en mettant du code et en ayant des gros placards au milieu du texte... Merci

Ceci dit, ça n'est pas l'objet de mon message.

Tu sembles préférer la forme range("t_Contacts").ListObject à Feuil1.ListObject("t_Contacts").
Étant un farouche partisan dans le fait de toujours préciser le classeur et la feuille sur laquelle on travaille, j'avoue que cette écriture me laisse perplexe. Y-a-t-il une raison particulière à l'utilisation de cette façon de faire?

Par contre, je n'avais pas remarqué (avant un de tes autres poste utilisant les ListObject) la possibilité à partir d'un range de pointer le listObject.
Je trouve que c'est une possibilité intéressante, d'autant qu'il est possible de remonter au tableau à partir de n'importe quelle cellule incluse dans celui-ci Feuil1.range("A8").ListObject (Juste pour le plaisir d'utiliser codeinline [ça manque un bouton dans l'éditeur par contre!]).
Je n'ai pas d'exemple d'utilisation concrété en tête mais je suis sûr que ça peut servir! J'essaierais de le placer dans un code pour le fun ^^.

Ton dernier message m'a aussi interpelé, tu ne sembles pas partisan de l'utilisation des noms de champs lors de l'utilisation en VBA du Tableau Structuré, chose que je fais plus que régulièrement lors de réponse sur le forum, notamment en utilisant le tag des contrôles pour y mémoriser le nom du champs correspondant.
Sur ce dernier point, à la lecture de ton antépénultième réponse... je comprends que je n'isole pas assez mes userforms en pratiquant ainsi :p Ça aussi j'essaierais d'y penser lors du prochain post traitant de userform (Je ne sais pas s'il y aura assez de place dans am tête )

++
Qwaz

[Pierre Fauconnier]

Salut Quaz

[...]
Tu sembles préférer la forme range("t_Contacts").ListObject à Feuil1.ListObject("t_Contacts"). Étant un farouche partisan dans le fait de toujours préciser le classeur et la feuille sur laquelle on travaille, j'avoue que cette écriture me laisse perplexe. Y-a-t-il une raison particulière à l'utilisation de cette façon de faire?

Dans la mesure où le nom du tableau est unique dans le classeur, je préfère ne pas passer par la feuille. Si le tableau est déplacé sur une autre feuille, mon code continue à fonctionner

[...]
Par contre, je n'avais pas remarqué (avant un de tes autres poste utilisant les ListObject) la possibilité à partir d'un range de pointer le listObject.
Je trouve que c'est une possibilité intéressante, d'autant qu'il est possible de remonter au tableau à partir de n'importe quelle cellule incluse dans celui-ci Feuil1.range("A8").ListObject (Juste pour le plaisir d'utiliser codeinline [ça manque un bouton dans l'éditeur par contre!]).
Je n'ai pas d'exemple d'utilisation concrété en tête mais je suis sûr que ça peut servir! J'essaierais de le placer dans un code pour le fun ^^.

J'illustre le cas où c'est intéressant de faire ainsi, dans la fonction getRowFromCell(rng as Range), utilisé avec Intersect pour trouver le ListRow de la cellule active lorsque cell-ci se trouve dans le tableau, avec une simple arithmétique pour trouver l'index du ListRow souhaité

Code :

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

1
2
3
4
5
6
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function

[...]
Ton dernier message m'a aussi interpelé, tu ne sembles pas partisan de l'utilisation des noms de champs lors de l'utilisation en VBA du Tableau Structuré, chose que je fais plus que régulièrement lors de réponse sur le forum, notamment en utilisant le tag des contrôles pour y mémoriser le nom du champs correspondant.


Sur ce dernier point, à la lecture de ton antépénultième réponse... je comprends que je n'isole pas assez mes userforms en pratiquant ainsi :p Ça aussi j'essaierais d'y penser lors du prochain post traitant de userform (Je ne sais pas s'il y aura assez de place dans am tête )

++
Qwaz

En effet, j'essaie de pas trop coupler le userform avec la structure du tableau, histoire d'avoir le moins possible à modifier lorsque l'un ou l'autre change. La fonction qui alimente et récupère les données du userform ainsi que le userform lui-même pourraient être utilisés dans une autre configuration (Word, un autre classeur,...) et lier le userform à un listrow verrouille son utilisation à Excel. Comme je l'ai précisé à Patrick, si la source change, j'ai plus de souplesse avec un array qu'aver le listrow. Mais je pousse peut-être le bouchon un peu loin.

Pour autant, dans le cadre d'un framework où il faudrait travailler avec plusieurs userforms liés à des tableaux structurés, on pourrait passer le listrow à la fonction qui gère le userform en se servant des tags comme tu le préconises et en récupérant le nom de la colonne de chaque ligne du listrow, avec un jeu d'échange de données entre listrow et userform qui pourrait être générique et s'appliquer à n'importe quelle paire listrow/usrform .

Mais pour ma part, je préfèrerais probablement une autre solution qui consisterait à passer deux tableaux d'array, l'un pour les données et un autre de même structure pour les noms des contrôles du userform. Ca rendrait plus générique tout en ne liant pas aux listrow et donc à la structure du classeur.

Quelque chose du genre:

Fonction d'un module standard pour échanger les données avec le userform qui reçoit les données et le tableau des contrôles

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function UpdateContact(ByRef Data, ByVal Controls) As Long
  Dim Counter As Long
 
  Unload usfContact
  With usfContact
    For Counter = 1 To UBound(Data, 2)
      .Controls(Controls(Counter)).Value = Data(1, Counter)
    Next Counter
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      For Counter = 1 To UBound(Data, 2)
        Data(1, Counter) = .Controls(Controls(Counter)).Value
      Next Counter
    End If
  End With
  Unload usfContact
End Function

La procédure de création/mise à jour devient alors celle-ci, avec l'ajout de la création du tableau. J'ai choisi d'initialiser Controls avec un VBA.array créé en dur et qui, à cause de VBA.Array, est de base 0 quel que soit le Option Base du module. Dès lors, pour coller avec le compteur du tableau Data qui lui est de base 1 quel que soit le Option Base, je lui met 0 comme premier élement.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data, Controls
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set lr = getRowFromCell(ActiveCell)
  Else
    Set lr = getNewRow("t_Contacts")
  End If
  Data = lr.Range.Value
  Controls = VBA.Array(0, "tboID", "tboFirstName", "tboLastName")
  If UpdateContact(Data, Controls) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

Pour moi, je me répète , sauf classeur avec beaucoup de userform et donc où le couplage faible a moins d'importance, je préfère cela à l'utilisation des TAG des contrôles, surtout que ceux-ci pourraient devoir être utilisés pour autre chose. On serait alors obliger d'utiliser des tag multivalués avec un split sur un séparateur, mais je pense que ça devient aussi lourd que de créer l'array avec les noms des contrôles...

en fait, c'est une question de choix d'architecture

[Qwazerty]

Dans la mesure où le nom du tableau est unique dans le classeur, je préfère ne pas passer par la feuille. Si le tableau est déplacé sur une autre feuille, mon code continue à fonctionner

Ha oui jolie, ça fait en fait appelle à un nom de cellule et les tableau sont déclarés au niveau classeur... je retiens

J'illustre le cas où c'est intéressant de faire ainsi, dans la fonction getRowFromCell(rng as Range), utilisé avec Intersect pour trouver le ListRow de la cellule active lorsque cell-ci se trouve dans le tableau, avec une simple arithmétique pour trouver l'index du ListRow souhaité

Code :

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

1
2
3
4
5
6
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function

Pratique en effet.

En effet, j'essaie de pas trop coupler le userform avec la structure du tableau, histoire d'avoir le moins possible à modifier lorsque l'un ou l'autre change. La fonction qui alimente et récupère les données du userform ainsi que le userform lui-même pourraient être utilisés dans une autre configuration (Word, un autre classeur,...) et lier le userform à un listrow verrouille son utilisation à Excel. Comme je l'ai précisé à Patrick, si la source change, j'ai plus de souplesse avec un array qu'aver le listrow. Mais je pousse peut-être le bouchon un peu loin.

Pour autant, dans le cadre d'un framework où il faudrait travailler avec plusieurs userforms liés à des tableaux structurés, on pourrait passer le listrow à la fonction qui gère le userform en se servant des tags comme tu le préconises et en récupérant le nom de la colonne de chaque ligne du listrow, avec un jeu d'échange de données entre listrow et userform qui pourrait être générique et s'appliquer à n'importe quelle paire listrow/usrform .

Mais pour ma part, je préfèrerais probablement une autre solution qui consisterait à passer deux tableaux d'array, l'un pour les données et un autre de même structure pour les noms des contrôles du userform. Ca rendrait plus générique tout en ne liant pas aux listrow et donc à la structure du classeur.

Quelque chose du genre:

Pour moi, je me répète , sauf classeur avec beaucoup de userform et donc où le couplage faible a moins d'importance, je préfère cela à l'utilisation des TAG des contrôles, surtout que ceux-ci pourraient devoir être utilisés pour autre chose. On serait alors obliger d'utiliser des tag multivalués avec un split sur un séparateur, mais je pense que ça devient aussi lourd que de créer l'array avec les noms des contrôles...

en fait, c'est une question de choix d'architecture

Bon prochain UserForm, je regarde plus précisement, j'ai lu les codes, je les comprends dans l'enssemble mais il faudra des mises en pratiques pour bien en comprendre les avantages/inconvéniants.

Evidemment, vu mon aversion pour les Exit de toute sorte, on sort proprement de la boucle dès que le contrôle est trouvé

Rien que ça
Mes codes doivent te hérisser les poiles car j'utilise très souvent les Exit For, que je trouve au demeurant fort pratique (sans vouloir faire de jeu de mot). J'utilise même parfois des Goto pour en sortir !

En tout cas, merci pour ces précieux conseils

A bientôt
++
Qwaz

[patricktoulon]

re
merci Pierre
je vais deja m'entrainer avec ca pour comprendre le fonctionnement , il est temps que je m'y mette a ces satanés tableaux
j'attend ton tuto impatiement

[Pierre Fauconnier]

[…]Rien que ça
Mes codes doivent te hérisser les poiles car j'utilise très souvent les Exit For, que je trouve au demeurant fort pratique[…]

J'en parlais dans un billet pas trop récent, et j'ai complété ma réflexion par ce billet plus philosophique…

Hum… Digression, quand tu nous tiens…

[Pierre Fauconnier]

Re Quaz...

Sur base des tags, et toujours sans toucher au code du userform, on pourrait imaginer une fonction dans le module standard qui reçoit le userform et le nom du tag et qui renvoie le contrôle correspondant. Evidemment, vu mon aversion pour les Exit de toute sorte, on sort proprement de la boucle dès que le contrôle est trouvé

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Function getControlByTag(usf As MSForms.UserForm, lr As ListRow, index As Long) As MSForms.Control
  Dim Counter As Long
  Dim Found As Boolean
  Dim Tag As String
 
  Tag = lr.Parent.ListColumns(index).Name
  Counter = 0
  Do While Counter < usf.Controls.Count And Not Found
    If StrComp(usf.Controls(Counter).Tag, Tag, vbTextCompare) = 0 Then
      Set getControlByTag = usf.Controls(Counter)
      Found = True
    End If
    Counter = Counter + 1
  Loop
End Function

La fonction d'échange recevrait alors le listrow à la place de l'array et récupèrerait le contrôle à valoriser où dont il faut récupérer la valeur via la fonction précédente (on utilisera ici le nom decolonen dont tu parles dans ton message).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Function UpdateContact(ByRef lr As ListRow) As Long
  Dim Counter As Long
  Dim Control As MSForms.Control
 
  Unload usfContact
  With usfContact
    For Counter = 1 To lr.Range.Count
      Set Control = getControlByTag(usfContact, lr, Counter)
      If Not Control Is Nothing Then Control.Value = lr.Range(Counter).Value
    Next Counter
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      For Counter = 1 To lr.Range.Count
        Set Control = getControlByTag(usfContact, lr, Counter)
        If Not Control Is Nothing Then lr.Range(Counter).Value = Control.Value
      Next Counter
    End If
  End With
  Unload usfContact
End Function

La procédure qui pilote le tout passerait alors le listrow et plus un array.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub CreateUpdateContact()
  Dim ID As Long
  Dim Lr As ListRow
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set Lr = getRowFromCell(ActiveCell)
  Else
    Set Lr = getNewRow("t_Contacts")
  End If
  If UpdateContact(Lr) = -1 Then
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

On notera cependant que cela déporte l'inscription des données dans le tableau dans la fonction d'échange, puisque l'on manipule directement le listrow. Perso, je ne suis pas chaud pour cette méthode:
telle qu'écrite ici:

• elle oblige à ce que chaque contrôle à valoriser soit tagué précisément comme la colonne associée (en cas de changement de nom de colonne, on doit modifier le userform);
• elle oblige à ce que le contrôle possède les propriétés Value et Tag;
• elle oblige peu ou prou à créer une fonction qui récupère le contrôle sur base de son tag, invalidant l'utilisation du tag pour autre chose (sauf à multivalué le tag et à récupérer ses valeurs avec un split);
• elle couple fortement le userfom et Excel.

Voilà. Je préfère la solution que j'ai proposée avec l'array Data, dans la mesure où il est relativement rare d'avoir des centaines de valeurs à échanger entre le tableau et le userform...

Mais les solutions proposées ici sur base des tes remarques offrent un autre choix et ouvrent peut-être, par l'exemple, à des utilisations spécifiques et des généralisations de code qui peuvent s'avérer utiles