Optimiser les SELECT

Bonjour le forum,

Je cherche à optimiser ce bout de code (qui ralentit l'execution de la macro)

Private Sub liste_batiments_Change() 'correspond à ma liste déroulante dans mon Userform

liste_locaux.Clear 'à chaque changement dans ma liste déroulante liste_batiments, je réinitialise ma seconde liste déroulante liste_locaux

Call Filtre_afficher_locaux(liste_batiments.Value) 'je réalise un filtre dans une feuille Excel avec le numéro du batiment (colonne A) afin de récupérer ensuite tous les locaux qui sont présents dans le batiment choisi (colonne B)

    N = liste_batiments.Value
    x = Sheets("SS").Range("A7:A65536").Find(N, LookAt:=xlWhole).Row

    Sheets("SS").Visible = True
    Sheets("SS").Select
    Sheets("SS").Range("B" & x).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("DATABASE").Visible = True
    Sheets("DATABASE").Select
    Sheets("DATABASE").Range("L2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _           'je copie colle les résultats des locaux dans une feuille BDD afin de pouvoir récupérer uniquement les valeurs uniques que je mets dans la liste déroulante
        :=False, Transpose:=False

    For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row
      Me.liste_locaux = Sheets("DATABASE").Cells(i, "L")        ' on se positionne dans le combobox
      If Me.liste_locaux.ListIndex = -1 Then                    ' Existe t-il?
         Me.liste_locaux.AddItem Sheets("DATABASE").Cells(i, "L")        'j'alimente ma liste déroulante sans doublons
      End If
    Next i

     Sheets("DATABASE").Range("L2").Select
     Range(Selection, Selection.End(xlDown)).Select
     Selection.ClearContents               'je réinitialise le contenu
     Sheets("SS").Visible = False
     Sheets("DATABASE").Visible = False
     Exit Sub

End Sub

Mon Userform reste ouvert.

On voit bien que les select ralentissent clairement l'execution. Avez-vous une suggestion d'optimisation svp ?

Merci pour votre aide

Cdt,

Excellium

bonjour,

Sans le classeur KIVABIEN avec je doute fort que tu obtiennes une réponse.

Ton classeur doit contenir

La feuille DATABASE avec quelques enregistrements

Le UserForm et tout son code

La Feuille "SS" intégrale.

Cette macro effectue un call (Filtre_afficher_locaux) qui se réfère sans doute à une autre feuille.

Il faudra également cette feuille et cette macro.

A+

Bonjour à tous ...

Galopin a raison ... le fichier ...le fichier ... le fichier ...

La meilleure façon d'optimiser les Select .. c'est de les supprimer ...!!!

Bonjour,

En effet, les Select sont inutiles.

Autre détail, on voit souvent des boucles du type :

For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

Dans cette écriture à chaque itération de i Excel fait le calcul de End(xlUp).Row

Si on écrit plutôt

Dim nb as long, i as long
   nb=Sheets("DATABASE").[L65000].End(xlUp).Row
   for i=2 to nb

Le End(xlUp).Row n'est ici évalué par Excel qu'une seule fois.

L'intérêt? Si la End(xlUp).row est d'environ mille, Excel ira tout simplement mille fois plus vite!

Ceci étant dit, je comprends pas trop l'intérêt de copier/coller une partie des data pour les injecter dans une liste, il y a probablement plus simple à faire (par l'utilisation d'array pas exemple).

Pierre

HS : c'est pour ça que ça m'amuse de voir parfois des codeurs qui sont contents d'écrire un code en un nombre minimum de lignes. Dans cet exemple les 3 lignes sont bien plus efficaces que la ligne seule

pierrep56 a écrit :

Autre détail, on voit souvent des boucles du type :

For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

Dans cette écriture à chaque itération de i Excel fait le calcul de End(xlUp).Row

Bonjour Pierre,

En fait dans le cas d'une boucle for la valeur finale n'est évaluée qu'une seule fois à l'initialisation de la boucle. et donc les 2 codes reviennent au même (evaluation une seule fois), si ce n'est que tu as écris sur 2 lignes ce qui peut s'écrire sur une seule. Ce que je ne fais que très rarement ;o)

Bonjour à tous,

pierrep56

D'accord pour la déclaration des variables, mais pour

For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

je pense que tu fais erreur, [L65000].End(xlUp).Row ne sera évalué qu'une fois.

eric

Bonjour à tous,

A priori Excellium ... n'a pas l'air très très intéressé par les réponses ...

Bonjour (tous)

Oui eriic il me semble avoir lu quelque part, qu'Excel à la particularité de n'évaluer qu'une seule fois l'argument final d'une boucle FOR/NEXT

Cependant je suis d'accord avec Pierre sur la forme

Sub ComplexeAlire()

    For i = 1 To formuleComplxeOuPas
        ' ...
    Next

End Sub

Sub FacileAlire()

    x = FormuleComplexeOuPas
    For i = 1 To x
        ' ...
    Next

End Sub

' Surtout si il y a des imbrications

Sub EncorePlusComplxeAlire()

    For i = 1 To FormuleComplexeOuPas
        For j = 1 To FormuleEncorePlusComplexeOuMoins
            For k = 1 To FormuleToujoursPlusComplexeOuPresque
                ' ...
            Next
        Next
    Next

End Sub

' Mais il est possible aussi de faire encore plus pire !

Sub QuasiementImpossibleAlire()
For i = 1 To FormuleComplexeOuPas - FormuleToueSimpe
For j = 1 To FormuleEncorePlusComplexeOuMoins + FormuleBasique
For k = 1 To FormuleToujoursPlusComplexeOuPresque * FormuleSimplissime / FormulePourRegner + FormuleQuiFaitToutLeReste
' ...
Next
Next
Next
End Sub

Une première "optimisation" tout de même, un peu "à la va vite" certes (je n'ai pas testé ! - faute PJ)

'correspond à ma liste déroulante dans mon Userform
Private Sub liste_batiments_ChangeVersionPremiereSimplification()

    'à chaque changement dans ma liste déroulante liste_batiments, je réinitialise ma seconde liste déroulante liste_locaux
    liste_locaux.Clear

    'je réalise un filtre dans une feuille Excel avec le numéro du batiment (colonne A) afin de récupérer ensuite tous les locaux qui sont présents dans le batiment choisi (colonne B)
    Call Filtre_afficher_locaux(liste_batiments.Value)

    N = liste_batiments.Value
    x = Sheets("SS").Range("A7:A65536").Find(N, LookAt:=xlWhole).Row

    Sheets("SS").Visible = True
    Sheets("SS").Select
    Sheets("SS").Range("B" & x).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("DATABASE").Visible = True
    Sheets("DATABASE").Select
    Sheets("DATABASE").Range("L2").Select

    'je copie colle les résultats des locaux dans une feuille BDD afin de pouvoir récupérer uniquement les valeurs uniques que je mets dans la liste déroulante
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

        ' on se positionne dans le combobox
        Me.liste_locaux = Sheets("DATABASE").Cells(i, "L")
        ' Existe t-il?

        If Me.liste_locaux.ListIndex = -1 Then
            'j'alimente ma liste déroulante sans doublons
            Me.liste_locaux.AddItem Sheets("DATABASE").Cells(i, "L")
        End If
    Next i

    Sheets("DATABASE").Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select

    'je réinitialise le contenu
    Selection.ClearContents
    Sheets("SS").Visible = False
    Sheets("DATABASE").Visible = False

    Exit Sub

End Sub

'correspond à ma liste déroulante dans mon Userform
Private Sub liste_batiments_ChangeVersionDeuxiemeSimplification()

    'à chaque changement dans ma liste déroulante liste_batiments, je réinitialise ma seconde liste déroulante liste_locaux
    liste_locaux.Clear

    'je réalise un filtre dans une feuille Excel avec le numéro du batiment (colonne A) afin de récupérer ensuite tous les locaux qui sont présents dans le batiment choisi (colonne B)
    Call Filtre_afficher_locaux(liste_batiments.Value)

    N = liste_batiments.Value
    x = Sheets("SS").Range("A7:A65536").Find(N, LookAt:=xlWhole).Row

    ' => si l'onglet est invisible tu peux le laisser inivisble / sauf pendant le debuggage eventuellement
    'Sheets("SS").Visible = True

    ' Comme disait Pierre : pas de select => suaf pendant le debug pour controler mais pas a l'execution !
    ' Sheets("SS").Select
    'Sheets("SS").Range("B" & x).Select
    'Range(Selection, Selection.End(xlDown)).Select
    ' a la place tu ecris :
    Sheets("SS").Range(Cells(x, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)).Copy
    ' pas de select donc pas de selection non plus
    'Selection.Copy

    ' => si l'onglet est invisible tu peux le laisser inivisble / sauf pendant le debuggage eventuellement
    'Sheets("DATABASE").Visible = True
    'Sheets("DATABASE").Select
    ' pas de select donc pas de selection non plus
    'je copie colle les résultats des locaux dans une feuille BDD afin de pouvoir récupérer uniquement les valeurs uniques que je mets dans la liste déroulante
    Sheets("DATABASE").Range("L2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ' ET CE N'EST QU'UN DEBUT !!!!!

    For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

        ' on se positionne dans le combobox
        Me.liste_locaux = Sheets("DATABASE").Cells(i, "L")
        ' Existe t-il?

        If Me.liste_locaux.ListIndex = -1 Then
            'j'alimente ma liste déroulante sans doublons
            Me.liste_locaux.AddItem Sheets("DATABASE").Cells(i, "L")
        End If
    Next i

    Sheets("DATABASE").Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select

    'je réinitialise le contenu
    Selection.ClearContents

    ' => si l'onglet SS est invisible tu peux le laisser inivisble / sauf pendant le debuggage eventuellement
    ' comme ca pas besoin de le rendre invisible !
    ' ==> resultat 4 actions en moins !!!
    Sheets("SS").Visible = False
    Sheets("DATABASE").Visible = False

    ' Exit Sub ne sert a rien du tout a cet endroit !!!

End Sub

James007 a écrit :

Bonjour à tous,

A priori Excellium ... n'a pas l'air très très intéressé par les réponses ...

Re,

@NCC 1701

Que tu aies devant une expression a= ou bien for i= 1 to ne la rend pas beaucoup plus complexe à lire à mon avis.

De plus, soit tu ne t'intéresses pas à la fin de boucle auquel cas c'est réglé, soit tu t'y intéresses et tu es obligé de remonter pour trouver là elle est évaluée et là tu es largement perdant au niveau lisibilité.

D'accord pour une très longue expression pour alléger la lecture, mais là....

Je n'ai pas regardé dans le détail ta proposition mais il manque l'essentiel d'une optimisation : la typage des variables au plus près du besoin.

eric

Ce n'est qu'une piste à suivre eriiic !

Une voie à prendre... il reste à optimiser beaucoup.... mais vu le code... cela ressemble fort à "enregistrer macro"

Un bonjour à tous ! Avec mon petit grain de sel (pour me remettre dans le bain après quelques jours d'absence... )

Je partage l'avis de h2so4 et Eric sur le fait que VBA évalue au démarrage une fois pour toutes le terme de la boucle.

J'ai eu l'occasion d'avoir une discussion sur ce thème il y a quelques temps, et d'en faire la démonstration : une boucle type

For i = 1 to nb (avec nb initialisée à 30000), en cours d'exécution (en début de boucle...) on réinitialise nb à 15000, dons la boucle on sert une variable à la valeur de i. En fin de boucle, on vérifie que la variable a atteint la valeur 30000, et que i vaudra alors 30001, mais nb est bien à 15000 !

Après, il est sûr qu'un nombre de ligne de code plus réduit ne garantit pas une plus grande rapidité d'exécution, il faut voir l'ensemble des éléments en jeu...

Bonne soirée à tous.

Bonjour à tous,

Merci pour vos remarques. C'est compliqué pour moi de vous donner un fichier COMPREHENSIBLE car la totalité des données sont confidentielles.

J'ai noté la remarque concernant cette instruction :

For i = 2 To Sheets("DATABASE").[L65000].End(xlUp).Row

A priori, elle serait évaluée qu'une seule fois -> donc je laisse comme ça ?

Je reviens vers vous que maintenant car je n'étais pas chez moi ce weekend (donc pas de PC sous la main )

Merci pour votre retour,

Cdt,

Excellium

12tableau.zip (297.26 Ko)
Rechercher des sujets similaires à "optimiser select"