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