Macros simples qui rament dans gros fichier excel

nicopat

XLDnaute Junior
Bonjour à tous,

Je suis relativement expérimenté sur excel (+ que la moyenne disons...), mais là, c'est un pb qui va au-delà de ce que j'ai déjà expérimenté.

Cela fait 3 mois que je bosse sur un gros fichier excel.

J'ai commencé par réaliser quelques feuilles simples contenant des macros simples permettant d'insérer du texte X dans certaines cellules et quelques fonctions (recherchev) permettant de rechercher une valeur en fonction des dits textes X. L'éxécution de ces macros était instantanée.

A ce stade du projet, le fichier .xlsm contenait 3 feuillets et pesait 300 KO.

Puis j'ai ajouté d'autres feuilles beaucoup + compliqués, avec beaucoup de formules complexes et des données.
A noter que ces feuillets additionnels n'ont pas de connexions avec les feuillets initiaux contenant les macros (pas de formules allant chercher des valeurs dans ces feuillets, etc...).
A présent, le fichier contient 48 feuillets et pèse 35 MO.

Hier, j'ai découvert que dans les premiers feuillets, l'éxécution des macros simples n'est plus instantanée, mais nécessite environ 1 seconde pour chaque macro ( dont la seule fonction est de cop-coll ou d'insérer du texte dans une cellule) !

Donc c'est quasi inutilisable, tellement c'est pénible (ces macros sont censées accélérées la saisie).
En reprenant les sauvegardes quotidiennes que j'ai faites de ce fichier, il semble que la lenteur de ces macros ne soit pas directement liée au poids du fichier, car des sauvegardes intermédiaires pesaient jusqu'à 41 MO et les macros semblent + rapides qu'actuellement (bien que pas instantanées). Dans certaines sauvegardes antérieures, le poids du fichier est de 33MO (mais seulement 18 feuillets), mais les macros fonctionnent instantanément.
En revanche, la lenteur des macros semble liée à la lenteur d'ouverture du fichier : les fichiers dans lesquels les macros sont lentes semblent + longs à ouvrir que les fichiers dans lesquels les macros sont instantanées.
A noter que mon PC est assez costaud (Intel Core i5, 3.1Ghz, 8Go RAM, disque SSD, Windows 8 ).

J'imagine que les ressources du PC sont impliquées dans ce problème puisque si j'ouvre les premières sauvegardes dans lesquelles les macros sont rapides, et que j'ouvre en même temps une sauvegarde récente dans laquelle les macros sont lentes, les macros deviennent lentes dans les 2 fichiers.

Par ailleurs, les feuillets contenant des fonctions complexes, très interconnectées etc... fonctionnent parfaitement et se mettent à jour instantanément. C'est à n'y rien comprendre.

La lenteur des macros peut-elle être due au nombre de feuillets dans le classeur (plutot qu'à son poids)?
Quelqu'un sait-il comment je peux améliorer les performances sur ces macros?


Merci

Nicole

PS : à noter que durant l'évolution de ce fichier, il y a eu un bug d'excel un jour, et j'ai du poursuivre avec un fichier récupéré par Windows. Cela dit, la date à laquelle cela s'est produit ne correspond pas aux sauvegardes dans lesquelles les macros ont commencé à devenir lentes.
 

pierrejean

XLDnaute Barbatruc
Re : Macros simples qui rament dans gros fichier excel

Bonjour Nicole

et bienvenue sur XLD

Sur l'une de tes macros simples essaie:

En début de macro

Application.Calculation=xlCalculationManual

et en fin de macro:

Application.Calculation = xlCalculationAutomatic
 

nicopat

XLDnaute Junior
Re : Macros simples qui rament dans gros fichier excel

Bonjour Nicole

et bienvenue sur XLD

Sur l'une de tes macros simples essaie:

En début de macro

Application.Calculation=xlCalculationManual

et en fin de macro:

Application.Calculation = xlCalculationAutomatic

Merci d'essayer de m'aider.
Je viens de tester sur une macro et cela ne change pas le temps d'éxécution.
Je n'ai pas mentionné un détail : sur les autres feuillets, il y a des formules matricielles. Cependant, cela n'a rien à vois avec les feuillets concernés par les macros.
 

nicopat

XLDnaute Junior
Re : Macros simples qui rament dans gros fichier excel

Je me rends compte que même un simple calcul n'est pas instantané et prend quelques dixièmes de seconde.
Serait-ce lié aux formules matricielles?

Par ailleurs je viens de faire un test :
j'ai créé une copie de mon fichier actuel (48 pages / 25 MO)
j'ai supprimé les 8 pages les + lourdes, mais qui ne contiennent pas les formules matricielles (40 pages / 1.5 MO)
et les macros sont maintenant rapides (mais pas instantanées).
 

Modeste geedee

XLDnaute Barbatruc
Re : Macros simples qui rament dans gros fichier excel

Bonsour®
La lenteur des macros peut-elle être due au nombre de feuillets dans le classeur (plutot qu'à son poids)?
Quelqu'un sait-il comment je peux améliorer les performances sur ces macros?
Excel possède "By Design" une façon particulière d'effectuer les recalculs qui est dépendante des calculs précédemment déjà effectués...

il est possible dans ton cas que cet ordre (calculs dépendants) ne soit pas optimum...
voir :
How the Excel Smart Recalculation Engine works - Decision Models

concernant les matricielles évoquées voir aussi cet article :
Ce lien n'existe plus
 

nicopat

XLDnaute Junior
Re : Macros simples qui rament dans gros fichier excel

Merci pour ces infos,

J'ai refait un autre test :

j'ai créé une copie de mon fichier actuel (48 pages / 25 MO)
j'ai supprimé la page contenant les formules matricielles (47 pages / 24 MO)
et les macros sont maintenant rapides (pas instantanées mais presque).

Peut-on en conclure que le délai de mes macros vient de tous les recalculs dans l'ensemble des cellules du fichier qui se produit à chaque modification du fichier par l'utilisateur?
Et que ces temps de recalcul sont particulièrement long lorsque des formules matricielles sont dans le fichier?
Y a-t-il une solution?
 

pierrejean

XLDnaute Barbatruc
Re : Macros simples qui rament dans gros fichier excel

Re

Salut Modeste

Sauf si la macro que tu as testée avec Application.Calculation=xlCalculationManual affecte une cellule concernée par les matricielles il y aurait du avoir un gain de temps
Par ailleurs, si solution il y a , c'est en remplaçant les matricielles par des macros déclenchées lorsqu'il en est besoin
 

nicopat

XLDnaute Junior
Re : Macros simples qui rament dans gros fichier excel

Peut-être ai-je mal procédé.

Voici ma macro initiale :

Sub PosMP()
'
' PosMP Macro
'

'
Range("C3").Select
ActiveCell.FormulaR1C1 = "MP"
Range("C4").Select
End Sub


Je l'ai modifiée ainsi :

Sub PosMP()
'
' PosMP Macro
'

'
Application.Calculation = xlCalculationManual
Range("C3").Select
ActiveCell.FormulaR1C1 = "MP"
Range("C4").Select
Application.Calculation = xlCalculationAutomatic
End Sub


Et il n'y a pas d'amélioration.

Ai-je fait une erreur?
 

Discussions similaires

Réponses
1
Affichages
311

Statistiques des forums

Discussions
311 715
Messages
2 081 822
Membres
101 822
dernier inscrit
holale