Macros simples qui rament dans gros fichier excel

Y compris Power BI, Power Query et toute autre question en lien avec Excel
n
nicopat
Membre fidèle
Membre fidèle
Messages : 257
Inscrit le : 28 mars 2015
Version d'Excel : 2007 FR

Message par nicopat » 28 mars 2015, 13:14

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.
Sympathisme, Communautarisme, Altruisme.
Je peux aider pour choisir une agence web. (experte agréée pour les appels d'offres sur http://www.webagency321.com/fr/)
A
Amadéus
Modérateur
Modérateur
Messages : 17'770
Appréciations reçues : 68
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 28 mars 2015, 13:54

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
Amadéus vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
n
nicopat
Membre fidèle
Membre fidèle
Messages : 257
Inscrit le : 28 mars 2015
Version d'Excel : 2007 FR

Message par nicopat » 28 mars 2015, 14:18

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.
Sympathisme, Communautarisme, Altruisme.
Je peux aider pour choisir une agence web. (experte agréée pour les appels d'offres sur http://www.webagency321.com/fr/)
A
Amadéus
Modérateur
Modérateur
Messages : 17'770
Appréciations reçues : 68
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 28 mars 2015, 14:28

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 vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
n
nicopat
Membre fidèle
Membre fidèle
Messages : 257
Inscrit le : 28 mars 2015
Version d'Excel : 2007 FR

Message par nicopat » 28 mars 2015, 14:44

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).
Sympathisme, Communautarisme, Altruisme.
Je peux aider pour choisir une agence web. (experte agréée pour les appels d'offres sur http://www.webagency321.com/fr/)
n
nicopat
Membre fidèle
Membre fidèle
Messages : 257
Inscrit le : 28 mars 2015
Version d'Excel : 2007 FR

Message par nicopat » 28 mars 2015, 15:11

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?
Sympathisme, Communautarisme, Altruisme.
Je peux aider pour choisir une agence web. (experte agréée pour les appels d'offres sur http://www.webagency321.com/fr/)
A
Amadéus
Modérateur
Modérateur
Messages : 17'770
Appréciations reçues : 68
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 28 mars 2015, 16:08

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
Amadéus vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 13'136
Appréciations reçues : 734
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 28 mars 2015, 16:08

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

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
A
Amadéus
Modérateur
Modérateur
Messages : 17'770
Appréciations reçues : 68
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 28 mars 2015, 16:26

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 vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
n
nicopat
Membre fidèle
Membre fidèle
Messages : 257
Inscrit le : 28 mars 2015
Version d'Excel : 2007 FR

Message par nicopat » 28 mars 2015, 16:57

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()
'
' 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?
Sympathisme, Communautarisme, Altruisme.
Je peux aider pour choisir une agence web. (experte agréée pour les appels d'offres sur http://www.webagency321.com/fr/)
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message