Discussion :

[pijaku]

Bonjour,

A proprement parler, il ne s'agit pas d'un vrai problème, mon code fonctionnant plutôt bien.
Je voulais juste vos avis éclairés à propos de :
> cette utilisation des tableaux structurés (pas vraiment structurée)
> les éventuels manques de "protection" dans le code (risques d'erreurs que je n'aurais pas entrevu).

1- Les 2 tables de données :

La table nommée TableauVilles qui reprend, en entête, des noms d'îles, et en colonnes, les villes correspondantes :



La table nommée TableauRues qui reprend, en entête, toutes les villes du 1er tableau structuré et, en colonnes, les noms des rues (comme vous pouvez le constater, j'ai masqué moultes colonnes) :



2- L'Userform, son module et leurs codes :

Un simple Userform (UserForm1) avec dessus, 3 Combobox : Combobox1 = îles; Combobox2 = Villes; Combobox3 = rues
Un Module avec ce code :

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
Option Explicit
 
Public Sub Appel_Userform()
    With UserForm1
        .ComboBox1.Column = Range("TableauVilles[#Headers]").Value
        .Show
    End With
End Sub
 
Public Sub Change(CbSource As MSForms.ComboBox, CbDest As MSForms.ComboBox, nomTablo As String)
    If CbSource.Value <> vbNullString Then
        With CbDest
            .Clear
            .List = getArrayFromRange(Range(nomTablo & "[" & CbSource.Value & "]"))
        End With
    End If
End Sub
 
Private Function getArrayFromRange(Item As Range)
'volée à Pierre Fauconnier
    If WorksheetFunction.CountA(Item) > 0 Then
        If Item.Cells.Count = 1 Then
            Dim t(1, 1)
            t(1, 1) = Item.Value
            getArrayFromRange = t
        Else
            getArrayFromRange = Item.SpecialCells(xlCellTypeConstants).Value
        End If
    Else
        getArrayFromRange = Array(vbNullString)
    End If
End Function

Le code de l'Userform :

Code :

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

1
2
3
4
5
6
7
8
9
Option Explicit
 
Private Sub ComboBox1_Change()
    Change ComboBox1, ComboBox2, "TableauVilles"
End Sub
 
Private Sub ComboBox2_Change()
    Change ComboBox2, ComboBox3, "TableauRues"
End Sub

Je réitère donc mes deux questions :
> trouvez-vous des failles dans les procédures de mon Module ?,
> Que dire de l'utilisation de tableaux structurés avec des colonnes non remplies, voire vides?

Pour le coup cela entraîne une troisième question : n'y a t'il pas mieux à faire?

Pour ceux qui le souhaitent, je peux laisser traîner un fichier exemple : Demo Combo Cascade.xlsm

[mfoxy]

Bonjour,

Je vai suivre avec intérêt ton poste, je recherchais justement l utilisation des combobox en cascade avec les tableaux, pour migration.

Un petit souci pour moi qui ne devrait pas être trop compliqué à résoudre, les combo ne sont pas trié par ordre alphabétique, ce qui est selon plus user friendly.

Il faudrait peut-être ajouté une fonction de tri ?

Bat

[OrDonc]

Bonsoir …

Chez moi, cela coince avec un tableaux à un seul élément comme Ici en premier et seul item de la colonne Le Tampon.
Je passerai, sans la fonction de Pierre Fauconnier, par

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Public Sub Change(CbSource As MSForms.ComboBox, CbDest As MSForms.ComboBox, nomTablo As String)
    If CbSource.Value <> vbNullString Then
        CbDest.Clear
        With Range(nomTablo & "[" & CbSource.Value & "]")
            If Application.CountA(.Value) > 0 Then
                CbDest.List = .Value
            Else
                CbDest.Value = CbSource.Value
            End If
        End With
    End If
End Sub

[Pierre Fauconnier]

Salut.

Chez moi, cela coince avec un tableaux à un seul élément comme Ici en premier et seul item de la colonne Le Tampon[...]

Je suis d'accord sur le problème du tableau monocellule. On ne relève pas assez ce problème sur le forum. Par contre, je n'aime pas sa mise en place dans l'événement, et je ne comprends pas pourquoi tu te passes d'une fonction générique pour réaliser cela. Devoir pisser du code à chaque fois que tu vas devoir gérer ce problème de cellule unique est un non-sens contre-productif. Le VBA te permet de construire des fonctions qui vont sécuriser ton code et te faire gagner du temps. C'est absurde de s'en priver et de réinventer la roue à chaque fois. Tu crées la fonction, sur base de mon code ou d'un autre, bien qu'il n'y ait pas 36 manières de coder cela, puis tu la stockes dans ton module d'outils génériques (perso, j'ai un module Tools pour tout ce qui est VBA, un module xlTools dans lequel je range mes outils génériques propres à Excel, et c'est évidemment là que je stockerais cette fonction, un appTools pour tout ce qui est générique à l'application que je crée, deux classes xlTable et xlRow pour gérer les tableaux structurés...). C'est en systématisant son approche que l'on programme de façon professionnelle, de manière à être rapide et efficace (donc coût moindre pour le client ou bénéfice plus grand pour le prestataire) tout en utilisant des outils sécurisés car génériques, testés et retestés... Programmer de cette façon est bien plus confortable que de bidouiller des solutions à chaque besoin.


Réponse à la question initiale

Perso, je préfère une architecture "base de données": Iles, Villes, Rues en trois tableaux, plutôt qu'un grand tableau pour les villes avec les iles en colonnes et des lignes vides et un autre pour les rues avec les villes en colonnes et des lignes vides. Si tu veux trier par exemple les villes d'une île par ordre croissant, les autres colonnes vont bouger également, idem pour les rues.

N'avoir que trois tableaux permet également des listes de validation en Excel que l'on ne doit pas modifier ou recréer lors de l'ajout d'îles, de villes ou de rues (voir dans le fichier joint les formules nommées et les listes de validation en L1, L2 et L3). Par contre, cette architecture oblige à ce que les données soient triées, sinon, tant dans les validations Excel que dans les combobox, ça sera rock 'n' roll . Si l'on ne veut pas la contrainte du tri, il faudra passer par des arrays (mais c'est contreproductif puisqu'Excel propose des solutions natives, et les "codes" proposés sur base de dictionary sont pour moi des aberrations proposées par des gens qui n'ont pas compris qu'ils travaillaient avec Excel et qui se gaussent de proposer des solutions de programmeurs qu'eux seuls comprennent, quoique).

Attention également que si deux villes d'îles différentes portent le même nom, ça coincera pour les rues. Normalement, on devrait travailler avec des ID uniques, comme pour une "vraie" base de données (Access ou SQL, par exemple). Le problème se résout assez aisément en ajoutant par exemple une colonne d'ID. A partir du moment où l'on utilise Excel pour ce pour quoi il n'est pas fait au départ, aucune solution ne résoudra parfaitement le problème

Voici perso comment je réaliserais la chose. Ca ne fait pas moins de code que le tien, c'est juste une autre architecture, à prendre ou à laisser, plus dans l'esprit "Access" puisque l'on gère des données. Dans le fichier joint, j'ai tout mis sur la même feuille, mais normalement, il ne devrait y avoir qu'un tableau par feuille, à mon idée.




Le code du 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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
Option Explicit
 
Sub ShowForm()
  sortIslands
  SortCities
  SortStreets
  With UserForm2
    .cboIslands.List = getIslands
    .Show
  End With
End Sub
 
Sub sortIslands()
  With Range("t_Iles").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Range("t_Iles[Ile]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .Apply
  End With
End Sub
 
Sub SortCities()
  With Range("t_Villes").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Range("t_Villes[Ile]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add Range("t_Villes[Ville]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .Apply
  End With
End Sub
 
Sub SortStreets()
  With Range("t_Rues").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Range("t_Rues[Ville]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add Range("t_Rues[Rue]"), SortOn:=xlSortOnValues, Order:=xlAscending
    .Apply
  End With
End Sub
 
Function getIslands()
  getIslands = getArrayFromRange(Range("t_Iles[Ile]"))
End Function
 
Function getCities(Dpt As String)
  Dim Pos1 As Long
  Dim CountOf As Long
 
  Pos1 = Evaluate("match(""" & Dpt & """,t_Villes[Ile],0)")
  CountOf = Evaluate("countifs(t_Villes[Ile],""" & Dpt & """)")
  getCities = getArrayFromRange(Range("t_villes[Ville]")(Pos1).Resize(CountOf))
End Function
 
Function getStreets(City As String)
  Dim Pos1 As Long
  Dim CountOf As Long
 
  Pos1 = Evaluate("match(""" & City & """,t_Rues[Ville],0)")
  CountOf = Evaluate("countifs(t_Rues[Ville],""" & City & """)")
  getStreets = getArrayFromRange(Range("t_Rues[Rue]")(Pos1).Resize(CountOf))
End Function
 
Function getArrayFromRange(Value As Range)
  If Value.Cells.Count = 1 Then
    Dim Item(1 To 1)
    Item(1) = Value.Value
    getArrayFromRange = Item
  Else
    getArrayFromRange = Value.Value
  End If
End Function

[pijaku]

Bonjour tout le monde,

Je suis d'accord avec vous sur le test à réaliser sur la colonne (remplie, non remplie ou avec 1 unique élément).
C'est ce que j'essayais de faire...

Mes conclusions :

1- On ne dispose pas d'un code "universel" pour la fonction GetArrayFromRange.
Selon si la source est une "Range pur" ou s'il s'agit d'une colonne d'un ListObject, la fonction sera différente...
Pas top cette histoire. J'étais persuadé que cela ne changerait rien...

La solution que j'entrevois passe par ce type de code (test si le Range passé en argument possède un ListObject) :

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
Private Function getArrayFromRange(Item As Range)
'volée à Pierre Fauconnier
    If WorksheetFunction.CountA(Item) > 0 Then
        If WorksheetFunction.CountA(Item) = 1 Then
            If Item.ListObject Is Nothing Then
                Dim t(1, 1)
                t(1, 1) = Item.Cells(1).Value
                getArrayFromRange = t
            Else
                getArrayFromRange = Array(Item.Cells(1).Value)
            End If
        Else
            getArrayFromRange = Item.SpecialCells(xlCellTypeConstants).Value
        End If
    Else
        getArrayFromRange = Array(vbNullString)
    End If
End Function

Cela vous semble fiable?

2- Oui, Pierre, ta solution présente plusieurs avantages, notamment le tri.
Mais, l'alourdissement du fichier dû aux répétitions des noms de ville, etc, est-il compensé par ces avantages?

J'aime bien l'idée de traiter des données selon le principe d'un SGBD, mais le jeu en vaut-il la chandelle?
Ici, je ne parle que d'alimenter des combobox en cascade.

Mon utilisation, pas du tout orthodoxe, des tableaux structurés, semble, elle, compensée pleinement par le "gain de confort" du code.
S'il le faut, le tri de LA colonne (unique) qui composera la CobomBox peut-être fait après la fonction getArrayFromRange.
Cette fonction renvoyant une variable tableau, il est aisé et très rapide de la trier. [idem pour une Combobox multicolonnes]

Exemple avec une légère adaptation de la fonction de Jacques Boisgontier :

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
Private Function getArrayFromRange(Item As Range, Optional Tri As Boolean = True)
'volée à Pierre Fauconnier
    If WorksheetFunction.CountA(Item) > 0 Then
        If WorksheetFunction.CountA(Item) = 1 Then
            If Item.ListObject Is Nothing Then
                Dim t(1, 1)
                t(1, 1) = Item.Cells(1).Value
                getArrayFromRange = t
            Else
                getArrayFromRange = Array(Item.Cells(1).Value)
            End If
        Else
            'Dim v
            getArrayFromRange = Item.SpecialCells(xlCellTypeConstants).Value
            If Tri Then TriTab getArrayFromRange, LBound(getArrayFromRange), UBound(getArrayFromRange)
            'getArrayFromRange = v
        End If
    Else
        getArrayFromRange = Array(vbNullString)
    End If
End Function
Private Sub TriTab(ByRef Tableau As Variant, Mini As Long, Maxi As Long)
    Dim i As Long, j As Long, Pivot As Variant, TEMP As Variant
        On Error Resume Next
        i = Mini: j = Maxi
        Pivot = Tableau((Mini + Maxi) \ 2, 1)
        While i <= j
            While Tableau(i, 1) < Pivot And i < Maxi: i = i + 1: Wend
            While Pivot < Tableau(j, 1) And j > Mini: j = j - 1: Wend
            If i <= j Then
                TEMP = Tableau(i, 1)
                Tableau(i, 1) = Tableau(j, 1)
                Tableau(j, 1) = TEMP
                i = i + 1: j = j - 1
            End If
        Wend
        If (Mini < j) Then Call TriTab(Tableau, Mini, j)
        If (i < Maxi) Then Call TriTab(Tableau, i, Maxi)
    End Sub

3- En fait, cette question m'est "survenue" parce que je suis en train d'actualiser mes "vieux" codes.
Comme Pierre le répète, utilisons les outils d'aujourd'hui. 2007 c'est pas récent, et, donc, il est temps pour moi d'adapter.

Cela m'a permis de me rendre compte d'une "boulette" (je n'ai pas d'autre nom)...
Lorsque, le nom en entête d'une colonne de votre ListObject comporte une apostrophe : ', le code plante :

Erreur d'exécution '1004':
La méthode 'Range' de l'objet '_Global' a échoué

ou encore :

Erreur d'exécution '1004':
Erreur définie par l'application ou par l'objet

ou encore :

Erreur d'exécution '1004':
Objet spécifié introuvable

Exemple :

Code :

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

? Range("TableauRues[L'Étang-Salé]").Address

Est-ce contournable sans modifier les données?

[Pierre Fauconnier]

[...]
1- On ne dispose pas d'un code "universel" pour la fonction GetArrayFromRange.
Selon si la source est une "Range pur" ou s'il s'agit d'une colonne d'un ListObject, la fonction sera différente...
Pas top cette histoire.[...]

Je ne comprends pas très bien ton propos. La fonction GetArrayFromRange accepte une plage de données, qu'elle soit issue d'un listobject ou pas ne change rien. Dès lors, il n'est pas question de lui passer une listobject, mais bien une plage, éventuellement issue de ce listobject... ^^. Mais elle ne peut pas recevoir un listobject et en déduire un objet range, puisqu'un listobject contient bien plus qu'un seul objet Range...

Le ListObject contient plusieurs objets de type Range:

• Range lui-même, qui correspond à Range("Tableau1[#All]");
• DataBodyRange, qui correspond à Range("Tableau1"), pour autant que le tableau ne soit pas vide (= sans lignes) sinon plantage;
• Autant de ListColumn.DataBodyRange qu'il y a de colonnes, qui correspondent aux range("Tableau1[Colonne1]") etc;
• Autant de ListColumn.Range qu'il y a de colonnes, qui correspondent aux Range("Tableau1[[#All],[Colonne1]]"), etc;
• autant de ListRow.Range qui correspondent à une ligne précise du tableau;
• Les range d'entête et de total (si elle sont affichées).

La fonction GetArrayFromRange peut recevoir n'importe quelle range, issue ou non du listobject, et si elle ne contient qu'une cellule, elle renvoie un tableau monoligne et monocolonne... Elle me semble donc générique, que tu travailles avec un listobject ou non.

Je n'ai pas décortiqué le reste

[Pierre Fauconnier]

[...]Cela m'a permis de me rendre compte d'une "boulette" (je n'ai pas d'autre nom)...
Lorsque, le nom en entête d'une colonne de votre ListObject comporte une apostrophe[...]

Tu dois "l'échapper" en la doublant (comme quelques autres caractères)



A nouveau (j'enfonce le clou ,) ), des fonctions génériques permettent d'éviter de traiter des problèmes de ce genre au cas par cas. Cela dit, je ne rencontre que très rarement des problèmes de ce genre dans mes codes car j'évite les apostrophes et autres caractères problématiques dans les noms de mes colonnes. C'est l'organisation de tes tables avec des noms de villes comme noms de colonne qui amènent ce problème. Dans des tableaux architecturés tels que je les préconise, on ne rencontre pas ce genre de problème car la donnée contenant une apostrophe est bien une donnée et non un nom de colonne. Elle se retrouve donc dans les lignes de données et pas dans les entêtes. Si je dois par exemple construire un tableau avec une colonne reprenant un taux d'érosion, j'appellerai ma colonne "taux érosion" et pas "taux d'érosion", alors que tu ne peux pas te permettre ce genre de fantaisies avec des noms de villes qui doivent être écrits exactement comme ils existent. C'est pour moi l'indicateur d'un défaut structurel des tableaux que de devoir respecter exactement un nom de colonne, à l'apostrophe près.

[mfoxy]

Bonjour,

Quitté à utiliser Excel comme une pseudo sgbd, et utilisé des data range, ne serait-ils pas opportun d utiliser des requêtes sql sur nos tableau, avec les avantage connu des requêtes sql ?

Selon moi l utilisation des requêtes sql permettraient de s affranchir des macros de tris, pour les combo en cascade ajout d un critère dans requêtes, union de plusieurs table,...

Je ne suis pas assez calé que pour créer des functions générique et exploitable dans de multiple projets, mais il me semble à mon petit niveau que cela pourrait être très intéressant.

[Pierre Fauconnier]

Salut mfoxy,

Utiliser Excel comme un SGBD(R), c'est utiliser un outil inadapté, c'est l'utiliser pour ce pour quoi il n'est pas fait. C'est donc choisir une solution de compromis.

Le SQL d'Excel ne permet pas un CRUD complet (Create, Read, Update, Delete) puisqu'il n'accepte pas les requêtes de suppression. il ne permet pas les jointures gauches (left join) de plus d'un niveau... Palier ces problèmes par VBA n'est plus vraiment utiliser un "SGBD" puisque, pour moi, un SGBD doit au moins permettre le CRUD... Les champs d'une requête SQL sur Excel ne sont pas typés, ce qui ne simplifie pas la gestion "SGBD" de l'outil.

Donc, à chacun de choisir ce qu'il lui plait le mieux, ou le dérange le moins... Dès lors que l'on sort du "périmètre de compétence" de l'outil (je rappelle qu'Exel est un outil d'analyse de données, à la base), on s'oblige à être ingénieux, plombier, Mc Giver, ... . Perso, je ne raffole pas du tout des solutions SQL sur des fichiers Excel, comme je ne raffole pas de solutions "de programmeur" à coup d'array, voire de dictionary, qui demandent au non-initié de s'initier à la programmation (enfin, quand je dis "initier", je pense que c'est plus que de l'initiation ^^), et je privilégie donc un code peut-être un peu plus long, mais plus compréhensible par un excellien, et plus facilement adaptable aux inévitables évolutions du classeur.

[mfoxy]

Bonjour Pierre,

Merci pour ton avis.

C était justement en relisant ton tuto Dialogues entre Excel et Access grâce à Adodb, et celui de la sentinelle de Laurent OTT, qui traite justement les table via sql que je me posais la question.

Merci d avoir éclairci ma demande.

Bat

[boisgontierjacques]

Bonsoir,

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
Private Sub UserForm_Initialize()
  Me.ListBox1.List = [t_iles].Value
End Sub
 
Private Sub ListBox1_Click()
  tbl = [t_villes].Value
  n = 0: Dim tbl2()
  For i = 1 To UBound(tbl)
    If tbl(i, 1) = Me.ListBox1 Then
       n = n + 1:       ReDim Preserve tbl2(1 To 2, 1 To n)
       tbl2(1, n) = tbl(i, 2): tbl2(2, n) = tbl(i, 3)
    End If
  Next i
  If n > 0 Then Me.ListBox2.Column = tbl2 Else Me.ListBox2.Clear
End Sub
 
Private Sub ListBox2_Click()
  tbl = [t_rues].Value
  n = 0: Dim tbl2()
  For i = 1 To UBound(tbl)
    If tbl(i, 1) = Me.ListBox2 Then
       n = n + 1: ReDim Preserve tbl2(1 To 2, 1 To n)
       tbl2(1, n) = tbl(i, 2)
    End If
  Next i
  If n > 0 Then Me.ListBox3.Column = tbl2 Else Me.ListBox3.Clear
End Sub

Boisgontier

[boisgontierjacques]

Autre solution avec la jointure des 3 tables (généralement fournie par l'informatique centrale)

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
Dim TblBD()
Private Sub UserForm_Initialize()
  TblBD = [tableau1].Value
  Set d = CreateObject("scripting.dictionary")
  For i = 1 To UBound(TblBD)
    d(TblBD(i, 1)) = ""
  Next i
  Me.ListBox1.List = d.keys
End Sub
 
Private Sub ListBox1_Click()
  Set d = CreateObject("scripting.dictionary")
  For i = 1 To UBound(TblBD)
    If TblBD(i, 1) = Me.ListBox1 Then
       d(TblBD(i, 2)) = ""
    End If
  Next i
  If d.Count > 0 Then Me.ListBox2.List = d.keys Else Me.ListBox2.Clear
End Sub
 
Private Sub ListBox2_Click()
  Set d = CreateObject("scripting.dictionary")
  For i = 1 To UBound(TblBD)
    If TblBD(i, 1) = Me.ListBox1 And TblBD(i, 2) = Me.ListBox2 Then
       d(TblBD(i, 3)) = ""
    End If
  Next i
  If d.Count > 0 Then Me.ListBox3.List = d.keys Else Me.ListBox3.Clear
End Sub

Boisgontier

[Pierre Fauconnier]

Si "l'informatique centrale" (à définir) est capable de fournir la jointure des trois tables, alors elle est capable de fournir la vue/requête/procédure stockée qui permet de se passer du dictionnaire, notamment, et Power Query permet également de se passer du code de tri et permet d'obtenir les listes qui alimenteront les combobox, à nouveau sans avoir besoin de dico ^^

Avec Excel, dans la solution "trois colonne en un seul tableau", il est également possible de se passer de dico et utilisant les outils de tri en VBA (programmer en VBA = penser en Excel avant de passer en VBA). Mais cela suppose que le tableau a été préparé en amont parce que la saisie manuelle de données dans un tableau comme celui présenté apporte son lot d'erreurs potentielles. Perso, à nouveau dans une approche "utilisateur Excel" et pas "programmeur", face à un tableau pareil, je le répartis en trois tableau et je pars sur l'approche donnée plus haut

Au risque de déplaire, je pense que la seule approche est celle de trois tables, et lorsque les données ne sont pas fournies sous cette forme, la première chose est de les remettre d'équerre et de travailler avec les design patterns qui ont fait leurs preuves. Si je recevais de "l'informatique centrale" (??) un tableau à trois colonnes, je m'empresserais de le splitter en trois tables pour retrouver une organisation de données qui tient la route. Ca prendrait une dizaine de lignes de code ou ça demanderait 2 minutes de mise en place dans PowerQuery. Cette façon de préparer les données permet d'utiliser tous les outils mis à notre disposition, plutôt que de passer par des dictionnaires, des boucles et des machins "inventés" à chaque fois.

[OrDonc]

Re ...

Je ne suis pas le seul à avoir des fuites dans les idées, non ? ici (suite des précédents).
Dans l’exemple* suivant, dans le cadre donné, 2 tableaux IV et Rue mais 2 contrôles seulement Ci, Cv, en faisant attention que les noms de Villes de IV soient identiques à ceux des titres de Rue

*Il y a quand même un objectif visé quand on veut construire, non ?
Pour moi, c’est une des façons pratiques d’avoir sous les yeux une colonne située très, très loin sans beaucoup d’efforts en vue de la modifier ou pas.

Autre réponse : dans

Code :

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

1
2
3
4
Public Sub Change(CbSource As MSForms.ComboBox, CbDest As MSForms.ComboBox, nomTablo As String)
      If CbSource.Value <> vbNullString Then
          CbDest.Clear
          With Range(nomTablo & "[" & CbSource.Value & "]")

CbSource.Value désigne un titre de Rue. Il y a erreur quand celui-ci ne contient pas l’apostrophe que tu as dans le nom des villes !

[Pierre Fauconnier]

OrDonc,

Crois bien qu'il n'y a rien de personnel dans ce que je vais écrire...

• Je n'aime pas les EXIT SUB (ici et ici);
• Je n'aime pas les écriture condensées, puisqu'elles ne permettent pas la saisie semi-automatique (Late Binding oblige) et donc ne permettent pas, à la saisie, de savoir avoir quoi on joue. Cela implique un plus gros travail du (pré)processeur; cela amène à des erreurs de frappe qui ne seront détectées qu'à l'exécution; cela amène, comme expliqué au point suivant, à utiliser des valeurs approximatives pour certaines propriétés; cela réduit la lisibilité et la compréhension intuitive du code produit puisque l'on ne sait pas directement avec quoi on joue (dans le cas de [IV], comme ça ne peut représenter une plage, on cherche à quel type de données cela peut faire référence); cette écriture perd le néophyte dans un code abscons et illisible pour lui en première lecture. S'il s'agissait seulement d'un style d'écriture sans conséquences, je laisserais passer, mais c'est vraiment une mauvaise pratique que de coder une plage avec la notation à crochets;
• Je n'aime pas les Hidden = 0 et Hidden = 1 (probablement codés ainsi à cause de la notation abrégée qui ne donne pas la liste des méthodes/propriétés). Hidden, pour une colonne, prend True (-1) ou False (0). Mettre 1 pour True est un "abus de codage" qui "passe" parce que, sauf dans le cas d'un test explicite sur True, le VBA teste que c'est faux ou que ce n'es pas faux ;
• Je n'aime pas les [IV] et les [E1] parce que cela impose une construction du classeur qui utilise la colonne IV et la cellule E1 (de la feuille active), alors qu'avec les tableaux structurés, on peut se passer de savoir où se trouvent les données;
• Pourquoi IV (colonne 256) alors qu'aujourd'hui, on travaille sur 16384 colonnes (XFD). IV, dans une construction comme le tableau de départ proposé par Pijaku, pourrait vite s'avérer limitant si l'on a plus que 256 villes à traiter...

As-tu testé ton code? un truc comme [IV] me semble devoir poser problème... (sauf à l'avoir défini avant, hors de la procédure, ce que je n'ai pas vu dans ton code). A la rigueur, je pourrais lire [IV:IV], mais chez moi, dans le contexte que tu donnes qui est de pointer vers un range, [IV] génère une erreur.


Concernant l'apostrophe, je n'ai compris ta réponse. J'ai expliqué qu'il fallait échapper l'apostrophe en la doublant. Tu as une autre solution?

[pijaku]

Bonjour,

Pour moi, l'apostrophe n'est qu'un "dégât collatéral". Merci à Pierre pour ta solution.

Je ne comprends pas très bien ton propos. La fonction GetArrayFromRange accepte une plage de données, qu'elle soit issue d'un listobject ou pas ne change rien.

Mea Culpa.
Je me suis perdu en essayant plusieurs choses, ça fonctionne plutôt bien.

Pour en revenir à mon fichier en question, il s'agit d'un fichier d'analyse, non d'un SGBD.
Cette partie "SGBD" n'est là que comme assistance à la saisie et ne représente qu'une petite partie de l'outil.
Mais, de toutes façons, ça n'est pas le propos.

Cette discussion a comme objet initial de réaliser des combobox en cascade à partir de données contenues dans un (ou des) tableaux structurés.
Pierre nous propose une réorganisation des données, Jacques également, avec le conseil d'Ordonc sur le cas de la colonne complétée d'une unique donnée.

Dont acte.

J'aimais cependant beaucoup l'organisation des données dans mon exemple.
> Elle ne prend pas trop de "place" car moins de redondance de termes.
> Elle est, somme toute, facile à traiter en VBA et simple à comprendre (en Excel) pour un néophyte.

Je vais tout de même tester plus longuement les propositions d'organisation de Pierre et Jacques.

[OrDonc]

Re …

Pierre Fauconnier a écrit

Crois bien qu'il n'y a rien de personnel dans ce que je vais écrire...

Aucun souci, c’est bien comme cela que je l’entends même si je ne fais pas miens certains de tes avis !

Le ListObject contient plusieurs objets de type Range: …

.
Certains veulent, avec un tel objet, n’utiliser que ses propriétés en pensant seulement aux avantages procurés, en faisant abstraction de tous ses désavantages (oui, oui, il y en a*) avec seulement la syntaxe qui leur est affectée (souci : il faut commencer par l’ apprendre, la digérer).

Quand je prévois de visiter la Corse, je ne commence pas par apprendre sa langue et je ne crains pas d’utiliser le français pour me faire comprendre.
En gros, je procède ainsi avec les tableaux structurés.
En utilisant Range("IV").Rows(0) plutôt que Range("IV[#Headers]"), je ne vois quelle erreur je commets. Je me réfère souvent au n° des lignes du tableau, plutôt que de créer une colonne d’index ,tout en sachant que le 0 est l’index de la ligne des titres (cachée ou pas).
Oups, ces 0 et 1 en informatique …

Voilà un autre exemple en gardant 2 listes déroulantes et …
un tri de la colonne du tableau affichée (à tester avec Guadeloupe, Les Abymes)*

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
Private Sub UserForm_Initialize()
    Ci.List = Application.Transpose([IV].Rows(0).Value)
    Ci = ""
End Sub
Private Sub Ci_Change()
    If Ci.ListIndex < 0 Then [rue].Columns.Hidden = 0: Cv.Clear: Exit Sub
    Cv.List = [IV].Columns(Ci.ListIndex + 1).SpecialCells(2).Value
    Cv = "": Cv.DropDown
End Sub
Private Sub Cv_Change()
    Dim L As Long, c As Integer
    L = [rue].Rows.Count: [rue].Columns.Hidden = 1
    If Cv.ListIndex < 0 Then Exit Sub
    c = [rue].Rows(0).Find(Cv).Column - [rue].Column + 1
    [rue].Columns(c).Hidden = 0
    '*********** tri de la colonne**********
    [rue].Columns(c).Copy [ZZ2]  ‘assez loin pour éviter des chevauchements
    [ZZ2].Resize([rue].Rows.Count).Sort [ZZ2]
    Range("ZZ2:ZZ" & L).Copy [rue].Item(1, c)
    [ZZ:ZZ].Delete
    [E1].Select
End Sub

*On peut, en s’inspirant de ce code tri, trier chaque colonne du tableau indépendamment des autres, avec une boucle. Bien sûr il y a d’autres méthodes, mais c’est une autre histoire ...

[Pierre Fauconnier]

Je n'avais pas fait attention que tu parlais d'un tableau structuré nommé IV, je croyais que tu parlais de la colonne IV (qui était la dernière colonne dans les versions antérieures d'Excel <=2003), d'où ma méprise par rapport à ton code. La façon de nommer tableaux et objets IV, Ci, Cv n'aide évidemment pas à la compréhension du code proposé et ajoute encore de la confusion à celle provoquée par la notation à crochets.

Pour le reste, je pense n'avoir strictement rien compris de ton propos

Notamment, je ne vois pas l'intérêt de vouloir trier les données d'une colonne d'un tableau structuré indépendant des autres colonnes. Ca n'a aucun sens dans un tableau structuré, dans lequel on appréhende la ligne entière du tableau comme indivisible (C'est LE but du tableau structuré, voir le chapitre II de mon tuto et les avantages liés, notamment le tri sécurisé). Un tableau structuré est à regarder comme une boite dans laquelle tu mets des fiches (les lignes de données), et dans laquelle il ne te viendrait pas à l'idée de faire passer des données d'une fiche à l'autre (ce que tu fais si tu tries un colonne du tableau indépendamment des autres). C'est utiliser un outil qui n'est pas fait pour et il y a alors d'autres structures à adopter. Si les colonnes d'un tableau structuré doivent bouger indépendamment des autres, c'est que les données qui sont sur une même ligne du tableau n'ont aucun lien entre elles, et dès lors, le tableau structuré n'est pas la bonne architecture à adopter, et il est alors plus intéressant de créer autant de tableaux qu'il y a de colonnes "indépendantes"... Ou mieux encore, d'adopter une architecture "Base de données" telle que celle que j'ai décrite dans ma première réponse et qui est, à mes yeux, la seule valable dans le cas présent.

Trier les données d'une colonne du tableau par boucle en VBA est un non-sens dont le besoin résulte d'une mauvaise conception de classeur et/ou d'une incompréhension de ce qu'est le tableau structuré (une table de données) et à quoi il sert.

[...]en faisant abstraction de tous ses désavantages (oui, oui, il y en a*)[...]

En utilisant l'outil pour ce qu'il est et ce pourquoi il est fait, il n'y a aucun désavantage à utiliser un tableau structuré, et il n'y a même que des avantages (ceux que je cite dans mon tuto, et probablement quelques autres)

[OrDonc]

Re ...

Je n'avais pas fait attention que tu parlais d'un tableau structuré nommé IV, je croyais que tu parlais de la colonne IV (qui était la dernière colonne dans les versions antérieures d'Excel <=2003), d'où ma méprise par rapport à ton code.

Dont acte avec plaisir ; pour le reste, aucun commentaire !

[Pierre Fauconnier]

Il n'y a pas de honte à ne pas avoir compris la finalité d'un outil. Il est préférable dans ce cas d'apprendre plutôt que de s'enferrer dans l'erreur

Beaucoup de problèmes en Excel/VBA naissent uniquement d'un outil mal utilisé, d'un classeur mal conçu ou de règles de base de la programmation non comprises et/ou non appliquées. Le but de nos forums est aussi, à mon sens, de partager les bonnes pratiques et d'expliquer en quoi les mauvaises le sont. Je peux comprendre que cela puisse déplaire, voire même que l'on se sente attaqué personnellement (ce qui n'est pas le cas), mais j'ai des difficultés à laisser passer des énormités comme celle de trier une colonne d'un tableau structuré indépendamment des autres, car c'est clairement une mauvaise pratique à déconseiller fortement qui illustre que l'on n'a rien compris de l'outil, comme j'ai des difficultés à laisser passer la notation à crochets qui n'est pas juste un style de programmation (si c'était uniquement cela, je laisserais pisser) mais qui est, elle aussi, une mauvaise pratique pour les raisons expliquées dans mon billet