Macros simples qui rament dans gros fichier Excel

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.

Bonjour

Tu dis avoir ajouté un grand nombre de feuilles avec des formules de plus en plus complexes.

Essaie en mettant en début de procédure le mode de calcul sur ordre et rétablis-le en automatique en fin de proc.

parce que, pour peu que tu ais en plus des formules matricielles, le temps de recalcul à chaque modification peut rapidement augmenter

Cordialement

Merci d'essayer de m'aider.

Je viens de tester sur une macro en ajoutant :

En début de macro

Application.Calculation=xlCalculationManual

et en fin de macro:

Application.Calculation = xlCalculationAutomatic

et cela ne change pas le temps d'éxécution.

Je pense que c'était ce que tu préconisais?

Je n'ai pas mentionné un détail : en effet, sur les autres feuillets, il y a des formules matricielles. Cependant, cela n'a rien à voir avec les feuillets concernés par les macros.

Bonjour

en effet, sur les autres feuillets, il y a des formules matricielles. Cependant, cela n'a rien à voir avec les feuillets concernés par les macros.

Même si ces feuilles ne sont pas concernées par les macros, le recalcul les affecte également puisque tout le classeur est recalculé à chaque opération.

Regarde le temps de recalcul en rajoutant une opération quelconque dans une feuille et sans activer aucune macro. Tu pourras déjà avoir une idée.

Ou, à contrario, laisse le calcul sur ordre pour voir la différence

Cordialement

Amadéus a écrit :

Même si ces feuilles ne sont pas concernées par les macros, le recalcul les affecte également puisque tout le classeur est recalculé à chaque opération.

Regarde le temps de recalcul en rajoutant une opération quelconque dans une feuille et sans activer aucune macro. Tu pourras déjà avoir une idée.

En effet, un simple calcul n'est pas instantané et prend quelques dixièmes de seconde.

Ce serait donc lié aux formules matricielles?

Comment puis-je faire, sachant que je ne peux pas me passer des formules matricielles et que l'usage du fichier est prévu en calcul en temps réel?

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).

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?

Bonjour

Regarde si tu peux remplacer tes formules matricielles par une variable dans ton code qui inscrirait le résultat de la formule au lieu de la formule

Cordialement

si c'est le cas, dans les macros, ajoute l'instruction calcul manuel sur commande ...

maintenant, les calculs matriciels ne sont pas recommandés sur de grosses quantités de données, aussi élégantes soient-elles, il faut aussi passer par des macros le cas échéant

Bonjour steelson

si c'est le cas, dans les macros, ajoute l'instruction calcul manuel sur commande ...

Cette manip a déjà été préconisée en début du sujet

Cordialement

Amadéus a écrit :

Bonjour steelson

si c'est le cas, dans les macros, ajoute l'instruction calcul manuel sur commande ...

Cette manip a déjà été préconisée en début du sujet

Cordialement

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

Voici ma macro initiale :

Sub PosMP()

End Sub

Je l'ai modifiée ainsi :

Sub PosMP()

End Sub

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

Ai-je fait une erreur?

Amadéus a écrit :

Bonjour steelson

si c'est le cas, dans les macros, ajoute l'instruction calcul manuel sur commande ...

Cette manip a déjà été préconisée en début du sujet

Cordialement

désolé, pas lu

Bonjour a tous

on aperçoit un début de code, avec déjà deux :

range("XX").select

si ton code VBA contient beaucoup de select, c'est aussi normal que le code prenne du temps, il faut les éviter autant que possible pour que le code se passe plus rapidement...

as tu pensé aussi un

Application.ScreenUpdating = False

cela peut aussi améliorer le temps d'execution

fred

Bonsoir à tous,

Essaie de rajouter en début de macro

Application.screenupdating=False

Ensuite évite les "Select" qui ralentissent beaucoup et souvent ne servent à rien.

Bon courage !

Pour info : finalement, remplacer la formule matricielle a solutionné le problème.

Merci à ceux qui m'ont aidée.

Bonjour

Les Select ralentissent les macros.

Range("C3").FormulaR1C1 = "MP"

Cordialement

Rechercher des sujets similaires à "macros simples qui rament gros fichier"