Autres Demande de l'aide sur Fichier Excel avec macro VBA ( lenteur )

[gilmour76]

Bonjour,

je travaille avec un fichier excel auquel j'ai apporté des amélioration ( du moins de pense ) et dont vous aussi chers membres avait contribué.

Voilà mon souci est que ce fichiers contient beaucoup de donnée et commence vraiment à être lent,en tout cas avec mon Excel 2007 que j'utilise , car de plus il ne fonctionne pas sur excel 2010.

LE fichier est composé d'une BD et de 2 feuilles de calcul sur lequel sont des mises en formes conditionnelle pour afficher mon planning de réservation et livraison de véhicules, quand je souhaite accéder à une de ces feuilles le fichier rame a mort , plus de 18 sec pour rafraîchir et me montrer le résultat.

Je ne suis pas très doué mais j'ai essayer de chercher et trouver une solution en vain.

Je m'en remet à vous pour demander de l'aide à savoir comment améliorer les performance de ce fichier, si moyen il y a.

Vous remerciant d'avance.

PS: j'ai attaché le fichier sur lequel j'ai enlever les données sensible pour respecter l’anonymat des clients

 

[sylvanu]

Bonjour Gilmour,
C'est sur que reconstruire de telle bases de données prend du temps.
Pour le fun, on gagne un peu de temps (15%) en rajoutant Application.Calculation = xlCalculationManual après le freeze de l'écran
Mais la solution passera par d'autres mesures plus radicales.
Juste une question : Avez vous toujours besoin de construire des bases hebdomadaires sur 6 mois ? Est ce possible de réduire ? Ou par défaut faire moins , et plus sur demande utilisateur ?
Les mises en forme de la BD prennent aussi énormément de temps, on peut, peut être optimiser.

 

[sylvanu]

Re Gilmour,
Déjà de base, mettez ça en début et fin de macro, ça ne peut qu'accélérer.

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False

...

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  ActiveSheet.DisplayPageBreaks = True
  Application.ScreenUpdating = True

J'ai vu aussi qu'en BD Véhicules chaque cellule avait sa liste déroulante, soit ... 1800 listes déroulantes, même si c'est la même ça alourdit.
On pourrait alléger avec un useform comme pour les dates qui donnerait le choix à l'utilisateur en cliquant sur la colonne C.

 

[sylvanu]

J'ai fait une petite macro qui consiste juste à changer de feuille et mémoriser les temps. Avec plusieurs configs :

J'ai exprimé en % car les configs sont différentes. Et par rapport au fichier d'origine que j'ai pris comme référence.
Les gains les plus importants sont la suppression des listes déroulantes dans BD, et la suppression des MFC dans BD.
Les MFC dans Plan xxx interviennent un peu moins.

 

[gilmour76]

Bonjour Sylvanu,

et merci pour ta réponse, juste parce-que j'aime bien comprendre et pas envie de mourir bête en quoi la MFC et les listes déroulantes dans la BD cause le problème de ralentissement et d affichage dans les feuilles Plan xxx?

En plus sur la feuille BD je n'ai pas de ralentissement particulier.

Pour répondre à tes questions, je l'utilise pour mes réservation de voiture donc j'ai besoin d"un visuel sur l'année.

La solution de remplacer la liste déroulante par un userform me plait bien, mais je t'avoue que je ne saurai la faire car à l'origine ce fichier est de Boisgontier qui proposer cette solution pour ne pas avoir deux location sur la même période et afficher que les voitures disponibles, si tu est prêt à me donner un peu de ton temps pour m'aider je veux bien m'y mettre.

Sinon je pensais remplacer la MFC de excel par un code VBA pour le remplissage de PLan XXX, ca existe ou pas?

Merci pour ton aide

 

[gilmour76]

Me suis peut être mal exprimé, mais quand je passe de la feuille BD à planXXX c'est là que j'ai un ralentissement et un lag. Mais quand je passe de planningXXX à BD c'est quasi instantané.

 

[sylvanu]

Bonjour Gilmour,
Je n'ai mesuré les temps que dans un passage BD Plan, je n'ai pas fait l'inverse.
Le passage BD Plan est long car outre votre code qui extrait cette page sur 1900 lignes, il y a 54356 MFC, c'est à dire qu'XL en arrivant sur cette page doit évaluer
27178 fois : =ET(SOMMEPROD((PlanVéhicules=$A4)*(B$3>=ENT(début))*(B$3<=fin));$A4<>0) et
27178 fois : =JOURSEM(B$3;2)>5
Ce qui fait beaucoup. C'est là qu'il rame, surtout pour les sommeprod qui sont réputés lourds à calculer.
Quant à remplacer une MFC par du code, c'est illusoire. Si le VBA fait la même chose que la MFC ... vous ne ferez jamais aussi bien que Microsoft.
Les listes déroulantes n'ont pas à être évaluées par XL, c'est pour ça que le retour est plus rapide. Mais ça alourdit beaucoup le fichier.

Pour vraiment accélerer, reposez vous la question du besoin de l'utilisateur. A t-il besoin systématiquement d'une vision de 6 mois sur les feuilles Planxx ? Une façon d'accélerer serait de ne lui montrer que le mois courant, et plus sur sa demande.
D'autre part, est ce que PlanSemxx doit montrer tous les véhicules ? Car même si une ligne est blanche, XL doit évaluer la MFC pour justement décider ... qu'elle n'est pas concernée.

Pour conclure, je pense que l'accélération passera plus par une refonte de l'ergonomie que par une optimisation de votre code.
Faites un essai comme moi : Vous faites une copie, vous virez toutes les MFC ( selection de toute la feuille et effacer règles ), et vous faites un test, et vous pouvez apprécier le résultat, il ne reste que les listes et votre code.

 

[sylvanu]

Le café porte conseil.
En fait l'erreur originelle est ,je pense, que vous reconstruisez l'ensemble du plan de location a chaque fois que vous selectionnez une feuille PlanSemxx.
C'est comme si à chaque fois que vous regardiez un planning, vous preniez un nouveau calendrier, un Stabilo et que vous le reconstruisiez.
Je pense que la bonne solution est de mettre à jour les 2 planings hebdo à chaque fois que vous modifiez BD. Vous n'auriez qu'à mettre à jour une ligne. Dans ce cas vous pouvez vous permettre de mettre les couleurs en VBA, ce qui d'une part supprime les MFC et d'autre part rend le planning immédiat. En fait comme si un calendrier papier était au mur et mis à jour à chaque nouvelle réservation.
Mais ça c'est reprendre le code depuis la base. ... et je comprendrais fort bien que cette solution vous rebute, même si c'est l'idéal.