[résolu]Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

[loureed]

Bonjour,

J'ai un classeur constitué d'une feuille "base de données" et une série de feuille qui chacune représente un personnel, et dont la structure de chaque feuille est identique.
Sur les feuilles "personnel" des mise en formes conditionnelles sont utilisées pour la présentation de la fiche en fonction des données de la base de données : un personnel X avec la qualification Y on affichera la ligne 3, la ligne 5 et on mettra sur fond gris les lignes 1,2,4 et 6. Un personnel avec la qualification Z, on affichera la ligne 3,4 et 5 et on mettra les lignes 1,2 et 6 en gris. Etc en fonction de la base de données.

Tout marche bien.
J'ai une macro qui me permet de rajouter une ligne dans la base de données, et du coup, vu que toutes les feuilles du personnel sont identique, je dois rajouter cette ligne sur toutes les fiches de personnel.

Ceci marche également, sauf que depuis peu vu le nombre de fiche de personnel crée la macro est de plus en plus longue, jusqu'à 7 minutes pour 60 fiches personnels.

Ce qui m'intrigue, c'est que le même fichier exécute cette opération en 6 secondes sous excel 2003.

Alors où se trouve le problème ?

La procédure de rajout de ligne est la suivante :
j'active le première feuille de personnel,
je copie la ligne 15, j'insère et colle la ligne ainsi copié (permet de récupérer les formules et les mise en formes conditionnelles)
j'active la feuille suivante, etc...

Alors qu'il y avait peu de feuille personnel la procédure allait vite... maintenant ce n'est plus possible !

J'ai essayé feuille à feuille ou en sélectionnant toutes les feuilles : même résultat.

En regardant la macro en mode pas à pas, s'il n'y a qu'une feuille de personnel la ligne d'insertion ne dure pas longtemps mais s'il y a une vingtaine de feuille de personnel alors la ligne d'insertion dur environ 7 secondes !!!
Hors la macro travail feuille après feuille, pourquoi cette différence de temps !!!!

Merci à vous de m'éclairer enfin si vous voyez de quoi cela peut venir

Loureed

 

[Jack2]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Bonjour,

Je crois que c'est Dranreb qui avait souligné dans un post la perte de temps liée à l'utilisation de .Select et d'une manière générale en sélectionnant trop souvent directement feuilles et cellules au lieu de passer par des variables de type variant. Un autre Barbatruc a posté ce type de boucle:

For i = 1 To Sheets.Count
    With Sheets(i)
    .Activate
    'ton bout de code
    End With
Next i

Essaye ces pistes en enlevant ce type d'instruction ou poste ton fichier sans données sensibles. Il y aura quelqu'un pour optimiser ton code.

A+ JAck2

 

[loureed]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

voici un bout de code qui ressemble au mien mais en plus allégé, surtout au niveau des mise en forme conditionnelles, mais le principe est le même :

On ajoute une opération dans la base de données, on ajoute cette ligne à toutes les fiches de personnels et les mise en forme conditionnelles font qu'Excel affiche ou pas la ligne correspondante.

Sur le fichier original (je ne l'ai pas en ce moment) il y a 90 fiche de personnel et les mise en forme conditionnelles sont un peu plus compliquées.

Merci encore de bien vouloir vous pencher sur ce dossier.

Loureed

 

[Paf]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

bonjour,

s'il y a bien quelques select à modifier sur le classeurs exemple, ce ne doit pas être ça qui ralentit beaucoup (quoique 90 feuilles ...)

je pencherait davantage sur le code de mise en forme qui justement est absent.

Seul l'analyse du classeur en défaut permettra de voir sur quoi agir. (sans données confidentielles)

A+

 

[Misange]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Bonjour
Avec excel 2010 il n'y a pas besoin de macrco pour créer une ligne qui recopie les formats, les formules et les MEFC de la ligne du dessus, cela se fait tout seul en utilisant la fonctionnalité des tableaux !
PLus d'infos sur cet outil vraiment indispensable et méconnu ici
Ce lien n'existe plus.
onglet accueil/Style/Mettre sous forme de tableau, ça fait bien plus que simplement mettre des bandes colorées...

 

[loureed]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Réponse à Jean-Marcel :
Bonjour et merci de réfléchir pour mon problème, j'avais oublié de dire que j'ai stoppé le calcul automatique, j'ai stoppé la mise à jour de l'écran, et j'ai stoppé la détection évènementielle du classeur.

Réponse à Misange :
Il faut voir le lien... Toujours est-il que j'ai un fichier équivalent sous excel 2003 avec 160 fiches personnels et l'ajout d'une opération ne dure que 24 secondes... Hors là je suis sous 2010 et je me prend 6minutes et 36 secondes dans la tête !!!
Mais je vais regarder cette histoire de tableau, merci encore.

Réponse à Paf :
Il est vrai que le fichier exemple n'est pas aussi fourni que le vrai en ce qui concerne les mises en forme, qui lui gère jusqu'à 9 qualifications par personnel donc sur la partie droite de la feuille personnel se trouve des formules pour différents test de mise en forme :
exemple qualif_1 opération 1 à 6 mois de validité, qualif_2 opération 1 validité permanente donc on affiche l'opération une avec une validité à 6 mois mais elle est prise en compte pour les deux qualifs... si vous voyez ce que je veux dire... Mais sous excel 2003 ça ne dure que 24 secondes ! Alors 2010 a peut-être des contrôles internes qui tournent... Je ne sais pas.

Ci joint le fichier avec quelque rajout, celui ci prend en compte jusqu'à trois qualif par personnel et affiche les lignes en fonction

 

[Misange]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Bonsoir
Quelques raisons pour cette lenteur :
sur excel 2003 tu as 256 colonnes, beaucoup plus sur excel 2007 et +
Tu sélectionnes et actives alors que c'est inutile
la mise à jour de l'écran n'est pas désactivée et les calculs non plus
Mais surtout :Tes règles de MEFC se dupliquent à chaque tour. (regarde par exemple =C5=0 sur la feuille personnel2)


Je réitère mes conseils : utilise les tableaux, fais tes mefc sur les colonnes du tableau de façon à ce qu'elles ne se dupliquent pas.
Au besoin utilise un peu de code pour faire un resize du tableau et l'agrandir d'une ligne (regarde dans les pages qui suivent celle que je t'ai indiqué pour voir la manipulation des tableaux en VBA)

 

[MJ13]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Bonjour à tous

J'ai pas trop suivi l'affaire.

Mais les MEFC, j'en suis revenu. Sur un fichier qui ramait trop à cause des MEFC, j'ai du faire une macro pour les éviter .

 

[loureed]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Réponse à Misange :
je viens de tester les tableaux c'est impeccable... Il faut que je m'y penche car j'ai des formules de test par colonne pour savoir si pour une Kalif donnée sélectionnée pour un personnel si toutes les opérations sont effectuées avec un sommeprod et autre.

Mais j'aime bien l'idée des tableaux, merci.

 

[loureed]

Re : Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Pour Jean-Marcel :
Désolé je n'avais pas vu la petite subtilité...
Mais mon code en mode pas à pas montre que c'est bel et bien l'insertion de la ligne copiée qui dure trop longtemps, presque 7 secondes à elle seule, alors avec 160 personnels !!!!

Pour MJ13 :
C'est tellement plus beau quand en fonction de données le tableau change de couleur !!!
Mais à vrai dire je crois bien que les MEFC alourdissent les tableaux.

Pour Misange :
Petit test avec les tableaux et vraiment j'adore !!! Moi je dis J'ACHEEEEEEEEETE les tableaux !!!!
Il me reste plus qu'à savoir comment gérer les couleurs car sur certaine ligne il devra y avoir une donnée de date sans avoir de validité ni de butée et là j'aimerais que la ligne soit sur fond bleue pastel par exemple et que les cellules validité et butée soient grisées... Comment accéder à cette mise en forme ?

Merci encore à vous !

Faut absolument que je travail cela au plus vite.

 

[loureed]

Re : [résolu]Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Voilà le fichier final

Une feuille pour mettre à jour la base de données et les feuilles de personnel avec mise par ordre alphabétique des intitulés d'opérations.
Un système de filtrage pour ne faire apparaitre que les lignes nécessaire aux différente qualifications sélectionnées (4 au choix parmi les 10)

Merci encore à vous tous, il me reste à faire le test en multipliant les feuilles de personnels pour voir la différence de temps de traitement mais je pense que ce sera bien moins de 7 minutes !

Loureed

 

[loureed]

Re : [résolu]Pourquoi faut il plus de temps pour insérer une ligne dans une feuille ?

Bonjour,

Le test est bon avec un classeur comportant 110 personnels l'ajout d'une opération n'a pris que 7 secondes et l'ordinateur n'est un foudre de guerre ! Pour moi c'est GAGNE !!!

Je rappel la procédure lors du clic sur le bouton ajouter : insertion d'une ligne dans le tableau de la feuille BD, copie du nom de l'intitulé, copie des différentes valeurs dans les colonnes de qualifications, tri par ordre alphabétique
puis en boucle de la première feuille personnel à la dernière : insertion d'une ligne dans le tableau de la feuille, copie de l'intitulé de l'opération, tri par ordre alphabétique, puis filtrage sur la colonne affichage pour ne laisser visible que les lignes "vrai".

Merci encore à tous

Loureed

NB : chez moi sur excel 2007, pour le filtrage de la colonne "affichage" avec la condition VRAI j'ai du modifié la macro crée par excel au niveau de l'égalité : [...:="VRAI"] par [...:=TRUE]
mais arrivé au travail avec excel 2010 il a fallut que je remodifie la macro pour mettre [...="VRAI"] ?!?!
Bravo Microsoft ! à moins que ce soit du à une option quelconque du logiciel....
Bref, merci encore