Discussion :

[P4board]

Bonjour, après avoir difficilement lâché Excel 2003 puis 2010 pour passer à la 2019 depuis 2-3 ans, j'avais vu que mes scripts VBA étaient devenus particulièrement mous. Je finalise un projet et j'ai pu faire des mesures sans trouver de solutions malgré le nombre de retours négatifs sur ce point.

Tout d'abord mon script ne travaille que sur un nombre limité de cellules : intersection d'environ 65 colonnes et 40 lignes. Aucune données et aucun script en dehors ou sur ligne ou colonne entière.
Mon script agit visuellement pour l'utilisateur avec réglage de la vitesse d'exécution géré par un Timer. Donc j'ai besoin du ScreenUpdating et xlCalculationAutomatic. Les désactiver est effectivement très efficace, mais ça n'a plus aucun sens dans le cas de mon projet.

Pour que vous compreniez l'idée :

Sur la feuille :

Sub Worksheet_Change(ByVal Target As Excel.Range)
Call Modifier(Target)
End Sub

Dans un module :

Sub Modifier(ByVal Target As Excel.Range)
ThisColumn = Target.Column
Select Case ActiveSheet.Cells(7, ThisColumn).Value
Case "0"
ActiveSheet.Cells(5, ThisColumn).Interior.Color = RGB(244, 176, 132)
ActiveSheet.Cells(5, ThisColumn).Borders.LineStyle = xlContinuous
ActiveSheet.Cells(5, ThisColumn).Borders.Weight = xlMedium
...

J'ai enregistré mon fichier au format XLSM et au format XLS 2003.

Même ordinateur (PC fixe), même configuration matérielle (bonne), même disque d'exécution (clé USB)... A vitesse d'exécution 1ms - idem si je court-circuite le Timer qui n'est pas la cause du problème, mais bien la gestion graphique en temps réel :

XLS sous WinXP 32 - Excel 2010 : 2 minutes 14 secondes
XLS sous Win10 64 - Excel 2019 : 4 minutes 50 secondes
XLSM sous Win10 64 - Excel 2019 : 15 minutes 47 secondes

Le script est donc 7x plus lent, ce qui n'est pas sans conséquence, car ce n'est pas pareil d'attendre 3 minutes et 16 minutes, surtout si on doit relancer le script plusieurs fois.

Même soucis et temps sur un DELL portable avec une quadro et un i7 fichier directement sur SSD.

Au boulot, j'ai cru voir que sur certains postes, ça allait plutôt vite alors que sur d'autres c'était encore plus lent.

Si vous avez des idées, y compris modification système. Dois-je plustôt privilégier le format XLS 2003 ? C'est ce que je faisais, constatant la disparition des macros dans le XLSX et avant de découvrir le XLSM. Aujourd'hui, j'ai plutôt tendance à convertir en XLSM, mais c'est peut-être une 1ère erreur, j'ai rarement besoin de plus de 2^16 lignes.

Merci,

Olivier.

[Tête de chat]

Bonjour,

pas facile de te répondre avec aussi peu d'éléments. Le temps de réponse que tu évoques est monstrueux donc certaines choses doivent se passer différemment de ce que tu imagines, par exemple :
- modifs en cascade,
- recherche de choses qui n'existent pas
- modif ciblant une plage trop grande (par exemple une colonne)

Donc sans voir le code ni pouvoir tester...

[P4board]

Bonjour, et merci pour votre réponse. Non rien de tout ça, j'ai évidemment cherché et fait pas mal de tests.

Ce sont les modifications de style des cellules du type ActiveSheet.Cells(5, ThisColumn).Interior.Color = RGB(244, 176, 132) qui demandent du temps. Les désactiver, même très localement, avec un Application.ScreenUpdating = False est radical, mais ce n'est pas ce qui me convient. Les anciennes versions d'Excel géraient ça très bien, là la 2019 semble ne pas bien le gérer. J'ai un autre fichier qui rame de manière excessive depuis le passage en 2019, mais sans trop de conséquences, j'ai préféré faire avec que de perdre du temps à chercher. Là, je peux difficilement me permettre de faire avec.

Je fais tourner exactement le même code qui ne modifie jamais une plage, mais toujours 1 seule cellule à la fois. Les modifications sont par contre très rapides puisqu'il s'agit de suivre visuellement le parcours des boucles. Je peux ralentir avec le Timer, mais une fois que c'est OK, j'aimerais le laisser rouler le plus vite possible.

Je suppose que ce doit être en lien avec la carte et paramètres graphique. Ou un paramètre Excel quelque part. Sur le PC fixe core 2 duo E8500 / quadro fx4600, sur le portable i7 / Quadro K2000M. A peu près équivalent, légèrement plus rapide sur le portable. Au lycée, il y a des pcs fixes avec des quadro pas trop vieux qui ont l'air d'exécuter le code plus vite (XSLM - Excel 2019). Sur des ordis sans carte graphique particulière, ça rame, mais pas beaucoup moins qu'avec mes ordis. Je ne pourrais vraiment tester sur les ordis du lycée que semaine prochaine.

[Tête de chat]

Re,

toujours pas très clair ce que fait ton code...

Histoire d'avancer, j'ai fait ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Option Explicit
Const imax = 40
Const jmax = 65
 
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Time()
Dim i As Integer, j As Integer
For i = 1 To imax
    For j = 1 To jmax
        ActiveSheet.Cells(i, j).Interior.Color = RGB(244, 176, 132)
    Next j
Next i
Debug.Print Time()
End Sub

Dans un XLSM, chez moi ça prend 4 s.

[deedolith]

Salut, quelques pistes:

- Fait un maximum de chose avec les fonctionnalités native d'Excel (Formules, Mise en forme conditionnelles, Tableau Structurés, Tableau croisés dynamiques, Power Query ect ...).
- Chronomètre les diverses fonctionnalités développées pour savoir la ou lesquelles font perdre du temps (un chronometre general n'est pas assez précis).
- Respect du SRP ?
- Respect de la Loi de Demeter ?
- Passage de paramètres (ByRef / ByVal) ?

[P4board]

Bonjour, je vous joins un petit exemple d'un truc qui ne sert évidemment à rien, mais qui montre parfaitement le problème. Un petit aller retour sous XP pour me rendre compte que c'est la version 2003 et non 2010 que j'ai installé. Au lycée, on est sous 2019, donc c'est avec cette version qu'il faut que ça fonctionne.

50 tours :

XLS sous WinXP 32 - Excel 2003 : 5 secondes (une bombe !)
XLS sous Win10 64 - Excel 2019 : 41 secondes
XLSM sous Win10 64 - Excel 2019 : 1 minutes 35 secondes

Ratio : 19

-------------

Edit : test XLSM avec Application.ScreenUpdating = False en début de programme : 18 secondes. C'est mieux, mais encore moins bien que 2003 et sans visuel.