Discussion :

[Ltspitfire]

Bonjour,

J'ai créé une macro qui rempli un tableau à partir des informations contenues dans deux autres feuilles du même classeur. La macro fonctionne, pas de soucis de ce coté la. De même lorsqu'il y a peu de colonne/ligne comme dans l'exemple que je fais suivre, la macro est instantanée. Le problème survient lorsque je passe à un nombre important de colonne/ligne. En effet la macro met 2 minutes à s'executer et j'aimerais porter ce temps d'execution à une dizaine de seconde dans le meilleur des cas. J'ai donc longuement chercher à optimiser mon code à l'aide des conseils trouvés sur plusieurs forum : Enlever un maximum de select, application.sreenupdating = false , etc.

La lenteur provient de la deuxième partie de la boucle contenue dans la fonction "TraitementDates".

Je reste à votre disposition pour toutes informations complémentaires.

Merci d'avance.

Cordialement,
Ltspitfire

[Nico Chg]

Bonjour,

Je n'ouvre que très rarement les pièces jointes, peux tu poster ton code (celui de ta boucle, tout du moins), sur le forum ?
Sinon, une très bonne méthode d'optimisation, en ce qui concerne les boucles, reste les variables tableaux.

Je ne connais pas le détails de tes fonctions, mais je sais que boucler dans une variable tableau met beaucoup moins de temps que de boucler sur un objet Worksheet.

D'expérience personnel, tu peux réduire des temps d’exécution de 15min à quelques secondes.

Pour plus d'information :
Tutoriel de SilkyRoad

[Ltspitfire]

Re,

Voila la fonction responsable de la lenteur :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
Public Function TraitementDates(DateSyn, NomNewSheet)
    DateSynF = Format("01/" & DateSyn, "MM/YYYY")
    DerLi = Worksheets(NomNewSheet).Range("A" & Rows.Count).End(xlUp).Row
    DerLiTable = Worksheets("Table").Range("E" & Rows.Count).End(xlUp).Row
    Worksheets("BDD").Activate
    PremDomBDD = PremDom()
    DernColBDD = Range("XFD9").End(xlToLeft).Column
    Worksheets(NomNewSheet).Activate
    y = 0
    ' Parcours de chaque agent
    While y <= DerLi - 6
        If CDate(Worksheets("BDD").Range("D10").Offset(y, 0).Value) < CDate(DateSyn) And Worksheets("BDD").Range("D10").Offset(y, 0).Value <> "" Then
            GoTo 4
        End If
        x = 0
        ' Remplissage des X
        While x <= 11
            If Worksheets("BDD").Range("E10").Offset(y, x).Value <> "" Then
                DateTest = Format(Worksheets("BDD").Range("E10").Offset(y, x).Value, "MM/YYYY")
                If CDate(DateTest) > CDate(DateSynF) Then
                    Worksheets(NomNewSheet).Range("B7").Offset(y, x).Value = "P"
                    ' Mise en forme de la cellule
                    With Worksheets(NomNewSheet).Range("B7").Offset(y, x)
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                    End With
                Else
                    Worksheets(NomNewSheet).Range("B7").Offset(y, x).Value = "X"
                    With Worksheets(NomNewSheet).Range("B7").Offset(y, x)
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                    End With
                End If
            End If
            x = x + 1
        Wend
        Z = PremDomSyn()
        Dercolonne = Range("XFD5").End(xlToLeft).Column
        ' Remplissage des stages
        ' Parcours de chaque colonne stage de la synthèse
        While Z <= Dercolonne
        LiDebTab = 4
            ' Parcours de chaques lignes de stage de la table
            While LiDebTab <= DerLiTable
                ' Test code stage de la ligne dans la table = code stage de la colonne en question de la synthèse
                If Worksheets("Table").Range("E" & LiDebTab).Value = Cells(6, Z).Value Then
                    ColBDD = PremDomBDD
                    ' Parcours de chaque colonne de la BDD pour voir si l'agent à les CTs requisent pour annimer le stage et son niveau
                    While ColBDD <= DernColBDD
                        ' Test code CTs dans la table = code CTs dans la BDD
                        If Worksheets("Table").Range("F" & LiDebTab).Value = Worksheets("BDD").Cells(8, ColBDD).Value Then
                            ' Test et écrit, dans la cellule de la synthèse, le niveau à l'aide des if suivants et est mis à jour si le niveau est inférieur
                            If Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX") Then
                                Cells(7 + y, Z).Value = "XX"
                                With Cells(7 + y, Z)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .WrapText = False
                                .Orientation = 0
                                .AddIndent = False
                                .IndentLevel = 0
                                .ShrinkToFit = False
                                .ReadingOrder = xlContext
                                .MergeCells = False
                                End With
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX" Or Cells(7 + y, Z).Value = "X") Then
                                Cells(7 + y, Z).Value = "X"
                                With Cells(7 + y, Z)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .WrapText = False
                                .Orientation = 0
                                .AddIndent = False
                                .IndentLevel = 0
                                .ShrinkToFit = False
                                .ReadingOrder = xlContext
                                .MergeCells = False
                                End With
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX" Or Cells(7 + y, Z).Value = "X" Or Cells(7 + y, Z).Value = "P") Then
                                Cells(7 + y, Z).Value = "P"
                                With Cells(7 + y, Z)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .WrapText = False
                                .Orientation = 0
                                .AddIndent = False
                                .IndentLevel = 0
                                .ShrinkToFit = False
                                .ReadingOrder = xlContext
                                .MergeCells = False
                                End With
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value = "" And Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value = "" And Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value = "" Then
                                Cells(7 + y, Z).Value = ""
                                GoTo 5
                            End If
                        End If
                    ColBDD = ColBDD + 3
                    Wend
                End If
            LiDebTab = LiDebTab + 1
            Wend
5        Z = Z + 1
        Wend
4        y = y + 1
    Wend
 
 
End Function

Si le tableau peut me permettre de gagner en efficacité, c'est une bonne idée.

Cordialement,
Ltspitfire

[Nico Chg]

Re-Bonjour,

Pourquoi toutes ces mises en formes de cellules ? Effectivement, ça prend du temps, car Excel recalcule énormément de chose lorsque tu travaille directement sur la feuille, mais lors de mise en forme, pas trop le choix, je pense.

Sinon, je peux te proposer une autre alternative. Tu remplie avec une première macro tes "X" et tes "XX" et autre valeurs, sans mise en forme, puis tu fais une mise en forme conditionnel après ?

[Ltspitfire]

Merci de te pencher sur mon problème.

Je n'ai pas bien compris lorsque tu as dis : "mais lors de mise en forme, pas trop le choix, je pense."

Oui bonne idée, j'essaie en effectuant la mise en forme à la fin et je te redis.

Cordialement,
Ltspitfire

[Ltspitfire]

Re,

En faisant la mise en forme à la fin je gagne 10 secondes.
Je vais devoir passer par des tableaux, je vais regarder le tuto que tu m'as passé.

Comment ferais-tu ?

Cordialement,
Ltspitfire

[deedolith]

Je n'ai pas ouvert ta piece joint non plus, mais pour gagner en vitesse, il vaut mieux bosser avec les valeurs en memoire que directement dans une feuille de calcul.

Esssaye de voir si tu peux copier tes valeurs dans un tableau, puis faire tes traitement, recopier tes valeurs finales dans la feuille et enfin faire la mise en forme.

[Nico Chg]

Re-bonjour,

Je voulais dire que lorsque tu travaille sur les cellules d'Excel directement, en te référant directement à l'objet Worksheet, ta macro va prendre plus de temps à s’exécuter. Notamment parcequ'Excel va calculer plein d'information implicitement liée à la feuille.

Passer par une variable tableau ne va pas faire intervenir l'objet Worksheet d'Excel, mais une variable tableau. Beaucoup moins de calculs et re-calculs à faire, donc, et gain de temps.

Pour continuer à t'aider, j'aimerais bien voir ton code, s'il te plait ?

EDIT : C'est la méthode de Deedolith qu'il faut utiliser, bien évidemment !
Un peu d'aide sur les tableaux en lisant le tutoriel de silkyRoad.

[LaTeXLover]

Il faut faire le moins possible d'appels à un range, commence par mettre les données en tableau, le résultat de même et colle tout d'un coup.

[HopPopPop]

Bonsoir à tous,

Dans ta fonction, tu devrais mettre au tout début

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = False

puis en dernière ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = True

A moins que ces instructions n'aient été écrites avant l'appel de la Function.

[Ltspitfire]

Bonjour et merci de votre aide,

Bonsoir à tous,

Dans ta fonction, tu devrais mettre au tout début

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = False

puis en dernière ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = True

A moins que ces instructions n'aient été écrites avant l'appel de la Function.

Post 1# :

J'ai donc longuement chercher à optimiser mon code à l'aide des conseils trouvés sur plusieurs forum : Enlever un maximum de select, application.screenupdating = false , etc..

Re-bonjour,

Je voulais dire que lorsque tu travaille sur les cellules d'Excel directement, en te référant directement à l'objet Worksheet, ta macro va prendre plus de temps à s’exécuter. Notamment parcequ'Excel va calculer plein d'information implicitement liée à la feuille.

Passer par une variable tableau ne va pas faire intervenir l'objet Worksheet d'Excel, mais une variable tableau. Beaucoup moins de calculs et re-calculs à faire, donc, et gain de temps.

Pour continuer à t'aider, j'aimerais bien voir ton code, s'il te plait ?

EDIT : C'est la méthode de Deedolith qu'il faut utiliser, bien évidemment !
Un peu d'aide sur les tableaux en lisant le tutoriel de silkyRoad.

Je comprend mieux merci.

Mon code est disponible dans le fichier du post 1# et entre balise code dans le post 3#.

Merci du lien je vais essayé de mettre en oeuvre la méthode de Deedolith.

Cordialement,
Ltspitfire

[eric4459]

Bonjour Ltspitfire
Tu pourrais également déclarer tes variables comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Dim BD As Worksheet
Dim BdD As Range
Set BD = ThisWorkbook.Sheets("BDD")
Set BdD = BD.Range("D10")
BdD = BdD.Offset(0)

Et remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 CDate(Worksheets("BDD").Range("D10").Offset(y, 0).Value)

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CDate(BdD.Offset(y, 0).Value)

ou
plus simplement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CDate(BdD.Offset(y, 0)

Si ça n’accélère pas le traitement cela rendra ton code plus lisible.
Eric

[Nico Chg]

Bonjour,

Je voulais dire, ton code avec les modifications, notamment de mise en forme que tu as fait dessus !

[Ltspitfire]

Re,

Merci pour tes conseils avisés eric.

Voici le code modifié mais il n'y a pas encore les modifs tableau :

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
Public Function TraitementDates(DateSyn, NomNewSheet)
 
    DateSynF = Format("01/" & DateSyn, "MM/YYYY")
    DerLi = Worksheets(NomNewSheet).Range("A" & Rows.Count).End(xlUp).Row
    DerLiTable = Worksheets("Table").Range("E" & Rows.Count).End(xlUp).Row
    Worksheets("BDD").Activate
    PremDomBDD = PremDom()
    DernColBDD = Range("XFD9").End(xlToLeft).Column
    Worksheets(NomNewSheet).Activate
    y = 0
    ' Parcours de chaque agent
    While y <= DerLi - 6
        If CDate(Worksheets("BDD").Range("D10").Offset(y, 0).Value) < CDate(DateSyn) And Worksheets("BDD").Range("D10").Offset(y, 0).Value <> "" Then
            GoTo 4
        End If
        x = 0
        ' Remplissage des X
        While x <= 11
            If Worksheets("BDD").Range("E10").Offset(y, x).Value <> "" Then
                DateTest = Format(Worksheets("BDD").Range("E10").Offset(y, x).Value, "MM/YYYY")
                If CDate(DateTest) > CDate(DateSynF) Then
                    Worksheets(NomNewSheet).Range("B7").Offset(y, x).Value = "P"
                Else
                    Worksheets(NomNewSheet).Range("B7").Offset(y, x).Value = "X"
                End If
            End If
            x = x + 1
        Wend
        Z = PremDomSyn()
        Dercolonne = Range("XFD5").End(xlToLeft).Column
        ' Remplissage des stages
        ' Parcours de chaque colonne stage de la synthèse
        While Z <= Dercolonne
        LiDebTab = 4
            ' Parcours de chaques lignes de stage de la table
            While LiDebTab <= DerLiTable
                ' Test code stage de la ligne dans la table = code stage de la colonne en question de la synthèse
                If Worksheets("Table").Range("E" & LiDebTab).Value = Cells(6, Z).Value Then
                    ColBDD = PremDomBDD
                    ' Parcours de chaque colonne de la BDD pour voir si l'agent à les CTs requisent pour annimer le stage et son niveau
                    While ColBDD <= DernColBDD
                        ' Test code CTs dans la table = code CTs dans la BDD
                        If Worksheets("Table").Range("F" & LiDebTab).Value = Worksheets("BDD").Cells(8, ColBDD).Value Then
                            ' Test et écrit, dans la cellule de la synthèse, le niveau à l'aide des if suivants et est mis à jour si le niveau est inférieur
                            If Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX") Then
                                Cells(7 + y, Z).Value = "XX"
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX" Or Cells(7 + y, Z).Value = "X") Then
                                Cells(7 + y, Z).Value = "X"
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value <> "" And CDate(Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value) < CDate(DateSynF) And (Cells(7 + y, Z) = "" Or Cells(7 + y, Z).Value = "XX" Or Cells(7 + y, Z).Value = "X" Or Cells(7 + y, Z).Value = "P") Then
                                Cells(7 + y, Z).Value = "P"
                            ElseIf Worksheets("BDD").Cells(10, ColBDD).Offset(y, 2).Value = "" And Worksheets("BDD").Cells(10, ColBDD).Offset(y, 1).Value = "" And Worksheets("BDD").Cells(10, ColBDD).Offset(y, 0).Value = "" Then
                                Cells(7 + y, Z).Value = ""
                                GoTo 5
                            End If
                        End If
                    ColBDD = ColBDD + 3
                    Wend
                End If
            LiDebTab = LiDebTab + 1
            Wend
5        Z = Z + 1
        Wend
4        y = y + 1
    Wend
 
    DerLi = Range("A" & Rows.Count).End(xlUp).Row
    DerCol = Range("XFD6").End(xlToLeft).Column
 
'    Mise en forme de la cellule
    With Worksheets(NomNewSheet).Range("B7", Cells(DerLi, DerCol))
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
 
End Function

[Nico Chg]

Re-bonjour,

Alors après avoir regarder un peu ton code, je voulais savoir pourquoi tu utilise des While / Wend (et surtout des GoTo !) plutôt que des boucle For ?

Je code extrêmement peu avec des while, et encore moins avec des Wend

L'intégration d'un tableau est simplifié lors de boucle For (on parcours habituellement un tableau avec un For). Pour l'utilisation des GoTo, je pense que c'est plutôt pour de la gestion d'Erreur et des Try /catch que des sauts dans une boucle pour moi. Et si tu veux vraiment utiliser des GoTo, tu devrais donner des vrai noms à tes sauts, plutôt que "4" et "5", pour plus de clarté.

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
Public Function TraitementDates(DateSyn, NomNewSheet)
'[...]
 
'Parcours de chaque agent
For y =0 to  DerLi - 6
   '[...]
   For x = 0 to 11
      '[...]
   Next x
   '[...]
   For z = PremDomSyn() to Dercolonne 
      For LiDebTab = 4 to DerLiTable
         If ...
            '[...]
            For ColBDD = PremDomBDD to DernColBDD step 3
               '[...]
            Next ColBDD
         End if
      Next LiDebTab
   Next z
Next y

Une idée de ton code avec une boucle For.

Ceci dit, si tu préfère les While / Wend, ça marche très bien avec un tableau aussi.

EDIT : Au temps pour moi, je n'avais pas vu l'initialisation de z !

[Ltspitfire]

Re,

J'adapte ton code, je le test et je te dis
Merci beaucoup pour ton aide.

Les GoTo me permettent de sortir des boucles pour gagner de précieuses secondes.
Pour ce qui est des While c'est plus une question d'habitude. (Je n'ai jamais utilisé les tableaux donc for et while je vois pas la différence)

Le z est initialisé par la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Z = PremDomSyn()

[Nico Chg]

Pour sortir d'une boucle, d'une instruction, ou de ce que tu veux, tu peux faire comme tout le monde, et suivre les panneaux Exit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
For I = 1 to 1000
   If I = 5 then Exit for
   Msgbox I
Next I

Et pour l'explication, For c'est "Pour I valant 0 Jusqu'a I valant 1000" donc il va faire des opérations avec I, et l'incrémenter à chaque tour de boucle de 1 par défaut. (tu peux lui spécifier l'incrément avec step)

While, c'est "Tant que I est inférieur à 1000". Tu va rester dans la boucle Tant que I est plus petit que 1000. Il ne faut pas oublier d'incrémenter I toi même.

[Ltspitfire]

Re,

Merci pour ces informations très utiles.

Je sais que j'en demande beaucoup mais j'ai énormément de mal avec les tableaux ( même avec le tuto ).
J'ai beau me creuser la tête rien ne fonctionne.

Comment ferais-tu pour modifier ma macro avec des tableaux ?

[Nico Chg]

Re-bonjour,

Et bien là comme ça, je ne sais pas, est ce que tu pourrais upload une photo de ce que tu veux faire ? Pour que l'on puisse se faire une idée de ce qu'il y à a faire ?

Sinon, de façon rapide, je créerais un tableau contenant toute ta plage de données, et je travaillerais dedans de la même façon que tu le fais sur ta feuille. On peut certainement optimiser encore, mais je vais me pencher sur le problème plus en avant.

Après, est ce que tu cherche seulement une réduction du temps de la macro, et garder ton algorithme comme tu l'a fait, ou tu veux aussi l'optimiser ?

Je vais me pencher sur ton problème cet après-midi, je te tiens au courant

[Ltspitfire]

Re,

Voici une photo du resultat ( que tu retrouves dans la pièce jointe post 1#).

Après, est ce que tu cherche seulement une réduction du temps de la macro, et garder ton algorithme comme tu l'a fait, ou tu veux aussi l'optimiser ?

Dans le meilleur des cas ca serait de garder l'algo mais de le transposer avec des tableaux. Le princial est de réduire le temps de la macro, après si cela doit passer par une optimisation, soit.

Merci beaucoup pour l'aide que tu m'apportes.