Optimisation de la vitesse d'exécution

Bonjour à toutes et à tous,

Voilà quelques semaines que je construis et utilise un fichier Excel permettant de piloter l'intégration du REX dans la section où je me trouve. Je suis totalement novice en la matière et ai tout appris sur ce forum. Même si le fichier n'est en soi pas extrêmement compliqué, il m'aura fallu apprendre pas mal de notions. Je dois avouer arriver à mes limites (sans commencer à passer beaucoup trop de temps sur ce fichier, qui n'est qu'une mission annexe à mon travail). J'aimerais un peu connaître vos pistes d'amélioration sur la structure du fichier, qui permettraient de fluidifier l’exécution du programme. J'ai déjà fait des recherches et ai essayé d'améliorer comme je le pouvais mais cela n'est pas suffisant. Comme tout travail mérite à mes yeux rémunération (même pour des passionnés), je comprendrai que vous ne souhaitiez pas y passer trop de temps gratuitement (surtout si l'optimisation implique de revoir totalement la structure de mon code VBA).

Je vous mets mon fichier en P.J et reste à votre disposition pour avancer sur ce point à vos côtés. Il va de soi que je ne vous demande pas de faire le travail à ma place, simplement de m'indiquer par où prendre le problème en sachant que de base je n'ai jamais programmé.

Merci par avance et bonne journée !

Bonjour,

Juste pour info, il faut déclarer les variables une à une, sans quoi elles sont de type Variant, et donc ainsi:

Code:

---

1 2	Dim i As Integer, j As Integer Dim derligf6 As Long, derligrex As Long

---

Pour avoir un code plus rapide, voici un exemple pour le formulaire Extraction_REX:

Code:

---

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

Private Sub CommandButton1_Click()   ' Enlever les filtres Dim Sh As Worksheet For Each Sh In ThisWorkbook.Worksheets If Sh.FilterMode Then 'Si on ne voit pas toutes les données Sh.ShowAllData End If Next ' Fin enlever les filtres   If MsgBox(CB_Pilote.Value & " - Confirmer ?", vbYesNo, "Confirmation") = vbNo Then Exit Sub Application.ScreenUpdating = False With Sheets("Tmp_Extraction") .Range("B1") = CB_Pilote.Value .Range("A4:H6500").Clear End With Sheets("Suivi REX Gammes - Constats").Select '--- par sécurité Range("A:A,E:F,H:H,K:K").EntireColumn.Hidden = True Range("1:1").EntireRow.Hidden = True Application.CopyObjectsWithCells = False '--- pour ne pas copier les boutons With ActiveSheet.ListObjects("TabRex").Range .AutoFilter Field:=11, Criteria1:=CB_Pilote.Value .SpecialCells(xlCellTypeVisible).Copy Sheets("Tmp_Extraction").Range("A4") .AutoFilter Field:=11 End With Range("A:K").EntireColumn.Hidden = False Range("1:1").EntireRow.Hidden = False Sheets("Tmp_Extraction").Select Application.CopyObjectsWithCells = True Application.ScreenUpdating = True Unload Extraction_REX End Sub

---

Cordialement.
P.S. Corrigé ligne (ligne 25 remplace ex ligne 28 ShowAllData)

Bonjour,

Je n'avais pas reçu de notification pour cette réponse, veuillez m'excuser !

Merci beaucoup pour cette proposition, la vitesse d’exécution est incroyable en comparaison avec mon ancien programme. Pourriez-vous, s'il vous plaît, m'expliquer quels sont les points où je faisais mal ? Les points qui ralentissaient l’exécution du code. J'ai l'impression que c'était parce que j'essayais d'analyser ligne par ligne, pour ensuite exécuter une action en conséquence.

Cordialement.

Bonjour,

c'est effectivement le point qu'a modifié Eric.

Tu bouclais ligne par ligne, et récupérais les lignes sur lesquelles, en colonne 11, on avait la valeur recherchée.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

For i = 2 To derligrex If Rex.Cells(i, 11) = Extraction_REX.CB_Pilote.Value Then j = j + 1 With Rex .Cells(i, 2).Copy Feuil6.Cells(j, 1) .Cells(i, 3).Copy Feuil6.Cells(j, 2) .Cells(i, 4).Copy Feuil6.Cells(j, 3) .Cells(i, 7).Copy Feuil6.Cells(j, 4) .Cells(i, 9).Copy Feuil6.Cells(j, 5) .Cells(i, 10).Copy Feuil6.Cells(j, 6) .Cells(i, 12).Copy Feuil6.Cells(j, 7) .Cells(i, 13).Copy Feuil6.Cells(j, 8) End With End If Next

---

Eric utilise un filtre automatique basé sur ce critère, et copie la plage de résultat du filtre en une passe :mrgreen:

Code:

---

1 2 3 4 5

With ActiveSheet.ListObjects("TabRex").Range .AutoFilter Field:=11, Criteria1:=CB_Pilote.Value .SpecialCells(xlCellTypeVisible).Copy Sheets("Tmp_Extraction").Range("A4") .AutoFilter Field:=11 End With

---

Merci pour ces précisions !!!

J'aurai qqes questions supplémentaires :
- Je ne comprends pas vraiment à quoi servent ces deux lignes. Pourquoi cherche-t-on à cacher le reste du tableau, si on cantonne notre recherche (et nos filtres) à la colonne 11 juste après ?

Code:

---

1 2	Range("A:A,E:F,H:H,K:K").EntireColumn.Hidden = True Range("1:1").EntireRow.Hidden = True

---

- J'avais pu lire à droite et à gauche que pour optimiser la vitesse d'exécution d'un programme il fallait prendre pour habitude de stocker les choses que l'on souhaite déplacer dans un tableau "temporaire", pour par la suite les exploiter. Premièrement est-ce vrai pour tous les programmes ? Ensuite, je ne vois pas trop comment je pourrais l'appliquer ici. Je n'ai jamais eu l'occasion de manier les tableaux de la sorte.

- Enfin, j'avais un peu fait un "fourre-tout" de ces lignes là que j'avais trouvé à droite et à gauche, lesquelles sont vraiment pertinentes au regard de mes recherches ? Lesquelles serait-il essentiel de prendre pour habitude de mettre dans mes programmes ? (s'il y en a)

Code:

---

1 2 3 4 5 6 7

Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' mon code Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

---

Merci pour l'aide apporté !

Cordialement.

Bonjour,

Citation:

---

Envoyé par Elodiums

- Enfin, j'avais un peu fait un "fourre-tout" de ces lignes là que j'avais trouvé à droite et à gauche, lesquelles sont vraiment pertinentes au regard de mes recherches ? Lesquelles serait-il essentiel de prendre pour habitude de mettre dans mes programmes ? (s'il y en a)

Code:

---

1 2 3 4 5 6 7

Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' mon code Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

---

---

L'utilisation généralisée de ces lignes n'est pas du tout pertinente : il faut les utiliser avec parcimonie, uniquement lorsqu'elles sont utiles.
En particulier EnableEvents=False qui ne doit être utilisé que durant le court laps d'instructions qui génèrent les évènements à désactiver et doit être rétabli aussitôt après, quitte à être répété autant de fois que nécessaire.

Salut

Citation:

---

Envoyé par Patrice740

[...]
En particulier EnableEvents=False[...]

---

J'ajouterai que Application.EnableEvents ne peut être utilisée qu'avec une bonne gestion d'erreur pour rétablir la valeur à True en sortie de code, sinon elle restera à False en cas de plantage et il n'y aura plus d'écoute des évènements.

A minima le code suivant:

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Sub Test() On Error GoTo Catch   Application.EnableEvents = False ... ... ...   Catch: Application.EnableEvents = True End Sub

---

Pour ce qui est du mode de calcul, je n'aime pas trop le remettre en automatique en fin de macro. Qui dit qu'il l'était en début? Perso, j'utilise une procédure de mon xlTools pour switcher sur les différents modes de calcul, illustrée ici avec une procédure qui l'utilise

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub Test() SwitchCalculationMode xlCalculationManual   SwitchCalculationMode End Sub     Sub SwitchCalculationMode(Optional CalculationMode As XlCalculation) Static PreviousMode As XlCalculation   If CalculationMode = 0 Then If PreviousMode <> 0 Then Application.Calculation = PreviousMode Else Application.Calculation = xlCalculationAutomatic End If Else PreviousMode = Application.Calculation Application.Calculation = CalculationMode End If End Sub

---

Bonjour,

Merci pour tous ces éléments, c'est déjà plus clair pour moi et grâce à la réponse de EricDgn, j'ai pu enlever toute latence de mon fichier.

Je vais mettre ce sujet sur "résolu", il a permis de répondre à mes attentes.

Bonne journée à vous.

Bonjour,

Je me permets de rouvrir ce sujet. J'ai essayé d'adapter ce qui m'avait été proposé et je rencontre un problème.

Code:

---

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

Sub Nettoyer_REX_Sans_suite() ' Enlever les filtres Dim Sh As Worksheet   For Each Sh In ThisWorkbook.Worksheets If Sh.FilterMode Then 'Si on ne voit pas toutes les données Sh.ShowAllData End If Next ' Fin enlever les filtres   Application.ScreenUpdating = False Sheets("Suivi REX Gammes - Constats").Select Range("1:1").EntireRow.Hidden = True Application.CopyObjectsWithCells = False   With ActiveSheet.ListObjects("TabRex").Range .AutoFilter Field:=8, Criteria1:="Sans suite" .SpecialCells(xlCellTypeVisible).Copy Sheets("Archives_REX Sans suite").Range("A2") .AutoFilter Field:=8 End With   Range("1:1").EntireRow.Hidden = False Sheets("Archives_REX Sans suite").Select Application.CopyObjectsWithCells = True Application.ScreenUpdating = True   End Sub

---

Le code ci-dessus à pour objectif d'enlever une plage de données ayant pour intitulé "Sans suite" dans la colonne 8. Je n'ai pour le moment pas mis de .clear pour ne pas perdre les données, mais je n'arrive pas à les copier. Il me sort une erreur sur la ligne suivante :

Code:

---

.SpecialCells(xlCellTypeVisible).Copy Sheets("Archives_REX Sans suite").Range("A2")

---

Le message est le suivant :

Citation:

---

"Pas de cellules correspondantes"

---

Je ne comprends pas, j'ai simplement transposé ce que m'avait transmis Eric auparavant dans cette conversation (qui marche très bien sur mon fichier).

Je vous remercie par avance.

Bonne journée.

Bonjour,

C'est le problème lors qu'on s'affranchit des vérifications élémentaires :
Avant d'effectuer une action sur .SpecialCells(xlCellTypeVisible), il faut s'assure que la plage existe (Not Is Nothing) !!!

De quelle plage parle-t-on ? J'ai justement vérifié, il affiche bien la zone filtrée mais il n'arrive pas à la copier. La destination est également libre, A2 est une case vide et la plage alentour également. Je ne gère peut être pas bien la propriété "xlCellTypeVisible". J'ai tout fait pour que les conditions soient les mêmes que dans la correction d'Eric, mais sans succès. J'avoue ne pas trop savoir où chercher. C'est une méthode que je ne connaissais pas de base et même après lecture et relecture sur plusieurs forums, c'est un peu complexe pour moi. L'erreur est peut être grosse mais je ne la vois pas.

Citation:

---

Envoyé par Elodiums

De quelle plage parle-t-on ?

---

Comme c'est déjà dit, je parle de la plage .SpecialCells(xlCellTypeVisible)

Le contrôle élémentaire :

Code:

---

1 2 3 4 5 6 7 8

Dim r As Range '... With ActiveSheet.ListObjects("TabRex").Range .AutoFilter Field:=8, Criteria1:="Sans suite" Set r = .SpecialCells(xlCellTypeVisible) If Not r Is Nothing Then r.Copy Sheets("Archives_REX Sans suite").Range("A2") .AutoFilter Field:=8 End With

---

Merci pour ces précisions. Je suis désolé mes questions sont peut être bêtes mais est-il possible d'avoir des explications sur ces lignes de code ? Je ne connaissais pas du tout ce contrôle.

En ajoutant ces lignes j'ai toujours le même message, comment dois-je l’interpréter ? Que le contrôle montre que j'ai pas de plage et que donc j'ai mal codé quelque chose ?

Merci par avance.

Bonjour,

Citation:

---

Je ne connaissais pas du tout ce contrôle.

---

La méthode SpecialCells renvoie un objet Range et pour découvrir les différents arguments de cette méthode, il suffit d'utiliser l'enregistreur de macros avec la boîte de dialogue Atteindre qui outre les cellules visibles, peut sélectionner, les cellules contenant

• des formules
• des constantes
• des mises en forme conditionnelles
• des validations de données
• etc

Voir ces billets

• Excel – La boîte de dialogue « Atteindre »
• Comment atteindre rapidement les cellules contenant une formule dans Excel

Bonjour,

J'ai sans doute cherché le problème au mauvais endroit. Merci pour ces précisions, je vais regarder avec plus d'attention ce contrôle et les liens transmis.

Edit : J'ai trouvé le problème, la zone sélectionnée était en réalité pas intégrée à mon tableau et ne devait donc pas correspondre au format traité par la méthode SpecialCells. En ajoutant manuellement cette plage de données dans le tableau j'ai résolu le problème. Sans doute un copier/coller mal effectué par quelqu'un. Merci pour les liens c'est en lisant le second que j'ai eu cette piste.

Cordialement,

Bonjour,

Je rencontre un problème avec le contrôle mentionné plus haut dans cette conversation.
Grâce au code suivant, je cherche à sélectionner une plage de données et agir dessus si la colonne 8 est égale à "Traité".

Code:

---

1 2 3 4 5 6 7 8 9

Dim r as Range ... With ActiveSheet.ListObjects("TabRex").Range .AutoFilter Field:=8, Criteria1:="Traité" Set r = .SpecialCells(xlCellTypeVisible) If Not r Is Nothing Then r.Copy Sheets("Archives_REX Traité").Range("A" & derlig) r.Delete .AutoFilter Field:=8 End With

---

Le problème, c'est que quand il n'y a aucune donnée de type "Traité" le programme plante ("Pas de cellules correspondantes"). Je ne comprends pas, le "If Not r Is Nothing Then" n'est pas censé résoudre ce problème ?

Merci par avance.

Cordialement,

Bonjour

Mon grain de sel : toujours conserver en mémoire le dernier état des propriétés; ne pas présumer que EnableEvents était sur True p.ex. car dans un logiciel complexe, une méthode peut en appeler une autre et si, cette autre force EnableEvents à true; au revenir dans la première méthode, les évènements sont activés ce qui n'est peut-être pas souhaité.

Perso, c'est toujours ainsi que je fais:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Dim xlMode As XlCalculation Dim bEvents As Boolean, bScreenUpdating As Boolean     xlMode = Application.Calculation Application.Calculation = xlCalculationManual     bEvents = Application.EnableEvents Application.EnableEvents = False     bScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = bScreenUpdating     ' ... Put your code here ...     Application.ScreenUpdating = bScreenUpdating Application.EnableEvents = bEvents Application.Calculation = xlMode

---

Salut Christophe,

:plusser: :plusser:

Je l'ai encore précisé hier dans cette discussion. Perso, j'ajoute une gestion d'erreur pour être certain de repasser par la case "remise comme c'était avant".

J'avoue que c'est un peu trop poussé pour moi ces notions. Je ne comprends pas exactement où vous voulez en venir et comment l'utiliser pour résoudre le problème que j'ai dans le cas où ma variable "r" serait vide, car pas de données de type "Traité " dans mon tableau.

J'ai également lu la discussion dont vous parlez Pierre et à nouveau, cela dépasse ce que je connais de VBA (et je n'ai malheureusement pas le temps de m'y plonger dedans, même si ce n'est pas l'envie qui manque).

Merci par avance.