Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Tri scripting dictionnary


Sujet :

Macros et VBA Excel

  1. #1
    Nouveau membre du Club
    Tri scripting dictionnary
    Bonjour à toutes et tous,

    j'ai une question concernant le script ci dessous (fonctionnant correctement):

    comment procéder afin de trier l'affichage rendu

    En effet Statut peut correspondre à

    Active / Rejected / Negociation / Submitted / Drafting / Closed (celui là non affiché)

    à l'heure actuelle tout se reporte bien mais je voudrais par exemple afficher les rejected tout à la fin, aussi est il possible d'organiser l'affichage de l'array dans l'ordre souhaité et si oui comment svp?
    En effet j'aimerais les lister comme ceci (Active / Negociation / Submitted / Drafting / Rejected)

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    If statut <> "Closed" Then
    If Not dic_statuts.exists(statut) Then Set dic_statuts(statut) = CreateObject("Scripting.Dictionary")
    Set dic_projects = dic_statuts(statut)
    dic_projects(id_projet) = Array(statut, Acronym, Topic, début, fin, commentaire, Investigator_firstname, Investigator_name, CLng(Budget_Requested))
    Set dic_statuts(statut) = dic_projects
     
    End If

    Merci à vous par avance
    bonne journée

  2. #2
    Nouveau membre du Club
    Bonjour,

    voici à toutes fins utiles un exemple pour illustrer mon soucis
    le fichier test contient les données et va compléter un rapport dans le document dest

    je souhaiterais donc organiser l'affichage de sorte à ne pas avoir les abandonned et rejected juste après les active

    j'aimerais avoir dans l'ordre Active / Submitted / negociation / Drafting / Abandonned / Closed (celui là non affiché)

    merci par avance à vous pour votre aide
    Bonne journée


  3. #3
    Responsable
    Office & Excel

    Salut.

    Un dictionary n'est ni trié ni triable, de sorte que tu vas devoir trier ton array via un algo de ton choix (tri à bulles très lent, autres algos à mettre en place).


    La question est: Pourquoi passer par un dictionary alors que tu pourrais passer par un tableau structuré, éventuellement dans une feuille cachée, que tu trierais comme tu le souhaites et que tu restituerais filtré et trié en VBA. Si u nous disais d'où tu pars et où tu veux arriver, plutôt que comment, nous pourrions t'aider à trouver le bon chemin plutôt que de t'accompagner sur une mise en place qui, de mon point de vue, n'est pas la meilleure.

    Une solution (tag 2016) sans VBA ou à intégrer dans ta solution VBA serait de passer par Power Query au départ de ton tableau structuré des tâches t_Tâches et d'un tableau des statuts t_Statuts qui renseigne l'ordre de tri:
    1. Importer les deux tableaux dans Power Query
    2. Fusionner dans une nouvelle requête t_Ouvertes sur base de la colonne commune Statut
    3. Développer la table de statuts dans t_Ouvertes pour récupérer la colonne permettant le tri
    4. Filtrer pour exclure les 999 que l'on ne veut pas voir;
    5. Trier sur la colonne d'ordre
    6. Supprimer cette colonne
    7. Restituer le tout dans Excel






    En pur VBA, je ferais pareil avec une feuille masquée pour préparer les résultats.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  4. #4
    Responsable
    Office & Excel

    Si tu souhaites le réaliser en VBA, voici, sur base des trois tableaux précités t_Tâches, t_Statuts, t_Ouvertes et d'un tableau t_Temp, une solution qui utilise les tableaux structurés en VBA

    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
    Sub PrepareTasks()
      Dim Target As Range
      Dim Pos, CountOf As Long
     
      ' Nettoyage du tableau temporaire
      If Not Range("t_Temp").ListObject.DataBodyRange Is Nothing Then Range("t_Temp").ListObject.DataBodyRange.Delete
     
      ' Copie des taches dans le temporaire
      Set Target = Range("t_Temp").ListObject.ListRows.Add().Range(1)
      Target.Resize(Range("t_Tâches").Rows.Count, 2).Value = Range("t_Tâches").Value
     
      ' Ajout de l'ordre et tri
      Range("t_Temp[Ordre]").Formula = "=Index(t_Statuts[Ordre],match([@Statut],t_Statuts[Statut],0))"
      With Range("t_Temp").ListObject.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("t_Temp[Ordre]")
        .Apply
      End With
     
      ' Suppression des statuts 999
      Pos = Application.Match(999, Range("t_Temp[Ordre]"), 0)
      If Not IsError(Pos) Then
        CountOf = Application.CountIfs(Range("t_Temp[Ordre]"), 999)
        Range("t_Temp[Tâche]")(Pos).Resize(CountOf, 3).Delete
      End If
     
      ' Copie dans le tableau final
      If Not Range("t_Ouvertes").ListObject.DataBodyRange Is Nothing Then Range("t_Ouvertes").ListObject.DataBodyRange.Delete
      Set Target = Range("t_Ouvertes").ListObject.ListRows.Add().Range(1)
      Target.Resize(Range("t_Temp").Rows.Count, 2).Value = Range("t_Temp[[Tâche]:[Statut]]").Value
    End Sub
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  5. #5
    Nouveau membre du Club
    Bonjour à vous et merci pour vos retours.

    merci pour cette remarque plus que pertinente sur le fait que les dicos ne soit pas triable.
    cela m'évite de m'entêter dans cette direction.

    en fait je pars de ma base de donnée comprenant plusieurs données projets segmentés et je dois désormais générer un rapport pour mes supérieurs
    j'ai décidé d'automatiser cette tâche avec un tableau structuré segmentant et classant les projets selon leurs statuts.

    je n'ai pas su comment procéder et une âme charitable m'a proposer d'utiliser la méthode dico qui fonctionne au demeurant mais avec la limite que cela ne peut donc se classer m'obligeant à reformatter le doc manuellement ... ce qui me fait perdre du temps

    powerquery que j'avais testé très peu avait une tendance à ralentir le processus global de ma base et ne suis donc pas donc tenté de me réorienter vers cela si je peux éviter. quant au tableau structuré pourquoi pas mais j'ai pas encore saisis la méthode...

  6. #6
    Responsable
    Office & Excel

    A toutes fins utiles, voici le lien vers mon tuto sur les tableaux structurés.

    Power Query n'est actif qu'à l'actualisation, de sorte que je ne comprends pas bien ce que tu veux dire par "ralentir le traitement". A moins que tu aies des centaines de milliers de lignes et des centaines d'étapes dans ta solution Power Query, je doute que ce soit "lent", même si cette notion est hautement subjective. Pas certain que ce soit plus lent que de passer par du VBA.

    Voici deux solutions, l'une Power Query et l'autre VBA, sur lesquelles tu pourrais t'appuyer.




    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  7. #7
    Nouveau membre du Club
    A la vue de votre retour je dois donc conclure ne pas avoir utilisé powerquery de manière adéquate dans le passé

    je vous remercie pour vos deux fichiers
    je veux bien tenter cette méthode si effectivement ne se lance qu'actualisation.


    dans mon cas le soucis c'est que je dois récupérer des données de différents onglets:
    - projects
    - investigators
    - budget

    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
     With tb_projects
            For i = 0 To .ListRows.Count
                id_projet = .ListColumns("Id_Project").DataBodyRange.Rows(i)
                Acronym = .ListColumns("Acronym").DataBodyRange.Rows(i)
                Topic = .ListColumns("Topic").DataBodyRange.Rows(i)
                début = .ListColumns("Start_Date").DataBodyRange.Rows(i)
                fin = .ListColumns("End_Date").DataBodyRange.Rows(i)
                commentaire = .ListColumns("Comments").DataBodyRange.Rows(i)
                statut = .ListColumns("Status").DataBodyRange.Rows(i)
     
                 With Worksheets("Investigators")
                    On Error Resume Next
                   project_Range = Application.Match(id_projet, .Range("B:B"), 0)
     
     
                    Investigator_firstname = Empty: Investigator_name = Empty
                    If Err = 0 Then
                        Investigator_firstname = .Range("A:G")(project_Range, 3)
                        Investigator_name = .Range("A:G")(project_Range, 4)
                    End If
                End With
     
     
                      With Worksheets("Budget")
                    On Error Resume Next
                   project_Range = Application.Match(id_projet, .Range("A:A"), 0)
     
                                     Budget_Requested = Empty
                    If Err = 0 Then
                        Budget_Requested = .Range("A:O")(project_Range, 15)
                               End If
                End With


    et je n'ai pas forcement besoin de l'ensemble des données juste certaines
    aussi je dois bien a avouer ne pas trop savoir comment m'y prendre

    faut il importer tous les tableaux de chaque onglet?

  8. #8
    Nouveau membre du Club
    bion bah de toute facon tournant sur mon pc portable de boulot je ne peux activer powerquery sous excel car non visible des complements com

    oups rien dit je ne regardais pas au bon endroit déoslé

  9. #9
    Responsable
    Office & Excel

    Normalement, les règles de bonne conception requièrent que l'on dispose un seul tableau structuré par feuille. Power Query permet de récupérer tous les tableaux du classeur et, en les nommant de façon rigoureuse et systématique, on peut exploiter uniquement les tableaux souhaités, en utilisant un préfixe ou un suffixe pour désigner les tableaux à récupérer.

    Dans le cas des tableaux mentionnés et au vu de leurs noms, je suppose qu'il faut recréer un tableau qui va chercher les données dans les différents tableaux de base, un peu comme un RECHERCHEV, puis filtrer, trier, supprimer des colonnes, éventuellement ajouter des colonnes formulées => Bref, rien de bien compliqué pour Power Query. Dans cette vidéo de blog, j'explique comment on peut fusionner trois tableaux.

    Pour pouvoir aller plus loin, il faudrait avoir la structure des tableaux de base avec quelques données anonymisées dedans, le tableau de résultat qui reprend ces données et les règles qui président à son élaboration.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  10. #10
    Nouveau membre du Club
    la structure est dans le fichier test partagé

  11. #11
    Responsable
    Office & Excel

    Dans le fichier Dest, il n'y a qu'une feuille blanche, et ça n'explique pas le traitement. Faut-il filtrer certaines données, les trier, ....?

    Aide-toi, DVP t'aidera ... ^^
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  12. #12
    Nouveau membre du Club
    oui c'est normal tout est généré depuis le fichier test

  13. #13
    Responsable
    Office & Excel

    Tu t'imagines réellement que je vais lire ton code pour découvrir ce que tu peux m'expliquer en quelques lignes? Manifestement, nous n'avons pas toi et moi la même façon de voir à quoi sert un forum d'entraide informatique

    Bon courage pour la suite!
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  14. #14
    Nouveau membre du Club
    Tout d'abord Veuillez m'excuser je pensais que vous aviez déjà parcouru le code question et merci pour le temps pris pour votre assitance

    j'ai une base de donnée globale comprenant une liste de projets avec leurs work-packages, leur deliverables, le personnel impliqués etc.
    mon objectif ici est ici de généré un document récapitulatif des projets en cours de gestion qu'il soit déja actif, en cours de nego, déja soumis et meme nouvellement rejeté
    pour se faire:

    Tout d'abord et dans l'onglet projet je récupère les données suivantes
    - Id_Project
    - Acronym
    - Topic
    - Start_Date
    - End_Date
    - Comments
    - statut

    avec l'id projet de chaque projet je récupère ensuite

    dans l'onglet investigator:
    - l'investigateur principal du projet avec son prenom et nom

    et dans l'onglet budget
    - le montant demandé pour chaque projet

    ce que je recherche au final c'est pouvoir classer les projets avec leurs informations propres par leurs statuts dans un ordre donné à savoir Active / Negociation / Submitted / Drafting / Rejected
    je ne souhaite pas pour information affficher le statut closed

    merci par avance pour l'aide que vous pourrez m'apporter

  15. #15
    Responsable
    Office & Excel

    Tu as les bases du travail à réaliser, tant en Power Query qu'en VBA.

    Avec Power Query, tu peux parcourir les étapes du travail du fichier que j'ai fourni, elles sont en général simples à comprendre. En VBA, j'ai commenté les quelques lignes qui servent à réaliser cela. Il te suffit de fusionner les tableaux de départ pour en récupérer les données. Avec la vidéo que je t'ai fournie en lien, cela ne devrait pas poser de problème.

    Le code VBA que je t'ai fourni (17 lignes hors commentaires et déclarations!) illustre comment on peut lier les tableaux par VBA (application.match... qui est l'équivalent VBA de Power Query). C'est déjà une belle base de travail, non? En gros, il te suffit d'un peu de mise en forme à la fin (et on peut faire simple à ce niveau, surtout avec des tableaux structurés). Je veux bien aider, mais faire le travail gratos alors que c'est mon métier et que je facture mes clients pour ce genre de boulot, ça je ne fais pas.

    Si tu veux de l'aide (de ma part en tout cas), retrousse-toi les manches, mets les mains dans le cambouis et montre ce que tu as essayé, où ça bloque, etc etc...

    Et si tu l'a déjà fait en VBA avec le dictionary, peut-être te suffit-il alors de trier ton tableau final (au passage, je te donne un code dans la solution VBA dont tu peux t'inspirer). Que te faut-il de plus? le sourire de la crémière ?

    Pour conclure en attendant que tu montres que tu as cherché, mon approche sans dictionary est une approche Excel (quand on programme en VBA pour Excel, on pense d'abord Excel avant de penser VBA, surtout si on n'est pas programmeur). L'exécution du code est parfois plus lente, mais au moins tu la maîtrise car elle réalise en VBA ce que tu aurais fait en Excel:
    • Lier des tableaux avec RECHERCHEV, RECHERCHEX (XL 365), INDEx-EQUIV;
    • Trier des tableaux;
    • Filtrer des tableaux;
    • Copier des tableaux;
    • Supprimer des colonnes;
    • Faire un peu de mise en forme.



    Il n'y a rien de bien mystérieux dans les quelques lignes de code (ou les quelques étapes Power Query) nécessaires à ces manipulations qui sont des manipulations de base lorsque l'on traite des données pour les analyser
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  16. #16
    Nouveau membre du Club
    pas de soucis je suis effectivement disposé à m'y mettre
    je te remercie par ailleurs pour le lien est les documents soumis
    je vais désormais regarder cela de plus près

  17. #17
    Membre éclairé
    j'aimerais les lister comme ceci (Active / Negociation / Submitted / Drafting / Rejected)
    Bonsoir,
    Ici il n'est ocunement question d'un tri mais un ordonnancement selon critaire et je ne vois pas Powerquerry réglé cela nativement. Mais Pierre est bien plus compétant que moi pour confirmer ou infirmer !

  18. #18
    Responsable
    Office & Excel

    Salut Thumb,

    Pour résoudre le problème de l'ordonnancement, j'ai ajouté une colonne donnant le rang de chaque statut (voir le message #3). Dès lors, Power Query et VBA sont capables de trier sur base d'une clé de tri personnalisée spécifiée dans le tableau. J'ai mis 999 comme clé de tri pour les statuts que l'on ne souhaite pas retrouver dans le tableau de résultat, de manière à pouvoir filtrer et exclure les lignes qui possèdent le statut 999.

    J'ai mis deux fichiers d'exemple dans le message #6, un pour la solution Power Query et l'autre pour la solution VBA, qui s'appuie du reste sur le même principe de l'ajout d'une colonne d'ordre, le tri ou l'ordonnancement n'étant pas naturel Il suffit d'ouvrir les fichiers et de regarder chaque étape Power Query de la requête t_Ouvertes ou d'exécuter le VBA pas à pas pour voir que c'est en gros le même raisonnement qui s'applique et que les étapes sont les mêmes.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  19. #19
    Expert confirmé
    Hello,
    pour faire un tri suivant une liste on peut utiliser une liste personnalisée (voir ici)
    voici un exemple de code qui copie le contenu de la feuille Projects dans une feuille Temp. Dans cette feuille il y a une liste personnalisée de tri et une liste de filtre. On effectue un tri suivant la liste en P et un filtre suivant la liste en Q :
    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
    Sub CustomTriFiltre()
    Dim r As Range
    Dim rngLstTri As Range
    Sheets("Temp").Range("A1:N78").ClearContents
    Sheets("Projects").Range("A1:N78").Copy
    Sheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteValues
    Set r = Sheets("Temp").Range("A2:N78")
    Set rngLstTri = Sheets("Temp").Range("P2", Sheets("Temp").Range("P2").End(xlDown))
     
     
        Application.AddCustomList rngLstTri
        ' on trie en fonction de la liste personnalisée
        Sheets("Temp").Sort.SortFields.Clear
        r.Sort key1:=[M2], order1:=1, ordercustom:=Application.CustomListCount + 1, _
               key2:=[G2], order2:=1
     
        Application.DeleteCustomList Application.CustomListCount
        ' on  enlève ce qu'il y a en colonne filtre
        r.AutoFilter Field:=13, Criteria1:="<>" & Sheets("Temp").Range("Q2")
    End Sub


    voici le contenu des listes :




    et voici le résultat :




    Ami calmant, J.P
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  20. #20
    Responsable
    Office & Excel

    Salut JP,

    C'est vrai, mais je n'aime pas car la liste existe dans Excel et pas uniquement dans le classeur (et elle est chargée à l'ouverture d'Excel). Je ne recommande donc cette solution que dans de très rares cas.

    Evidemment, tu la crées et la supprime après, mais ça induit du VBA là où ce n'est pas forcément nécessaire. De plus, si l'ordre de tri change, ça impose une modification du code VBA alors que la colonne d'ordre peut être modifiée par "n'importe quel utilisateur" sans connaissance de VBA. De plus, ça ne résout pas le problème des statuts à ne pas prendre en compte
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

###raw>template_hook.ano_emploi###