Exécution VBA 5 fois plus rapide si on clique en dehors de la fenêtre Excel

Version imprimable

Bonjour,

Un utilisateur m'a soumis ce cas. Une macro met 8 - 10 sec pour s'exécuter et 1,5 sec si on clique en dehors de la fenêtre Excel. Le code est le suivant (tel quel, mais le problème est le même après optimisation) :

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
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
Sub Aplatisseur()
 
  Dim f As String
  Dim pos, Diviseur As Double
 
  f = "SAISIE DONNÉES"
  pos = 2  'la ligne où on écrit dans DATA
 
  Sheets("DATA").Activate
  Rows("2:1000000").Delete
  ActiveSheet.UsedRange
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
 
  For i = 5 To Sheets(f).Range("C1000000").End(xlUp).Row
 
    Application.StatusBar = "Compilation..." & Round((i / Sheets(f).Range("C1000000").End(xlUp).Row) * 100, 0) & "%"
 
    For j = 26 To 175 'colonnes numériques
 
     'Pour les lignes avec classifications et sans erreur, quand il y a un montant (volume, tarif, $$)
      If Sheets(f).Columns(j).EntireColumn.Hidden = False And _
         Sheets(f).Cells(i, j) <> 0 And Sheets(f).Cells(i, j) <> "" And _
         Sheets(f).Range("FV" & i) <> "" And Sheets(f).Range("FT" & i) = "" Then
 
        Range("V" & pos) = IIf(Sheets(f).Range("Y" & i) = "Flux monétaire", "FLUX MONÉTAIRE", "IMPACT RÉSULTATS") 'Vue
 
        Range("W" & pos) = Sheets(f).Cells(4, j) 'Année du montant
 
        If j >= 26 And j <= 50 Then
          Range("X" & pos) = Sheets(f).Cells(i, j) 'volumes
        End If
 
        If j >= 51 And j <= 75 Then Range("Y" & pos) = Sheets(f).Cells(i, j) 'tarif
 
        If j >= 76 And j <= 100 Then                 'mois projet
          Range("Z" & pos) = "PROJET"
          Range("AA" & pos) = Sheets(f).Cells(i, j)
        End If
 
        If j >= 101 And j <= 125 Then                '$$ projet
          Range("Z" & pos) = "PROJET"
          Range("AB" & pos) = Sheets(f).Cells(i, j)
        End If
 
        If j >= 126 And j <= 150 Then                'mois oper
          Range("Z" & pos) = "OPÉRATIONNEL"
          Range("AA" & pos) = Sheets(f).Cells(i, j)
        End If
 
        If j >= 151 And j <= 175 Then                '$$ oper
          Range("Z" & pos) = "OPÉRATIONNEL"
          Range("AB" & pos) = Sheets(f).Cells(i, j)
        End If
 
       'Récupérer le reste de la ligne
        For k = 4 To 24
          Cells(pos, k - 3) = Sheets(f).Cells(i, k).Value
        Next
 
        Range("C" & pos) = Sheets(f).Range("FV" & i)
 
        pos = pos + 1
 
      End If
 
    Next
 
  Next
 
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True
 
  Application.StatusBar = ""
 
End Sub

Avez-vous jamais rencontré un tel cas ?

Cordialement.

Daniel

21/03/2018, 20h31
patricktoulon

re

re
oui j'ai deja vu ca

screenupdating false fait gagner du temps mais le buffer (tampon )lui est occupé tandis que la fenetre déactivé le buffer doit rester vide

ca fait comme une sorte de screenupdating=xlveryFalse:mouarf:
d'ailleur tu le constate aussi quand tu minimise l'application juste avant ;)
21/03/2018, 22h02
Daniel.C

Bonsoir Patrick,

Merci de ta réponse. Mais c'est hyper-violent, ça devrait être obligatoire !

Cordialement.

Daniel
21/03/2018, 23h10
patricktoulon

re

oui je supose que le fait de figer l'ecran avec screenupdating doit manger un peu de ressource si en plus la fenetre est déactivée

j'ai fait quelques recherche je suis tombé sur certaines explications qui me parraissent logique de mon point de vue

mais ca remettrait en cause l'utilisation de cette astuce

en effet selon le PC memoire graphique dédiée ou pas le comportement serait different et pas que pour excel
a creuser ;)
21/03/2018, 23h43
eriiic

Bonjour,

il y avait eu un fil sur ce pb constaté sur excel 2016.
De mémoire changer le curseur souris en sablier le temps de la macro permettait de retrouver un temps correct, à l'identique que de sortir la souris de la fenêtre (le clic ne semblait pas nécessaire).
eric

edit :
j'ai retrouvé, c'était sur un autre forum.
En résumé :

Code:

Application.Cursor = xlWait

Rétablir avec Application.Cursor = xlDefault
22/03/2018, 08h30
patricktoulon

re

re
Bonjour eriic
interessant mais attention au leure masquer le visuel du probleme ne veux pas dire que ca tourne pas en arriere plans il faut verifier avec une macro un peu lourde et un timer
22/03/2018, 08h59
eriiic

N'ayant ni 2013, ni 2016 je ne peux tester.
Mais le demandeur n'était pas un lapin de 3 mois et il a confirmé l'amélioration très nette dans son cas.
eric
22/03/2018, 09h05
unparia

Bonjour à tous
Les termes

Citation:

si on clique en dehors de la fenêtre Excel

souffrent d'une carence de précision. Cliquer où, plus précisément ?

Je voudrais en ce qui me concerne rappeler quelques aspects :
- Windows n'est pas un champion en matière de gestion de la mémoire, notamment (et surtout) lorsque l'occupation de la mémoire virtuelle est déjà telle qu'il doit utiliser le fichier de pagination (dont il tente d'augmenter la taille pour faire face, en fonctions des "besoins" qu'il "estime". Et il "estime" par rapport à des "constats" antérieurs de l'utilisation de ceci ou de cela.
Si l'on est déjà en "débordement" (swapping) et que l'on clique sur ce qui (aux yeux de Windows) nécessite beaucoup de mémoire, Windows ajuste alors le fichier de pagination pour faire face à ce qu'il entrevoit être un nouveau besoin. Mais si on ne fait rien d'autre (dans ce qui est cliqué) ce fichier de pagination augmenté bénéficie alors totalement aux traitements de ce qui est actif.
- la mémoire utilisée inclut celle de la gestion des fenêtres affichées

- l'écart des vitesses alors constatées sera de surcroît proportionnel à l'état de fragmentation du volume/disque (accès disque obligeant puisque inévitables si en débordement de mémoire virtuelle)

La présente discussion trouverait sans aucun doute une place plus justifiée dans un autre forum que celui-ci (Windows).

EDIT : j'invite les plus avertis à faire l'expérience suivante :
1) conduire Windows à un débordement de la mémoire virtuelle (-->> utilisation du fichier de pagination)
2) lancer une procédure très gourmande (depuis VBA, par exemple) et impliquant des accès disque (lecture de fichiers texte, par exemple) et chronométrer sa durée d'exécution
3) la relancer à nouveau immédiatement après et constater : elle sera alors exécutée beaucoup plus rapidement
Explication : Windows est intervenu (à l'étape 2) sur le fichier de pagination.
22/03/2018, 09h16
Marc-L

Bonjour !

Daniel, teste aussi en cachant la fenêtre du classeur et sans Select ni Activate dans le code …

___________________________________________________________________________________________________________
Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …
22/03/2018, 10h09
Daniel.C

Merci pour toutes vos réponses qui m'ouvrent des possibilités insoupçonnées. Je vais faire des tests dès que possible (sans doute dimanche).

@MarcL je confirme
le application.visible false fait gagner quelque centiemes avec ce teste simple
Code:

1 2 3 4 5 6 7 8 9 Sub test() Application.Visible = False t = Timer For i = 1 To 65000 Cells(i, 1) = "toto" Next Debug.Print Timer - t Application.Visible = True End Sub

Bonjour,

je me permet une petite remarque sur le code posté par Daniel.C :

dans ce code on vois que cette partie du code :
Code:

1 2 3 Sheets("DATA").Activate Rows("2:1000000").Delete ActiveSheet.UsedRange
arrive avant cette partie :
Code:

1 2 3 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False
or
Code:

1 2 3 Sheets("DATA").Activate 'cette ligne provoque un rafraîchissement de l'affichage !!! Rows("2:1000000").Delete 'cette ligne provoque un important recalcule du classeur et donc une mise à jour de l'affichage, conséquent elle aussi !!! ActiveSheet.UsedRange
donc oui je ne suis pas étonné que l’exécution soit un peu plus rapide quand excel n'a pas le focus.
Il aurait été judicieux d'inverser ces 2 parties du code et de supprimer le "activate" qui ici ne sert à rien !

22/03/2018, 17h03
Daniel.C

Bonjour,

Exact aussi. Cependant, je dois optimiser le code. Je reporte à dimanche.