Procédure trop grande

Bonjour à tous ,

suite à une grosse procédure que j 'ai réalisé, Excel m'a refusé la compilation car ma procédure est trop grande. Ayant un niveau très faible en programmation vba, je n 'arrive pas à réduire le code (quoi que j'ai peut être une idée avec une boucle for qu'il faut que je test mais pas sur que ca suffira). J'ai bien pensé a une solution comme scinder ma procédure en plusieurs petites procédures, mais va falloir que je mette ces procédures dans un seul et même module pour pouvoir mettre les dim de mes variables dans le module pour garder en mémoire les valeurs(soit à l 'extérieur de la procédure principal), mais pour tester mon programme, impossible de savoir les valeurs que prennent mes variables au fur et à mesure car elles sont à l 'extérieur de ma procédure. Bref

En fichier joint mon code d'environ 4500 lignes (très répétitif).

Mon code sera dans mon rapport de stage, donc si possible ne pas mettre trop d'éléments directement écrit sur le forum car mon école utilise un logiciel de plagia. Ce serai dommage d'avoir 0 pour plagia alors que c 'est mon propre plagia lol...

merci à tout ceux qu' y me conseilleront et m 'aideront

Bonjour

Regarde le code.

Quoi qu'il arrive tu mets :

Sheets("SAGE").Cells(14 + LigneSage, 7).Value = Sheets("pointage 2017 2018").Cells(558 + a, 4 + b).Value
Sheets("SAGE").Cells(14 + LigneSage, 10).Value = Sheets("pointage 2017 2018").Cells(2 + a, 3).Value

Comme ces deux lignes reviennent 362 fois, en les mettants une bonne fois au début de ta boucle, tu gagnes .... 724 lignes.

For b = 0 To 110
If Sheets("pointage 2017 2018").Cells(1, 4 + b).Value <> "" Then
    For a = 0 To 549
    Sheets("SAGE").Cells(14 + LigneSage, 7).Value = Sheets("pointage 2017 2018").Cells(558 + a, 4 + b).Value ' 'on met le matricule
    Sheets("SAGE").Cells(14 + LigneSage, 10).Value = Sheets("pointage 2017 2018").Cells(2 + a, 3).Value 'on met la date de fin car ca c'est terminé maintenant
    If Sheets("pointage 2017 2018").Cells(2 + a, 3).Value <= Datefin And Sheets("pointage 2017 2018").Cells(2 + a, 3).Value >= Datedebut Then 'si la date est comprise entre les date selectionner par l'administrateur faire
'Etc....

Mets tout de suite toute ta colonne 8 au format "0000" (362 lignes de gagnées).

Ensuite tu peux supprimer toutes tes initialisations de variables à 0 (ce sont des integer donc elles sont automatiquement à 0) 38 lignes de gagnées.

Cordialement

Salut Antony, Efgé,

sacré travail de bénédictin, dis-donc! Tu as du y gagner tes premiers cheveux blancs!

Il nous faudrait un semblant de fichier pour comprendre qui, que, quoi dans cette affaire mais j'ai la furieuse impression que cela pourrait tenir en moins de lignes que tu penses.

J'imagine que c'est un fichier top secret. Un début de quelque chose avec les intitulés de colonne, une dizaine de lignes, même remplies de blabla et de chiffres bidon serait un plus.

Une explication quant au but de l'opération (une sorte de bilan, dirait-on) et l'élément déclencheur (un bouton?) seraient très appréciés aussi.

Un tableau avec l'intitulé de la variable, son code Motif et Nature permettrait peut-être de réaliser le tout en une bouclette.

En tout cas, à l'aveugle, je ne m'y aventure pas.

A+

Bonsoir,

Salut à tous !

J'ai ouvert par curiosité après avoir lu le post d'Efgé ! Avec quelques autres exemples de ce type, ton code devrait donc pouvoir se réduire à quelques dizaines de lignes... ?

J'ai lu aussi tes considérations pas très cohérentes sur tes variables, je n'ai pas compris, mais considère que ce que tu dis est faux ! Cela pourra t'éviter des erreurs !!

J'ai donc voulu voir de plus près ! et je ne suis pas déçu ! D'abord tu devrais commencer par apprendre que le code ça s'indente ! VBA ou le compilateur se moquent bien de l'indentation, mais pour une personne humaine qui le lit, l'indentation est ce qui permet de lire (vite) en sachant ce qu'on lit ! Et ce n'est nullement dénué d'intérêt pour jouer à programmer... !

Ta liste initiale de variables m'a impressionnée ! Je vais te faire une confidence : au-delà de 3 caractères je commence à trouver qu'un nom de variable est long ! Alors, en voyant les tiens...

Je ne les ai par aillleurs pas comptées, mais permets-moi de douter de la nécessité d'autant de variables ! En outre quand on choisit d'opérer avec un (certain) nombre de variables, il est bien rare qu'elles ne tendent pas à se regrouper naturellement, ce qu'on met à profit pour déclarer des tableaux visant à les regrouper effectivement, plus aisés à déclarer et éventuellement à manipuler...

D'ailleurs, en regardant juste un peu plus loin, tes séries d'affectations, j'ai tendance à penser que la plupart de tes variables sont inutiles, qu'elles auraient été remplacées plus efficacement par des tableaux, permettant de travailler au maximum hors feuille Excel, et d'affecter ensuite par ligne entière...

Revenons à notre point de départ, ta longue liste de varaibles se double à la suite d'une liste non moins longue où tu initialises tes variables à 0 !!!!

Il faut apprendre que lorsque tu déclares une variable, elle existe et prend une valeur par défaut ! Une variable objet aura la valeur Nothing tant que tu ne l'auras pas initialisée avec une référence d'objet, une variable String a la valeur "" (chaîne vide), une variable Integer (et toute variable numérique) a la valeur 0, une variable Variant a la valeur Empty (vide, qui renverra vrai si tu la questionnes avec IsEmpty, mais également si tu le fais avec "" ou 0, comme c'est d'ailleurs le cas avec les cellules, qui se comportent de la même façon que des variables Variant...)

Donc ta longue liste d'initialisations à 0 est tout particulièrement inutile ! Autant de lignes à supprimer.

Encore un mot sur les répétitions, déjà signalées par Efgé. Lorsqu'un élément se répète on le met sous bloc With, cela raccourcira le code... mais on a intérêt à placer des blocs With chaque fois qu'on le peut, car cela garantira une exécution plus rapide...

Mais comme je l'ai déjà soulevé, tes affectations répétitives indiquent qu'il y a intérêt à travailler sur tableaux...

Je ne vais sûrement pas lire les 4500 lignes... Il m'apparaît certain que même considérablement raccourcie, ta procédure devra être scindée... on dit plutôt modularisée, selon un découpage autant que possible rationnel, permettant d'éviter des répétitions de pans de codes... certains éléments pouvant être renvoyés par des proc. Function auxquelles la proc. principale ferait appel au fil des besoins...

Comme l'a suggéré Curulis, il conviendrait de reprendre l'ensemble au niveau analyse...

Pour être plus clair, mon conseil est de foutre ces 4500 lignes à la poubelle ! Sans regret : dis-toi que cela te servira de les avoir écrites, ton code ne comporte pas de nombreux défauts de débutants qui le rendent illisible... il est relativement clair. Le problème est une méconnaissance des outils VBA qui permettent de coder autrement les mêmes choses, et plus efficacement. Cela ne s'apprend pas en un jour, mais un travail concentré sur tableaux et boucles, utilisation des instructions VBA, approfondissement des variables, programmation modulaire... te montreront qu'on peut faire des progrès considérables rapidement...

Mais il serait vain d'essayer d'améliorer le code tel qu'il se présente ! Il sera infiniment plus rapide de repartir de l'analyse de la question... et le simple fait d'éviter ce qui a été fait ici et à abouti à produire une procédure fleuve, se traduira immanquablement par des améliorations...

Bon courage ! Cordialement.

Bonjour et merci d'avoir pris du temps pour me répondre.

Je vais tenter de reprendre la procédure à zéro mais franchement je vois pas du tout comment je peux faire autrement.

Pour expliquer ce que je veux faire :

dans une feuille appelé pointage 2017 2018 on y trouve un planning qu'y permet de connaitre le nombres d 'heures d'absences, le type d 'absence (soit grace à une couleur soit grace à un commentaire). Dans ce planning, des liaisons avec d'autres fichier Excel qui servent de pointeuse existe afin dé récupérer leurs heures (j'ai fait un programme qu'y s'utilise avec une tablette et ca badge les salariés).

Maintenant ma procédure récupére les informations pour les mettre dans un tableau qui servira pour SAGE ( import dans sage).

Actuellement mon programme fonctionne comme ca :

il demande la date de debut et de fin

grace à une boucle on peu test sur toute l 'année mais pour le bulletin on s'interesse uniquement à un mois c'est pourquoi j'ai borné avec datedebut et datefin.

Ensuite pour chaque personne il va aller de jour en jour pour trouver un cas d'absence trouvable soit grace a une couleur soit grace a un commentaire dans une cellule. Par la suite il récupére l'information, il inscrit la date de debut de l'absence dans le tableau pour l 'import de sage puis il va sur un autre jour. Si le motif de l'absence est toujours identique le total va s'incrémenter de la valeur qui se trouve dans la cellule sinon il va prendre fin et ecrire le matricule , la date de fin, les divers code correspondant à l 'absence et aisi de suite jusqu ' a la fin du mois pour après passer au deuxième salarié ...etc

Ensuite, ce que j 'ai pas encore fait , il y a un deuxième tableau pour sage à importer , donc a remplir , c'est celui concernant leure heures payé, travaillé, moduler acquis, moduler pris qui fonctionnera presque pareil que le programme ci dessus.

Oui c'est déclenché par un bouton

EDIT à 17:56 pour éviter de faire des double poste =) :

J'ai finalement refait un nouveau programme , ca m'a pris 3 heures mais au moins j'ai que 150 lignes. Je test ma procédure demain matin et je vous la transmets pour que vous me disiez ce que vous en pensez.

Voici mon nouveau programme , j'ai tout repris à zéro comme vous me l 'avez conseillé.

Par avance désolé pour mes nom de variable super long =s

J'ai un probleme lorsque j'excute mon programme.

Il semblerai que la ligne avec la lecture du commentaire ne fonctionne pas et je vois franchement pas ou est l 'erreur ....

Commentaire = ActiveCell.Comment.Text 'commentaire prend la valeur qui se trouve dans le commentaire de cette cellule

Salut Antony,

bel exploit! De 4500 à 150 en 3 heures : magnifique!

Autre point : pour éviter de copier 10.000 fois tes références à tes feuilles, déclare-les!

Dim sWkP As Worksheet, sWkS As Worksheet
Set sWkP = Worksheets("pointage 2017 2018")
Set sWkS = Worksheets("SAGE")

Ainsi, en utilisant WITH...END WITH tu peux te référer à telle ou telle feuille sans tout écrire à chaque fois. Le petit point placé devant un élément de cette feuille s'y rapporte automatiquement.

With sWkP
    For b = 0 To 110
        For a = 0 To 549
            c = 0
            If Not IsDate(.Cells(2 + a - 1, 3).Value) Then
                c = 1
            End If
            If .Cells(2 + a, 3).Value <= DateFin And .Cells(2 + a, 3).Value >= DateDebut Then 'si la date est comprise entre les date selectionner par l'administrateur faire

Plus loin dans ta procédure, tout un bloc IF...ENDIF se rapporte à 'SAGE' : tu fais la même chose.

            With sWkS
                If (Absences2 <> Absences1 And ValeurAbsences2 <> 0) Or (Absences2 = "" And ValeurAbsences2 = 0 And ValeurAbsences1 <> 0) Then
                    If c = 1 Then
                    .Cells(14 + LigneSage, 10).Value = sWkP.Cells(2 + a - 6, 3).Value 'on met la date de fin avec - 6 ligne pour enlever le week endcar ca c'est terminé maintenant
                    .Cells(14 + LigneSage, 12).Value = ValeurAbsences1 * 24 'je met la valeur total de l 'absence
                    c = 0
                Else
                .Cells(14 + LigneSage, 10).Value = sWkP.Cells(2 + a, 3).Value 'on met la date de fin car ca c'est terminé maintenant
                .Cells(14 + LigneSage, 12).Value = ValeurAbsences1 * 24 'je met la valeur total de l 'absence en heure décimal
                End If

Dans ce cas-ci, les références à 'Pointage 2017 2018" doivent être évidemment re-précisées, d'où l'intérêt de déclarer ces feuilles sous un nom plus court et facilement repérable.

Ma demande reste : peux-tu reproduire ton tableau de bord que nous puissions saisir la structure de ton tableau?

A+

Bonjour Curulis57 ,

je vais utiliser ta méthode qui me parait bien plus efficace. Par contre je pense pas que ca règlera mon probleme.

J'ai créé un petit classeur type test pour que vous puissiez m'aider.

Merci vraiment pour tout ce que vous faites

Faites le test du 01/11/2017 au 24/11/2017 soit le mois de novembre

Moi quand je le fait , seul la première ligne s'écrit correctement ensuite quand il commence à détecter le premier commentaire du type .comment ca merde je sais pas si c 'est à cause de activecell ou autre ... pourtant desfois sa remarche quand je rallume mon ordi mais du coup il me sort pas ce que je veux , fin bref

il me dit erreur 91 bloc with non défini alors que j 'ai pas encore mit de with dans mon programme ....

Salut Antony,

plus efficace, en terme de résultat, non, mais, en lisibilité, avec une bonne indentation des lignes, oui...

Pour ton ActiveCell, ben, en fait, à mon sens, elle n'est pas vraiment active puisque non sélectionnée.

Essaie ceci :

                If .Cells(2 + a, 4 + b).Comment Is Nothing Then 'si cette cellule n'a pas de commentaire vide
                    Commentaire = .Cells(2 + a, 4 + b).Comment.Text 'commentaire prend la valeur qui se trouve dans le commentaire de cette cellule

Je regarde ton fichier dès que j'ai 5'...

A+

Salut Antony,

voilà déjà un premier dégrossissage! On tombe à moins de 100 lignes!

Côté rapidité du code, il faudra attendre que la brume se lève : un peu tard, là!

J'ai retravaillé ton tableau de codes ABSENCES en y intégrant les codes EVENEMENTS : aucune raison de faire deux tableaux d'autant que le deuxième ne reprenait même pas les mêmes intitulés!!!

Tu peux le compléter à ta guise : la macro s'adaptera toute seule.

Je ne sais pas comment tu ajoutais les commentaires mais, pour te faciliter la tâche et éviter les erreurs de frappe réduisant à néant la recherche, j'ai intégré la fonctionnalité suivante :

• clic droit sur une cellule ouvre une USF qui te liste dans une ListBox tous les codes ABSENCE existants ;
• tu remarqueras le tiret en première position! ;
• un clic sur une des options l'inscrit en commentaire (création ou modification) ;
• le tiret signifie que tu souhaites supprimer le commentaire.

Je ne sais pourquoi certains commentaires étaient récalcitrants à être trouvés par FIND alors que bien présents (Absence maladie, entre autres). Intégrés avec ma USF, plus de soucis! Va comprendre!

Ce qui veut dire.. qu'il y a des risques que cela se reproduise dans tes 110 * 550 cellules!

Teste et dis-moi! Comme j'imagine très bien que ce serait oh combien fastidieux de tout remplacer par l'USF, je trouverai bien un truc...

Dernière chose, j'ai corrigé certaines fautes d'orthographe dans les intitulés (thérapeuthiques, entre autres) et mis "Congé" au singulier partout.

Ce sera sans doute un problème aussi mais je n'ai pas pu m'en empêcher : ça m'énerve!

Comme je suis parfois un mêle-tout, j'ai "corrigé" aussi, voire inventé, certains codes NATURE : A pour Absence, C pour Congé,...

Tu te feras un plaisir, j'en suis sûr, de jouer au jeu des 7 erreurs!

A ton tour de bosser, maintenant!

A+

Bonjour curulis , c'est super tes simplifications , j'avais encore jamais codé comme ca et effectivement c'est plus court ^^.

Désolé pour les fautes d 'orthographes =s c'est franchement pas mon point fort lol. Concernant le tableau SAGE, c'est pas moi qui l'ai créé mais des partenaires de SAGE afin de paramétré celui ci. Donc j'ai pas osé toucher les codes afin d'éviter d'éventuel beuger lors de l 'import dans sage.

J'ai tester ton programme, il compile très bien sans beug par contre il ne fait pas ce que je veux. Certaines lignes on plein d 'élément manquant du type le matricule , la date de fin ...etc . Je vais travailler sur ca ce matin pour enlever ces anomalies. Mais un grand merci à toi , ca va vachement m'aider.

Antony

Salut Antony,

la deuxième ligne est sous commentaire : enlever l'apostrophe!

                        If Absences1 <> "" Then
                            'ValeurAbsences1 = .Cells(2 + a, 4 + b).Value
                            sWkS.Cells(14 + LigneSage, 7).Value = .Cells(558, 4 + b).Value 'on met le matricule
                            sWkS.Cells(14 + LigneSage, 9).Value = .Cells(2 + a, 3).Value 'on met la date de début
                            Absences2 = "En attente"
                            Commentaire = ""
                        End If

A+

Salut Antony,

si tu pouvais m'expliquer la logique de ta procédure, je pourrais sans doute améliorer...

Commente ton processus et l'utilité de tes variables (c,...)

A+

Re bonjour ,

bon j'ai finalement trouvé pourquoi j'avais l 'erreur 91 avec l 'activecell

Pour créer un commentaire , je m 'aide d 'une procédure que j 'ai réalisé et qui fonctionne via un bouton , tous les types d 'absences sont dans une liste et mon programme prend l 'absence dans la liste et la met dans le commentaire

sauf que lorsque j 'ajoutais un commentaire je faisais .visible=false . Du coup dans mon programme , pour lire le commentaire j 'ai mis juste avant .visible= true et la magique ca marche nickel.

Maintenant j'avais exactement le même résultat que toi, certaines cellules n 'étaient pas rempli , certaines dates de fin était erronées voir meme pas misent. Bref j'ai passé 2 heure se matin à régler se problème. Maintenant mon programme marche très bien et il fait exactement ce que je voulais.

Désolé de ne pas avoir directement travaillé avec ton programme , j'ai essayé , mais je comprends rien du tout à la logique. Mon niveau est tellement faible que j 'ai galéré. Du coup je l 'ai fait sur mon programme en compilation pas à pas.

En revanche, maintenant je vais utiliser les with et je vais voir pour les autres truc que tu as fait dont je comprends pas la logique pour le moment afin de l'adapter a mon programme.

Par contre mon programme est sacrément lent sur 110 personnes , 1 minute au moins . Je vais peut etre faire un userform pour créer une barre de chargement.

Je met ci joint mon programme si ça t'intéresse comment j'ai fait pour débloquer ma problématique et en même temps tu verras ce que fait mon programme.

ps : c'est super dure à décrire le programme car j'ai tellement synthétisé pour éviter d 'avoir 4500 lignes qu'il a fallu faire un genre de condensé Booléen (avec plein de et , de ou , de no etc). Absences 1 à été créer pour la premiere situation détecter , seulement si le jour qui suit cette situation change , je dois pas perdre la situation 1 donc j'ai créer la situation 2 avec absences2 , et vue qu 'on a changé de situation 1 on va pouvoir selon le cas le réinitialisé et lorsque on fait ça la situation 2 devient la situation 1 et la situation 2 est vidé et l 'ancienne situation 1 à été entièrement écrit dans le tableau pour ne plus le mémoriser dans le programme. Ainsi je vais écrit dans mon tableau tous les cas d 'absence correspondant à chaque salarié.

Salut Antony,

dur, dur la programmation? Un peu de logique à partir du moment où on sait ce qu'on veut!

Voici une version à ma sauce après avoir (presque) tout jeté, ainsi que le préconisait MFerrand, qui a toujours raison!

En jouant avec la mise des diverses données en tableaux, je gage que tes 110 colonnes seront avalées en quelques toutes petites secondes!

Je n'ai plus qu'un problème : le format de ces foutues dates qui se mélangent dans le tableau des résultats!

Eh, les cracks, un coup de pouce?

A+

Re bonjour ,

oh oui j'ai beaucoup de mal , a la base je déteste ça mais bon finalement je commence à y prendre goût ^^.

Effectivement ton programme va beaucoup plus vite que le mien lol.

Par contre il y a pleins d 'anomalies sur le résultat quand je compile il y a plein d 'erreur avec les dates certain on très bien fonctionné par contre les premières lignes m'envoi carrément sur des mois de fevrier et mars alors que j'ai fait le test sur le mois de novembre. D'ailleurs on remarque qu'il y a des la même chose à chaque fois qu'il passe à un nouveau salarié.

Ensuite c'est le matricule, il s'écrit que un seul fois sur la première ligne et après plus rien, j'ai besoin que chaque ligne du tableau soit rempli. Vive SAGE lol

Par contre que ce soit les codes , les valeurs totales ou les motifs ils sont tous correct. C'est étonnant ce problème de date et de matricule. Je vais voir plus en détail ton code.

Je sais pas combien de fois je t'ai dit merci , mais MERCI BEAUCOUP =)

Salut Antony,

non, rien d'étonnant!

• le n° d matricule sur la seule première ligne du salarié, c'est fait exprès! Je vais donc retirer les lignes ad hoc ;
• dans la refonte de ta macro, j'ai oublié le test d'encadrement des dates.

Je vois cela tout de suite!

A+

Voilà Antony,

les corrections sont apportées qui m'ont permis d'en découvrir une petite troisième... chhhttt!

Evidemment, rien de plus embêtant que de ne pas pouvoir tester en grandeur nature!

Rappel :

• tu peux augmenter le nombre de colonnes à volonté ;
• tu peux augmenter le nombre de lignes à volonté (pas les jours de l'année, hein! ) ;
• tu peux ajouter autant de codes ABSENCE que ton imagination peut en créer.

A+

Salut ,

ah bha voila on commence sérieusement à ce rapprocher du résultat =). Manque plus qu' 'à trouver la solution avec les dates et puis c'est bon =). Pour le moment j'ai pas trouvé la solution avec ton code mais je lâcherais pas le morceau =).

Antony

Salut Antony,

pour les dates, je me souviens maintenant qu'il faut inverser dd/mm en mm/dd...

sWkS.Range("I14:J750").NumberFormat = "mm/dd/yyyy;@"

Maudits anglais!

A+

Bonjour ,

effectivement sacrée anglais ^^ , en modifiant ça maintenant ca marche nickel. Merci beaucoup pour ton aide.