Pourquoi ma fonction est-elle tellement plus lente que celle du pack XLP ?

Bonjour le forum

Je cherche à améliorer les performances d'une fonction ayant des points communs avec la fonction MAX_SI_COULEUR du Pack XLP. J'ai donc réécrit cette dernière à ma façon et ai comparé leur temps d'exécution. Je suis loin du compte...

Ci-joint un petit classeur banc d'essai chrono pour 3 fonctions réparties sur 3 feuilles distinctes. Dans la première j'ai la fonction native Excel en cellule A1 : =MAX($B$1:$K$65000) soit 650 000 cellules

Dans la deuxième, toujours en cellule A1, j'ai la fonction du pack XLP : =MAX_SI_COULEUR($B$1:$K$65000;255)

Dans la troisième j'ai ma fonction qui me donne un résultat équivalent à celle du Pack XLP mais qui consomme beaucoup plus de temps : =MA_FONCTION($B$1:$K$65000;255) 

Function MA_FONCTION(Plage As Range, Couleur&) As Double
    Dim oCell As Range, cpt As Double
    For Each oCell In Plage
        With oCell
            If .Interior.Color = Couleur And .Value > MA_FONCTION Then MA_FONCTION = .Value
        End With
    Next
End Function

Pour mon essai j'ai utilisé une première macro complétant aléatoirement 200 cellules des plages B1:K65000 avec une valeur aléatoire et sur fond rouge, vert ou bleu choisie aléatoirement également et une deuxième macro : "BancDessai" chronométrant le temps de calcul de chaque feuille donc de chacune des trois fonctions.

Je pensais que c'était la boucle For Each qui était évitable et consommatrice de temps mais en remplaçant sont contenu par un traitement plus simple comme :

For Each oCell In Plage
    MA_FONCTION = oCell.Value
Next

Alors on ne réponds plus au problème mais on obtient un temps de calcul plus court que la fonction du Pack
Donc si quelqu'un de pédagogique pouvait m'expliquer comment rivaliser en vitesse avec la fonction du Pack j'en serais très intéressé

(NOTA: mon but n'est pas de concurrencer la fonction MAX_SI_COULEUR mais d'avoir une fonction rapide qui me renvoie l'objet Range pour les cellules d'une police ou d'un fond d'une couleur donnée)

Bonjour Stéphane,

Je vais te donner mon astuce

Pour bon nombre de fonctions du pack, j'utilise d'abord la fonction lastUsedRow pour calculer la dernière ligne utilisée de la feuille et éviter d'aller jusqu'au fond de la plage si ce n'est pas nécessaire.

J'avais mis ça en place en particulier pour éviter de parcourir bêtement >1mio de lignes si l'utilisateur entre des colonnes entières ("A:C") dans les fonctions

Cordialement,

Bonsoir et merci beaucoup Sébastien pour ton implication personnelle pour me répondre et de me dévoiler ton astuce. Je suis vraiment content que tu m'ai consacré ce temps.

A bientôt sur le forum pour encore deux ou trois questions, avant de te soumettre une version 2 de CheckVBA beaucoup plus travaillée que le première où je ne m'étais pas foulé.

Cordialement