Discussion :

[patricktoulon]

Salut.

Je commence à travailler avec les tableaux structurés en VBA. j'ai deja compris plein de petit chose mais d'autre sont encore un peu brouillon

entre autre
une fonction find pour choper la plage complete de la ligne d'un client en faisant un find sur la colonne "Nom"
y a t il plus propre que ca ?
et surtout je souhaiterais y ajouter un control de la cellule d'apres a droite pour le prenom au cas ou j'aurais des homonymes dans les noms ou prenoms de mes clients
mais quelque chose de propre : recommencer tout les 10 ans son app suffit amplement pour moi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub test()
    Dim rangeligne As Range, recherche$
    recherche = "entou"
    Set rangeligne = Find_listrow_range(Sheets("Liste_client"), "t_Contacts", "Nom", recherche)
    MsgBox rangeligne.Address
' la plage obtenue sera remplie par les textboxs du userform 
End Sub
'
Function Find_listrow_range(sh As Object, ByRef nomTableau$, ByRef nomcolonne$, ByVal recherche$) As Range
    Dim r As Range, lig&
    Set r = sh.Range(nomTableau & "[" & nomcolonne & "]").Find(recherche, lookat:=xlWhole)
    If Not r Is Nothing Then lig = r.Row - sh.Range(nomTableau).Row + 1
    Set Find_listrow_range = sh.Range(nomTableau).ListObject.ListRows(lig).Range
End Function

[Pierre Fauconnier]

Salut Patrick

Perso, je n'aime pas les Find (d'une façon générale et dans ce cas-ci en particulier). Je préfère un Evaluate (qui permet en plus une matricielle implicite)
Pour choper le listrow complet d'un tableau sur base de deux valeurs, je ferais ceci, sur base du tableau suivant qui s'appelle Tableau2.

Code :

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

1
2
3
4
5
6
Function getRow(FirstName As String, LastName As String) As ListRow
  Dim Index As Long
 
  Index = Evaluate("iferror(match(""" & FirstName & LastName & """,tableau2[prénom] & tableau2[nom],0),0)")
  If Index > 0 Then Set getRow = Range("tableau2").ListObject.ListRows(Index)
End Function

Si la ligne est trouvée, l'objet renvoyé est un listrow valide, sinon c'est Nothing. Par exemple, getrow("Jean","Naymard").Range.Address renverra $C$6:$E$6.

Si, par exemple, tu t'es trompé de nom et que ce n'est pas Naymard mais Naypleinlesbottes, tu peux utiliser getrow("Jean","Naymard").Range(2)="Naypleinlesbottes" pour modifier la valeur de D6 puisque là, tu te positionnes sur la deuxième cellule de la plage en te déplaçant en zigzag de gauche à droite et de haut en bas par rapport à la cellule supérieure gauche de la plage renvoyée => et hop, on revient un peu dans le sujet... )

[patricktoulon]

re
ok je prend c'est good .
merci pour la fonction

[patricktoulon]

Bonjour a tous


j'aurais encore diverses questions
la première étant comment obtenir la plage entière de deux colonnes(la syntaxe )
j'ai essayé plein de petite choses mais rien y fait je n'y arrive pas
exemple

Code :

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

 msgbox range("t_Contacts[Nom][Prénom]").address

merci pour les retrours

edit j'ai trouvé

Code :

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

MsgBox Range("t_Contacts[[Nom]:[Prénom]]").Address

rendez vous pour la question suivantes

[Pierre Fauconnier]

Salut Patrick

Cest comme en Excel

Code :

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

Range("tableau1[[colonne1]:[colonne2]]")

Saisis des plages en Excel pour voir la syntaxe. Cest la même en vba

[joe.levrai]

Bonjour,

étant un grand utilisateur de tableaux structurés, j'ai toujours pris l'habitude de travailler en le considérant comme un objet, son modèle étant très fourni
Je trouve cette façon de procéder moins contraignante en termes de rédaction

Code :

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

1
2
3
4
5
Sub toto()
Dim Tablo As ListObject
Set Tablo = Feuil1.ListObjects("tablo") 'Listobjects(1) s'il est unique ou le premier de la feuille
MsgBox Tablo.ListColumns("B").Range.Resize(, 2).Address 'on passe par le nom d'en-tête de la colonne pour la manipuler
End Sub

comme un TCD, tu peux naviguer dans la partie de l'objet qui t'intéresse, la colonne, juste les données de la colonne, les en-têtes, une ligne etc...

[patricktoulon]

merci a tous les deux j'avais trouvé entre temps dans une formule excel que j'ai adapté en vba ";" vers ":"

joe je vais regarder ca (en effet d'avoir l'object et toute ses propriété accessibles c'est un plus)c'est un peu ca qui me perd

question N°2

est ce la seule solution ou la plus propre pour obtenir la ligne d'entete

Code :

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

Set entete = Range("t_Contacts[#all]").Rows(1)

avec listobject je ne sais pas faire si tant est que cela soit possible (ce dont je doute car le range("nomtableau").address ou databodyrange donne le tableau sans entete)

[joe.levrai]

Code :

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

1
2
3
4
5
Sub toto()
Dim Tablo As ListObject
Set Tablo = Feuil1.ListObjects("tablo") 'Listobjects(1) s'il est unique ou le premier de la feuille
MsgBox Tablo.HeaderRowRange.Address
End Sub

[patricktoulon]

OK
merci joe
je stock

Code :

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

1
2
3
4
'obtenir le range de l'entete (version1 pat)
Sub test4()
MsgBox Range("t_Contacts[#all]").Rows(1).Address
End Sub

Code :

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

1
2
3
4
'obtenir le range de l'entete (version2 pat)
Sub test5()
MsgBox Range("t_Contacts").ListObject.HeaderRowRange.Address
End Sub

Code :

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

1
2
3
4
5
6
'obtenir le range de l'entete (version joe)
Sub test6()
Dim Tablo As ListObject
Set Tablo = Feuil1.ListObjects("tablo") 'Listobjects(1) s'il est unique ou le premier de la feuille
MsgBox Tablo.HeaderRowRange.Address
End Sub

3eme question:
on voit ici par ton exemple que tu precise le sheet parent
ma question est donc :est ce bien obligatoire car je viens de faire le test en etant sur un autre sheets et ca fonctionne toujours
sachant que l'on voit bien que le parent est bien declaré dans le referto dans le gestionnaire de nom


Oupss!! suis je bete tu passe par la collections "listobjects" donc forcement sous ta forme c'est oui

[patricktoulon]

re
allez une question peut etre un poil plus difficile
question 4:
je suis sur une cellule je veux connaitre le parent(tableau) et non le sheets parent comment fait -on?

[78chris]

Bonjour

Si la cellule est bien dans un tableau

Code :

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

Nom = ActiveCell.ListObject.Name

Donc un truc du genre

Code :

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

If Not ActiveCell.ListObject Is Nothing Then Nom = ActiveCell.ListObject.Name Else Nom = "Pas tableau"

[patricktoulon]

bonjour 78chris
Ok je stoke

Code :

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

1
2
3
4
5
6
'obtenir le parent tableau de l'activecell ou d'une cells en particulier
Sub test7()
    Dim montableau As ListObject
    Set montableau = ActiveCell.ListObject
    If Not montableau Is Nothing Then MsgBox montableau.Name
End Sub

merci

[Pierre Fauconnier]

Salut Patrick, Joe, Chris,

Bonjour,[...]

Code :

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

1
2
3
Sub toto()
Dim Tablo As ListObject
Set Tablo = Feuil1.ListObjects("tablo") 'Listobjects(1) s'il est unique ou le premier de la feuille

[...]

Perso, je m'affranchis de Feuil1. qui n'est pas nécessaire et qui te lie à la feuille. Si tu déplaces ton tableau sur une autre feuille, bardaf
Le code suivant est suffisant pour renvoyer le tableau puisque la "plage nommée" Tablo est unique dans le classeur...

Code :

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

set tablo = range("tablo").ListObject

Question 2: Ligne d'entête
Pas besoin de Set entete = Range("t_Contacts[#all]").Rows(1). Set entete = range("Tableau2[#Headers]") suffit pour récupérer la plage d'entête. Ca t'évite de passer par le ListObject pour récupérer la même plage.

Question 3: voir ma "remarque" sur le code de Joe ci-dessus.

Question 4: Chris a répondu...

[...]j'ai toujours pris l'habitude de travailler en le considérant comme un objet, son modèle étant très fourni [...]

Oui et non. je trouve aussi le modèle bien foutu, mais ils auraient bien poussé un peu plus loin. Perso, j'ai développé des classes qui complètent la panoplie et permettent des manipulations faciles des données d'un listobject

[patricktoulon]

re
merci pierre

j'adopte

Code :

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

Set entete = range("Tableau2[#Headers]")

j'avoue etre seduit par cette forme d'écriture plus que par l'object listboject elle est finalement assez explicit en soi et concise

oui moi aussi je pense qu'il faut s'affranchir du parent sheet sauf besoins cas echeant ou il nous faut parcourir la collection listobjects la bien evidement ca me parrait difficile de passer outre quoi que par les names on peut aussi

d'ailleurs je pense faire une fonction genrique en utilisant cette forme d'ecriture

[patricktoulon]

re
allez on change pas une equipe qui gagne
question 5:

comment obtenir le range de x colonnes non contigues

j'ai essayé plusieurs syntaxes mais je ne trouve pas si tant est que se soit possible

Code :

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

1
2
3
4
'obtenir le range de x  collones non contigues
Sub testx3()
    MsgBox Range("t_Contacts[[Nom],[address1],[Ville]]").Address
End Sub

[Pierre Fauconnier]

[...]
question 5:[...]

Pas possible en Excel avec les références structurées (tu repasses en références classiques lorsque tu sélectionnes deux colonnes non contiguës), donc à mon avis pas possible en VBA. Tu dois passer par Union

Code :

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

union(range("t_Contacts[id]"),range("t_contacts[Nom]"))

Cela dit, il faut bien considérer le tableau structuré comme une table et pas comme une plage classique améliorée. N'importe quelle plage classique n'a pas vocation à devenir un tableau structuré. Il faut qu'elle constitue un ensemble cohérent d'informations de même nature dont chaque ligne représente un enregistrement. En respectant cette finalité du tableau structuré, tu as rarement besoin de ce genre de manipulations. Associer deux colonnes dans un range pour en parcourir les cellules à peu d'intérêt dans une table de données, me semble-t-il.

[Pierre Fauconnier]

[...]quoi que par les names on peut aussi[...]

Ca peut paraître bizarre, mais les tableaux structurés ne font pas partie de la collection Names du classeur. Je ne vois pas d'autre issue qu'une boucle sur les listobjects d'une feuille insérée dans une boucle sur les feuilles, par exemple avec cette fonction qui renvoie un array des tableaux structurés d'un classeur (le classeur actif par défaut)

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 getTablesList(Optional wb As Workbook)
  Dim t As String
  Dim sh As Worksheet
  Dim lo As ListObject
 
  If wb Is Nothing Then Set wb = ActiveWorkbook
 
  For Each sh In wb.Worksheets
    For Each lo In sh.ListObjects
      t = t & lo.Name & ";"
    Next
  Next
  t = Left(t, Len(t) - 1)
  getTablesList = Split(t, ";")
End Function

[boisgontierjacques]

Bonjour,

Pour les champs discontinus, il y a aussi cette écriture:

Code :

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

1
2
3
 Set Rng = Range("Tableau1[nom], Tableau1[ville]")
 MsgBox Rng.Address
 MsgBox Rng.Areas(2)(1)

Boisgontier

[patricktoulon]

bonjour a tous
OK
@pierre j'ai testé et éffectivement pas de name c'est bizzare ce truc je m'y attendais pas a celle la

@boisgontiersjacques je prends
merci

[patricktoulon]

@Pierre
en reponse a ton post 16
et bien si toute les semaine je recupere certaines colonnes de mon tableaux de consomation de produit (bois,resine,polymere,etc) pour en faire un sheets recap et archivage
ca me permet d'avoir un apercu direct semaine apres semaines des (pertes chutes de bois et resines pour x portes fabriquées)
pour le bois je peux diagnostiquer une avarie machine par exemple
pour les resines je met des coups de battes a mes apprentis

question6:
quelle est la solution la plus propre pour recupérer la plage visible dans un tableau structuré je dis bien la plus propre on a vu ca dans une discussion precedente mais autant la ramener ici

edit:
pensez vous qu'il soit plus pratique voir orthodoxe de travailler avec les objects "ListRow" plutot que range et specialcells(xlvisible)
dans ce genre la
'obtenir un tableau d'object "ListRow" visible ou invisible (2d argument)

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
'obtenir un tableau d'object "ListRow" visible ou invisible
Sub test2()
    Dim VisibleListRows
    VisibleListRows = GetListrows_visible(Range("t_Contacts"), False)'fase pour les listrow invisibles ;true pour les listrows visibles
    For i = 1 To UBound(VisibleListRows)
        Debug.Print VisibleListRows(i).Range.Address
    Next
End Sub
'
Function GetListrows_visible(ByRef rangetableau, ByRef visible_ou_pas As Boolean)
    Dim tabl() As ListRow, Lrow As ListRow
    For Each Lrow In rangetableau.ListObject.listrows
        If Lrow.Range.EntireRow.Hidden <> visible_ou_pas Then i = i + 1: ReDim Preserve tabl(1 To i): Set tabl(i) = Lrow
    Next
    GetListrows_visible = tabl
End Function