Lenteur excessive sur Macro avec recalcul auto

[hook85]

Bonjour à tous,

étant désespéré, je viens vers vous pour plus de conseils.

Le problème :
j'ai du adapter une macro permettant de retourner le nombre de fois que l'on trouve dans une colonne M suivant la valeur distincte de la colonne C.

Cette macro me retourne le résultat voulu, seulement, en mode Recalcul Auto (Classeur mis à dispo de tout le monde sur le réseau), le classeur présente une lenteur des plus énervantes aussi bien lors des modifs de cellules que lorsque l'on ne fait rien.

A la fermeture du fichier, nous sommes obligés de Canceler le process d'Excel dans le gestionnaire des tâches car Excel ne répond plus.

Pour info, cette macro est utilisée à différents endroits dans les lignes 1 à 30.

Merci d'avance pour toute l'aide que vous pourrez m'apporter.

 

[hook85]

Re : Lenteur excessive sur Macro avec recalcul auto

Personnes pour le coup de main?

 

[flyonets44]

Re : Lenteur excessive sur Macro avec recalcul auto

Bonjour
Au lieu de garder des formules dans ta feuille , garde le résultat des calculs
Au début de ta macro rajouter
Application.screenupdating=false
en fin de macro
Application.Calculation = xlCalculationautomatic
End sub
Cordialement
Flyonets

 

[Jacou]

Re : Lenteur excessive sur Macro avec recalcul auto

Bonjour,
quel est le volume du fichier? Y a-t-il beaucoup de lignes avec des formules? (je pose les questions car les règles de sécurité de mon entreprise ne me permettent pas de télécharger le fichier zip).

Pour améliorer la vitesse d'exécution d'une macro, il est possible de ne pas afficher les différentes opérations qu'effectue la macro pendant toute la durée de son excécution en mettant tout à fait au début du code la ligne :

Application.ScreenUpdating = False

Ceci permet de neutraliser l'affichage et on gagne un temps précieux si le nombre d'opérations effectuées est important.

Ne pas oublier d'ajouter à la fin de la macro (ou avant l'instruction "Exit Sub" la ligne :

Application.ScreenUpdating = True

pour réactiver l'affichage.

Bonne journée

 

[hook85]

Re : Lenteur excessive sur Macro avec recalcul auto

Bonjour Jacou,

mon fichier contient 2165 lignes.

Pour les autres infos, j'ai remis mon fichier (allégé) en PJ

 

[mth]

Re : Lenteur excessive sur Macro avec recalcul auto

Bonjour le fil

J'ai conscience de ne pas faire avancer le schmilblick, mais de mon coté même avec des sommeprod() sur des plages importantes, le fichier tourne tourne très bien en mode auto, (pourtant vieux portable méritant mais fatigué, équipé d'un vieux XP et Excel 2007), ça viendrait peut-être d'autre chose?

Bonne journée,

mth

 

[hook85]

Re : Lenteur excessive sur Macro avec recalcul auto

en fait, je n'avais pas de soucis avec d'ajouter ma macro NbVal_Distinct_Bis

 

[Dranreb]

Re : Lenteur excessive sur Macro avec recalcul auto

Bonjour

Remarque:

=SOMME(SI(ESTVIDE($C$35:$C$2169);0;1/NB.SI($C$35:$C$2169;$C$35:$C$2169))) & " Jobs dont"

validé par Ctrl+Maj+Entrée Donne le même résultat que

=nbval_distinct($C$35:$C$2169) & " Jobs dont"

Alors si pour éviter de trimballer de telles formule on tient à garder une fct personnalisée, la moindre des choses c'est de l'écrire en un code pratiquement aussi performant: limiter les requêtes Excel en chargeant les valeurs de plages en tableau de variant et en utilisant au maximum les WorksheetFunction.
À+

 

[hook85]

Re : Lenteur excessive sur Macro avec recalcul auto

=SOMME(SI(ESTVIDE($C$35:$C$2169);0;1/NB.SI($C$35:$C$2169;$C$35:$C$2169))) & " Jobs dont"

Cela me fait une division par 0.
Merci quand meme pour l'info Dranreb.

 

[Dranreb]

Re : Lenteur excessive sur Macro avec recalcul auto

Chez moi j'ai eu la division par 0 avant d'ajouter le test avec ESTVIDE. Après ça a marché.
Mais on a aussi une division par 0 si on la valide par Entrée seulement comme une formule normale au lieu de la valider en formule matricielle (c'est à dire en laissant appuyées ensemble les touches successivement enfoncées : Contrôle, Majuscule (aussi appelée Shift) et Entrée).

Avez vous bien veillé à la valider de cette façon ?
Et comment votre problème de performance avance -t-il ?

Il n'est pas absolument exclut qu'on puisse réécrire votre fonction de façon qu'elle soit plus performante que cette formule matricielle. L'idée serait d'indexer les valeurs de la plage par la méthode la plus rapide que je connaisse pour cet ordre de grandeur du nombre d'éléments à trier, puis de compter les doublons en parcourant la table des valeurs par l'ordre croissant donné par la table index. Mais il est possible aussi qu'Excel procède déjà ainsi.
À+