Optimiser le temps d'éxecution d'une macro

Version imprimable

je voudrai bien que vous m'aidez à optimiser le temps d’exécution de la macro suivante qui ' Copie depuis un tableau la premiere ligne puis elle met le resultat de cette ligne ( qui est dans une autre feuille) dans une feuille appelé Feuil2 et puis elle pase à la ligne suivante et agrège les résultats de chaque ligne dans cette feuille , ceci tout en calculant le temps écouplé , le temps restant d'éxecution et le temps total estimé , et renvoie ) la fin le temps effectivement écoulé .
Voila le code :

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
34
35
36
37
38
39
40
Sub ElapsedTime()
   Dim StartTime As Double, EndTime As Double
   'Stores start time in variable "StartTime"
   StartTime = Timer
   'Aggregation"
   i = 2
   j = 2
   k = 4
 
   Application.ScreenUpdating = False
   For k = 8 To 11517
    Application.StatusBar = (k - 8) * 100 / 11509 & "%" & "  " & Format$(Timer - StartTime) & "s ecoulées " & "    Temps restant estimé :" & (11517 - k) * Format$(Timer - StartTime) / k & "s" & "Durée total d'éxecution " & Format$(Timer - StartTime) + (11517 - k) * Format$(Timer - StartTime) / k
    DoEvents
 
    Sheets("MP").Select
    Rows(k).Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows(3).Select
    ActiveSheet.Paste
 
    For i = 2 To 100
        For j = 2 To 36
        Sheets("Feuil1").Select
        Cells(i, j) = Cells(i, j) + Worksheets(6).Cells(i, j).Value
 
 
        Next j
    Next i
Next k
 
 Application.StatusBar = False
 Application.ScreenUpdating = True
   'Stores end time in variable "EndTime"
   EndTime = Timer
   'Prints execution time in the debug window
   Debug.Print "Execution time in seconds: ", EndTime - StartTime
'Shows Message Box with elapsed time
   MsgBox "Execution time in seconds: " + Format$(EndTime - StartTime)
End Sub

01/08/2018, 12h27
a_diard
essaye de supprimer les select

remplacer
Code:

1 2 3 4 5 6 Sheets("MP").Select Rows(k).Select Application.CutCopyMode = False Selection.Copy Rows(3).Select ActiveSheet.Paste
par

Code:

Sheets("MP").Range("A" & k & ":d" & k).Copy ActiveSheet.Range("a3:d3")

à adapter au nombre de colonnes à copier

ajouter application.screenupdating
01/08/2018, 13h42
Othmane.

Merci , j'ai essayé mais ceci ne réduit pas le temps d'exécution c'est presque le même :( .
01/08/2018, 14h00
halaster08
Bonjour,
Code:

1 2 3 4 5 6 7 8 For k = 8 To 11517 [...] For i = 2 To 100 For j = 2 To 36 [...] Next j Next i Next k
11510*99*35=39.882.150, tu écris donc cellule par cellule dans près de 40 millions de cellules, ça t'étonnes vraiment que ce soit long ?

Je n'ai peut être pas bien compris comment fonctionne ta macro mais dans tes boucles for i et j je ne vois aucune référence à k, est ce normal ? As tu vraiment besoin de les mettre dans la boucle k ?
01/08/2018, 14h07
Othmane.

En fait moi ça m'étonne pas mais mon encadrant ( je suis en stage ) m'a dit qu' il y a certainement moyen pour minimiser le temps il m'a proposé de désactiver les calculs automatiques quand je copie mes lignes .Mais sinon , dans chaque itération de K je dois actualiser 100*30 Cellules ( j’agrège les résultats ) et ensuite je passe au K suivant parce que quand je change de ligne y'a 2 feuilles de calcul qui s'actualisent en fonctino des valeurs de cette ligne tu vois ? donc oui les 2 boucles doivent impérativement être dans la boucle K .

Bonjour,

Il y a quelques aberrations dans ton code alors que tu souhaites optimiser la durée !
Il faut savoir que VBA est du code interprété donc chaque ligne est lue par le compilateur et exécutée donc, la ligne ci-dessous ralentit inutilement le code puisque le compilateur doit appeler des fonctions (Format, Timer) et effectuer des calculs et ça, 11517 fois :
Code:

1 2 Application.StatusBar = (k - 8) * 100 / 11509 & "%" & " " & Format$(Timer - StartTime) & "s ecoulées " & " Temps restant estimé :" & (11517 - k) * Format$(Timer - StartTime) / k & "s" & "Durée total d'éxecution " & Format$(Timer - StartTime) + (11517 - k) * Format$(Timer - StartTime) / k
L'instruction "DoEvents" permet de laisser la main donc, le compilateur fait une pause pour que des actions puissent être exécutées par Excel.
La copie de valeurs (ici, tu demande la ligne entière alors que tu as probablement que quelques dizaines de cellules) prend plus de temps que l'affectation de valeurs.
Si tu as de nombreuses fonction dans tes feuilles, il serait préférable de paramétrer le calcul en manuel en début de code et le remettre en automatique en la fin.
Et toujours sur un grand nombre de valeurs il est préférable d'affecter ces valeurs à un tableau en mémoire puis une fois les modifications apportées directement dans le tableau, coller ce dernier dans la feuille plutôt que de travailler directement sur les cellules.

01/08/2018, 14h12
Theze
Re,

Et surtout, il te faut éviter absolument tous les Select et Activate qui ne servent à rien si ce n'est à ralentir le code et faire scintiller l'écran !

Dans ce bloc de code :
Code:

1 2 3 4 5 6 7 Sheets("MP").Select Rows(K).Select Application.CutCopyMode = False Selection.Copy Rows(3).Select ActiveSheet.Paste
qui peux s'écrire simplement :
Code:

1 2 Sheets("MP").Rows(K).Copy ActiveSheet.Rows(3)
tu colles plus de 11000 fois les valeurs sur la même ligne, à savoir la ligne 3 ???

Citation:
Envoyé par Theze
Bonjour,

Il y a quelques aberrations dans ton code alors que tu souhaites optimiser la durée !
Il faut savoir que VBA est du code interprété donc chaque ligne est lue par le compilateur et exécutée donc, la ligne ci-dessous ralentit inutilement le code puisque le compilateur doit appeler des fonctions (Format, Timer) et effectuer des calculs et ça, 11517 fois :
Code:

1 2 Application.StatusBar = (k - 8) * 100 / 11509 & "%" & " " & Format$(Timer - StartTime) & "s ecoulées " & " Temps restant estimé :" & (11517 - k) * Format$(Timer - StartTime) / k & "s" & "Durée total d'éxecution " & Format$(Timer - StartTime) + (11517 - k) * Format$(Timer - StartTime) / k
L'instruction "DoEvents" permet de laisser la main donc, le compilateur fait une pause pour que des actions puissent être exécutées par Excel.
La copie de valeurs (ici, tu demande la ligne entière alors que tu as probablement que quelques dizaines de cellules) prend plus de temps que l'affectation de valeurs.
Si tu as de nombreuses fonction dans tes feuilles, il serait préférable de paramétrer le calcul en manuel en début de code et le remettre en automatique en la fin.
Et toujours sur un grand nombre de valeurs il est préférable d'affecter ces valeurs à un tableau en mémoire puis une fois les modifications apportées directement dans le tableau, coller ce dernier dans la feuille plutôt que de travailler directement sur les cellules.
Merciii beaucoup , j'ai tout essayé j'ai pas gagné beaucoup de temps mais bon je dirai 20 sec sur 400 lignesau lieu de 250 sec , maint c'est 230 .

01/08/2018, 16h47
Othmane.

Citation:

Envoyé par Theze

tu colles plus de 11000 fois les valeurs sur la même ligne, à savoir la ligne 3 ???

Oui oui parce que les calculs effectués dans les 2 autres feuilles font référence à la ligne 3. Donc une fois j'ai terminé avec une ligne dans mon tableau je passse à la ligne suivante en la copaint dans la ligne 3 .