Demande d'optimisation de mon code vba

[napo124]

Bonjour,

Je travaille sur un projet, et je suis amené à préparer une macro de tri de données.
Celle ci doit éffectuer plusieurs test, ligne par ligne.

Deux lignes ayant le même fournisseurs et le même ID est considéré comme DOUBLON.
La base de données est triée par ID, puis par fournisseurs et enfin par "ref" (cf fichier)

La base de données sera constituée de plusieurs milliers de lignes. Ma macro est fonctionnel, mais commence à être vraiment longue au bout de 10 000 lignes.
Pouvez vous m'aider à rendre mon code plus rapide?

Le schéma décisionnel est présenté dans le fichier. Le code vba est détaillé avec mes commentaires.

Pouvez vous m'aider ?

Cordialement

 

[napo124]

Re : Demande d'optimisation de mon code vba

Oui cela est une idée!
Mais dans la construction de mon code, je ne peux pas faire ça puisque cela me génerait ensuite dans ma boucle qui affecte la priorité MINIMUM du groupe de doublons.
Je pense que mon code perd en fait énormément de temps dans la recherche de cette valeur, et je pense aussi que c'est là la grande difficulté du problème.

 

[GeoTrouvePas]

Re : Demande d'optimisation de mon code vba

Après analyse complémentaire, tu me dis si je me trompe :

Etape 1 : On dégage tous les lignes avec "Statut = 9"

Etape 2 : On repère toutes les lignes qui ont la même "ID et le même "Fournisseur" qui sont à considérer comme des doublons

Etape 3 : Parmi chaque groupe de doublons, on ne garde que la ligne qui a la "Priorité" la plus élevée

J'ai encore du mal à comprendre l'utilité de la colonne "Ref".... Help !

 

[GeoTrouvePas]

Re : Demande d'optimisation de mon code vba

J'ai encore parlé trop vite.

Un de tes précédents posts répond à ma question. On ne s'intéresse qu'aux doublons dont la "ref" est à N.

Malheureusement, je pense que, vu la complexité de tes critères de sélection, tu ne pourras pas beaucoup optimisé ton code.

Je pense que la meilleure solution, serait d'utiliser une variable tableau.

Charge ton tableau de départ dans une variable, adapte ta procédure pour quelle retraite cette variable puis retranscrit ta variable sur un onglet. Tu verras que, lorsque la quantité de données devient importante, c'est un moyen efficace pour accélérer la vitesse de traitement.

Tu as un très bon tuto ici pour comprendre comment marchent ces variables.

 

[napo124]

Re : Demande d'optimisation de mon code vba

Merci Geotrouvepas, je regarde ça!

Sinon pour répondre à ta question, tu as tout compris, sauf que:


Etape 1 : On dégage tous les lignes avec "Statut = 9"

Etape 2 : On repère toutes les lignes qui ont la même "ID et le même "Fournisseur" qui sont à considérer comme des doublons

Etape 3 : Parmi chaque groupe de doublons, on ne garde que les ref "O" et la priorité la plus petite des ref "N"

 

[napo124]

Re : Demande d'optimisation de mon code vba

Hum, je lis, mais j'ai du mal à savoir comment je pourrai utiliser ce tableau avec mon problème

 

[GeoTrouvePas]

Re : Demande d'optimisation de mon code vba

C'est pas si compliqué que ça.

Plutôt que de traiter les données dans un tableau "en dur" tu les traites dans une variable tableau.

Le principe est exactement le même sauf que ça reste "virtuel". Tu n'inscris tes données en dur dans ta feuille qu'à la fin du traitement.

Dans l'exemple cité sur le tuto, le traitement est 20 fois plus rapide en passant par une variable tableau.

 

[napo124]

Re : Demande d'optimisation de mon code vba

Si je comprends bien je dois créer un tableau virtuel pour chaque sous groupe de doublons afin qu'il m'en ressorte le min ?

 

[napo124]

Re : Demande d'optimisation de mon code vba

Bon, je continue à chercher et à faire des tests, mais ces tableaux sont une notion tout à fait nouvelle pour moi.
Quelqu'un peut il me mettre sur la bonne piste?

 

[napo124]

Re : Demande d'optimisation de mon code vba

Je viens de faire le test avec la variante tableau, mais c'est encore plus long... J'ai du mal préparé la chose. Mais je n'arrive pas à saisir la logique.

 

[napo124]

Re : Demande d'optimisation de mon code vba

Arf,

Finalement, non j'avais fais une erreur, j'ai gagné 20sec sur un code de 50sec, c'est un bon début !

 

[GeoTrouvePas]

Re : Demande d'optimisation de mon code vba

Bonjour napo124,

Désolé, je n'ai pas pu passer plus tôt sur le forum.

En fait, il faut que tu charges l'intégralité de ta liste initiale dans une variable tableau et que tu y appliques le même processus de sélection.

Une fois ce retraitement effectué, il te suffira de supprimer la base initiale et d'y placer les données restantes de ta variable tableau.

 

[Jam]

Re : Demande d'optimisation de mon code vba

Salut napo124, GeoTrouvePas,

Le seul moyen d’accélérer vraiment le traitement de ta macro est de passer par un tableau.
Il suffit de copier la plage dans le tableau puis de le parcourir (comme une plage de cellules) et y effectuer les modifs, puis de coller le tableau en retour dans la feuille.
Cette optimisation est particulièrement efficace car la lecture de données en mémoire est nettement plus rapide et performante que la lecture de cellules.
Pour faire simple, le moteur d'Excel doit analyser déterminer la cellule, puis son contenu...ce qui demande du temps, alors que la données en mémoire est lue quasi instantanément.

Bon courage,

Slt GeoTrouvePas, nos réponses se sont croisée...euh, non en fait y'a eu un bug d'affichage car je n'ai pas vu du tout ta dernière réponse. Désolé pour la redite.

 

[Jam]

Re : Demande d'optimisation de mon code vba

Re Napo,

...bon, la prochaine fois je passerai à la page 2 avant de répondre

Aller quelques pistes:

'Initialisation de la variable
Dim myArray as Variant    'Pour le tableau
(...)
Set myArray=Selection.Value    'Affectation du range (la sélection) au tableau

For i=1 to UBound(myArray,1)
    For j=1 to UBound(myArray,2)
        'utiliser myArray(i,j) comme avec cells(x,y) pour effectuer les traitements
    Next j
Next i

Selection.Value=myArray

Bon courage,

 

[napo124]

Re : Demande d'optimisation de mon code vba

Bonjour Geotrouvepas, bonjour Jam,

Merci de votre temps et de votre aide!
Je pense avoir compris la logique, et avoir fait le rapprochement avec ma problématique! (le tutoriel est complet, mais va trop vite pour moi ^^).
Si je ne me trompe, le dernier code de Jam permet d'étudier les cellules, ligne par ligne et ensuite colonne par colonne (c'est ça?), le problème, c'est qu'il me faut trouver le minimum des valeurs d'une plage de cellules, elle même variable en fonction de différents critères!

Cependant, rien qu'en utilisant la variante tableau dans mon code, je gagne beaucoup de temps ! Mais je suis sur de pouvoir aller plus loin...

 

[GeoTrouvePas]

Re : Demande d'optimisation de mon code vba

...bon, la prochaine fois je passerai à la page 2 avant de répondre

Le genre de boulette qui m'est arrivée plus d'une fois !

@napo124 : Oui tu as tout à fait compris comment fonctionne son code.
J'ai bien réfléchi à ton problème et, à mon avis tu ne peux pas échapper au fait de faire "une boucle dans la boucle" lorsque tu recherches tes valeurs MIN. Le seul moyen d'accélérer le traitement est donc de passer par une variable tableau.

Comme l'a souligné Jam et comme tu as déjà pu le constater, le gain de temps est considérable. En plus ça n'est pas plus compliqué de "naviguer" dans une variable tableau. Tu continues à utiliser des coordonnées comme pour un tableau :

MaVariable(i, j) équivaut à Cells(i, j)

Pour ce qui est de la procédure de traitement, je procèderai de cette façon :

Etape 1) Trier le tableau selon l'ID et le fournisseur

Etape 2) Charger ce tableau dans une variable tableau

Etape 3) Marquer les lignes où le statut est à 9 afin de ne pas avoir à les traiter dans les étapes suivantes

Etape 4) Marquer les lignes "Mono" afin de ne pas avoir à les traiter dans les étapes suivantes

Etape 5) Traiter les lignes Multi pour sélectionner celles qu'il va falloir garder

Etape 6) Transposer ta variable tableau dans ton classeur en ne prenant que les lignes sélectionnées.

Lors de ton prochain post, essaye de joindre un fichier avec le début de ton code que je puisse y jeter un coup d'oeil et peut être le compléter un peu.