[Résolu]Vitesse du filtre automatique quand utilisation de fonctions personnelles VBA

[Sabil]

Bonjour,

Tout d'abord, mille merci à tous les contributeurs, c'est la première fois que j'ai besoin de poster une question en 4 ans d'utilisation occasionnelle...

Afin d'améliorer la rapidité de la mise à jour des affichages dans un classeur, j'ai remplacé - grâce aux conseils trouvés ici - 25 formules à rallonges (avec des SI // RechercheV et autres joyeusetés) par des fonctions dédiées écrites sous VBA.
Cela a très bien marché et j'ai retrouvé une rapidité d'affichage normale (j'en étais auparavant à 5 s d'attentes après avoir modifié une case).

Malheureusement, il semble que cela ait pour effet de ralentir considérablement l'utilisation du filtre automatique Excel dans les colonnes. Ce fichier est utilisé comme une base de donnée pour retrouver des infos par référence (demandeur/statut de la demande/dates etc...) et quand je sélectionne un élément (le nom d'un demandeur, une plage de date, un statut...), je mets plus de 10s à retrouver la main sur le fichier.
Pour être précis, le filtre semble rapide car l'affichage du nb d'enregistrements trouvés est quasi immédiat, mais il déclenche une mise à jour du calcul du classeur très lente : on voit défiler le % de calcul pour les processeurs - cf image ci-dessous).

J'ai essayé de supprimer les nouvelles fonctions VBA de la feuille (pensant créer deux feuilles : une pour filtrer l'affichage des données de base, et une autre avec les colonnes pour les calculs par fonctions), mais tant que je ne supprime pas carrément les fonctions créées dans VBA, je conserve cette lenteur de mise à jour du fichier.


Précisions : cette lenteur existe même quand la fenêtre VBA est fermée, je travaille sur Excel 2010, le fichier fait 2.5 Mo pour moins de 2300 lignes de données. Cette lenteur a été observée sur deux autres PCs.


Je ne joints pas de fichier exemple pour l'instant (ce sera long de créer un exemple représentatif sans info industrielle), car j'ai l'espoir d'être passé à côté d'une "bonne gestion" des fonctions pour résoudre ce problème. Si ce n'est pas le cas, je prendrai le temps, bien sûr.

Désolé si j'ai raté un fil à ce sujet, j'ai cherché avant de demander, mais je n'ai rien trouvé...

D'avance merci pour votre aide.

 

[Modeste geedee]

Bonsour®

Pour être précis, le filtre semble rapide car l'affichage du nb d'enregistrements trouvés est quasi immédiat, mais il déclenche une mise à jour du calcul du classeur très lente : on voit défiler le % de calcul pour les processeurs - cf image ci-dessous).

prévoir lors de l'utilisation des filtres un passage préalable en mode Calcul à la demande
(Application.Calculation = xlCalculationManual)
puis retour en mode auto
Application.Calculation = xlCalculationAutomatic ou xlCalculationSemiAutomatique (tableaux non pris en recalcul)

 

[Sabil]

OK, je vais voir comment je peux combiner ça avec une "détection de filtre". Je te tiens au courant demain.

Merci beaucoup !

 

[Sabil]

Effectivement, lorsque je supprime le calcul automatique, je n'ai plus de ralentissement. Bien sûr, il réapparaîtra dès que je repasserai en auto, car il semble qu'il recalcule systématiquement tout le tableau dans ce cas (comme lors de l'utilisation "standard" du filtre automatique...).
Je pensais pourtant que le recalcul ne se faisait que sur les cellules modifiées. C'est ce qui se passe lorsque je change des cellules antécédents de mes formules, comme seule les cases concernées sont recalculées, le fichier reste fluide. Et même pour l'enregistrement du fichier complet, je n'ai pas ce ralentissement.

Je vais creuser encore. Merci pour votre aide.

 

[Modeste geedee]

Bonsour®

Effectivement, lorsque je supprime le calcul automatique, je n'ai plus de ralentissement...
Je vais creuser encore. Merci pour votre aide.

Sinon on peut désactiver le calcul dans une seule feuille (en faisant passer la propriété EnableCalculation de la feuille à False) mais c'est toute la feuille.

 

[Sabil]

Bonjour,
je reviens pour clore le sujet : le ralentissement était causé par une seule de la vingtaine de fonctions utilisées !
Elle utilisait l'instruction Dir (recherche de répertoire), et elle était recalculée très fréquemment, même si le changement dans le fichier concernait d'autres cellules que celles appelées dans la fonction.

Je n'ai pas trouvé le moyen d'empêcher le recalcul sans modification directe des paramètres, donc j'ai modifié cette fonction pour qu'elle ne tourne qu'une fois, et qu'après avoir trouvé le répertoire correspondant, elle copie l'adresse en valeur dans une cellule.

Merci encore pour les conseils.