Répétition de code VBA

Question

Je vous expose mon "petit" problème. J'ai un code VBA très simple, fait avec l'enregistreur de macro, le voici : Range("K8").Select ActiveCell.FormulaR1C1 = "Spare Parts" Range("K9").Select ActiveCell.FormulaR1C1 = "Downstream technology solutions" Range("K10").Select Sheets("Chiffres 2017").Select Range("E6:G7").Select Selection.Copy Sheets("Extract Amalia").Select Range("C192").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Chiffres 2017").Select Range("E23:F24").Select Application.CutCopyMode = False Selection.Copy Sheets("Extract Amalia").Select Range("C194").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Le "problème" est que j'ai ce code environ une centaine de fois dans mon module (même deux puisque tout mon code ne tiens pas, j'ai donc du dire en fin du module 1, de déclencher le module 2). Mon module marche il n'y a pas de soucis (c'est pour faire une extraction) Mais l'extraction prend genre 5 minutes complètes... Et je pense qu'il y'a plus simple que de répéter un code comme un bourrin. Si vous pouviez m'aidez Cordialement, Lultras...

MFerrand · Answer

Diagnostic rapide : poubelle (tout) Tu décris exactement le problème, fournit un modèle, et il deviendra possible de l'examiner... Ne pas fournir ce genre de code surtout ...

MFerrand · Answer

Il est toujours possible de fabriquer un modèle adéquat ...

MFerrand · Answer

Si on ne t'a pas encore dit de mettre sous balises Code le code cité dans un post, c'est fait ! Il serait bon de l'enregistrer !

Lorsqu'une citation est une procédure, on peut le réputer incomplet s'il ne commence pas par Sub et ne se termine pas par End Sub...

En ce qui me concerne, un Select à la première ligne, fait que je ne vais de toute façon pas plus loin dans la lecture...

Et ayant exprimé que j'étais éventuellement prêt à examiner le problème, posé sur un support adéquat, en faisant abstraction de tout code défectueux, je n'ai aucune raison de modifier ce point de vue car c'est la façon la pus simple et la plus rapide pour cerner la question et concevoir des solutions.

MFerrand · Answer

Un support est un classeur conforme à la question posée, et permettant de l'étudier et tester les solutions... Cela a été clairement dit ...

MFerrand · Answer

Désolé ! Pas vu ton classeur...

C'est un peu succinct ! Questions :

Le tableau se balade toujours ainsi au milieu d'une page ?

Pas de ligne d'en-tête ?

Est-ce une extraction qui est souhaitée, en fonction des critères sélectionnés par liste déroulante ?

Apparemment un simple filtre auto suffirait pour afficher... (s'il s'agit simplement de consulter)

Si pour travailler dessus ensuite, il faut définir où disposer l'extraction.

Sous réserve d'arranger un peu les données, pas de difficulté pour obtenir n'importe quel résultat (à préciser).

MFerrand · Answer

J'ai travaillé sur ta feuille, ça te modélisera quelques unes des possibilités... Il est bien évident que les différents éléments de la feuille pourraient tous se trouver sur des feuilles différentes. Une façon d'en tenir compte pour faciliter les adaptations du code consiste à nommer des plages... Les plages nommées étant des objets directement accessibles, on n'a ainsi quasiment plus à se préoccuper des feuilles où elles se trouvent.

J'ai réorganisé la feuille pour travailler un peu plus rationnellement.

La base de données commence à A1 ! J'ai déplacé les plages de listes déroulantes vers les colonnes O P Q, les cellules sous listes déroulantes sont en I J K. Je m'en sers comme zone de critères pour un filtre avancé, la zone d'affichage des données extraites ainsi est à la verticale à partir de la ligne 7.

Deux boutons : un qui permet de faire une extraction selon les critères sélectionnés dans les listes déroulantes, l'autre qui lance une procédure de listage des différentes extractions que l'on peut faire, sur une autre feuille.

Il y a 6 plages nommées : la base (nom dynamique, qui s'adapte aux variations ; l'en-tête est incluse, car elle est utilisée pour le filtrage), la zone de critères,, les 3 listes de critères et la première ligne de postionnement de l'extraction. A voir dans le gestionnaire de noms. (NB: les noms Critères et Extraire, niveau feuille sont mis automatiquement par Excel dès lors que l'on procède à un filtrage avancé, il vaut mieux ne pas les utiliser ailleurs pour éviter des confusions et des risques d'erreurs.)

Le bouton Extraction lance une procédure d'extraction des données selon les critères définis (par sélection dans les listes déroulantes).

Sub Extraction()
    Application.ScreenUpdating = False
    [Extrac].CurrentRegion.ClearContents
    [Base].AdvancedFilter xlFilterCopy, [Crit], [Extrac]
End Sub

Comme on le voit, c'est pas volumineux , le filtrage proprement dit occupe une ligne de code, et les deux autres sont là pour inhiber la mise à jour de l'écran (évite des tressautements) et effacer l'extraction précédente.

Cela fait partie des fonctionnalités d'Excel qui sont plus simples à utiliser en VBA qu'à opérer manuellement sur la feuille. On doit même pouvoir arriver à écrire la macro, la lancer plus rapidement que la procédure manuelle...

Je dois m'interrompre un petit moment, je reviens pour la suite...

MFerrand · Answer

Ce type de procédure s'imposait presque, dans le contexte car parfaitement adapté aux éléments mis en place...

Du coup, elle est réutilisée pour le listage, alors que d'autres méthodes sont possibles... Tant que celle-ci s'avère suffisamment rapide, il n'y a pas de raison importante d'en changer, si elle se révélait prendre un peu trop de temps (base importante), on pourrait essayer dans un premier temps de l'accélérer en différant l'affectation des extractions successives pour ne les affecter que globalement à la fin, et dans un second opérer par tableau à partir d'un tri de la base qui la mettrait dans l'ordre adéquat...

Le bouton Listage lance la procédure ci-dessous :

Sub ListerParCritères()
    Dim a%, b%, c%, ni%, n%, ws As Worksheet
    'Set ws = Worksheets.Add(after:=ActiveSheet)
    Set ws = ActiveSheet.Next
    n = ws.Cells(.Rows.Count, 1).End(xlUp).Row
    ws.Range("A1:C" & n).ClearContents
    ws.Range("A1:C1").Value = [Base].Resize(1).Value: n = 3
    Application.ScreenUpdating = False
    For a = 1 To [Crit1].Rows.Count
        For b = 1 To [Crit2].Rows.Count
            For c = 1 To [Crit3].Rows.Count
                With [Crit]
                    .Cells(2, 1) = [Crit1].Cells(a, 1)
                    .Cells(2, 3) = [Crit2].Cells(b, 1)
                    .Cells(2, 2) = [Crit3].Cells(c, 1)
                End With
                Extraction
                With [Extrac].CurrentRegion
                    ni = .Rows.Count - 1
                    If ni > 0 Then
                        ws.Cells(n, 1).Resize(ni, 3).Value = .Offset(1) _
                         .Resize(ni).Value
                        n = n + ni + 1
                    End If
                End With
            Next c
        Next b
    Next a
    ws.Activate
End Sub

Cela reste de taille très très raisonnable (mais je pense qu'avec la 3e méthode que j'ai évoqué on devrait arriver à faire plus court).

On est toutefois assez loin de ce code qui t'oblige à utiliser deux modules, évoqué dans tes propos initiant le sujet... j'avoue ne pas avoir bien compris de quoi il retournait... Quoi qu'il en soit, en VBA on évite toujours des répétitions indéfinies, boucles et variables le permettent généralement, et c'est dès la première répétition qu'il faut réagir et se dire que c'est une fois de trop...

C'est un peu pareil avec les formules excel, je suis toujours effaré quand je vois une formule qui arrive à occuper une dizaine de lignes ! En ce qui me concerne, si je me vois dépasser la 3e ligne, je m'arrête, c'est que la méthode n'est pas bonne, et qu'il faut réfléchir pour en trouver une meilleure et plus économique, et si pas trouvé dans Excel passage à VBA...

La méthode de la procédure est simple, 3 boucles imbriquées qui procèdent successivement à toutes les combinaisons des 3 critères, à chaque combinaison une extraction, que l'on affecte à sa position finale sur une autre feuille. Il faut noter que l'on ne procède pas par copier-coller mais par affectation de valeurs.

Quasiment tous les demandeurs ont comme réflexe de chercher à faire des copies et du collage... Mauvais réflexe en VBA, très mauvais même, car on continue de penser qu'on opère manuellement dans Excel, alors que VBA dispose d'autres moyens qui permettent justement de faire autrement, et généralement plus vite ! Avant de se lancer dans un copier-coller, il faut donc toujours se demander si l'on ne peut pas faire autrement (on peut toujours en général) et peser les avantages et inconvénients de chaque méthode et choisir en fonction.

Une ligne de code est invalidée en début de procédure (apostrophe placée devant), elle pemet de lister sur une nouvelle feuille insérée par la procédure. Si on utilise cette option, il faut enlever l'apostrophe, mais par contre en mettre devant les 3 lignes qui suivent (qui procèdent à l'effacement de la feuille suivante pour y réinscrire une nouvelle liste).

Cordialement.

6lultras977-test-vba-forum.xlsm (24.93 Ko)

MFerrand · Answer

Je pencherais plutôt pour un problème de longueur de procédure (il y a effectivement une limite à ce niveau), car s'agissant du module lui-même, je n'ai jamais atteint de saturation et j'en de bien chargés... Il est possible d'adapter à plus de colonnes, mais ce qui va allonger la durée d'exécution c'est le nombre de combinaisons de critères. Dans le modèle les 3 séries de critères conduisent à 90 combinaisons. Pour l'extraction individuelle, cela ne change rien si on augmente, pour les lister toutes, il sera sans doute préférable de passer à une autre méthode selon le nombre de combinaisons... Dans la mesure où un tri exhaustif, sur tous les critères, conduirait à ordonner la base identiquement à la liste qu'on compose, on gagnerait certainement du temps en éliminant l'extraction par combinaison de critères...

MFerrand · Answer

Quelles formules ...

MFerrand · Answer

Comme dans le fichier, pas de formule, je ne vois pas très bien de quoi tu parles... Voilà toujours une autre façon de procéder au même listage que précédemmant :

Sub Lister2()
    Dim n%, i%, ws As Worksheet
    Set ws = ActiveSheet.Next
    n = ws.Cells(Rows.Count, 1).End(xlUp).Row
    ws.Range("A1:C" & n).ClearContents
    With [Base]
        .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 3), order2:=xlAscending, _
         key3:=.Cells(1, 2), order3:=xlAscending, Header:=xlYes
        n = .Rows.Count
        ws.Range("A1").Resize(n, 3).Value = .Value
    End With
    With ws
        For i = n To 2 Step -1
            If .Cells(i - 1, 1) <> .Cells(i, 1) Or .Cells(i - 1, 2) <> .Cells(i, 2) Or _
             .Cells(i - 1, 3) <> .Cells(i, 3) Then .Cells(i, 1).Resize(, 3).Insert xlShiftDown
        Next i
        .Activate
    End With
End Sub

Cordialement.