XL 2013 Traitement trop long optimisation de code pour générer 2 états

[Merlin258413]

Bonjour à tous je me permets de solliciter votre aide dans le cadre d'une optimisation de code. Ces codes me permettent de sortir 2 états en TDC (ONGLET ETAT DES FAE attendu et ETAT DES FACTURES)
Ces codes fonctionnent parfaitement mais le temps de traitement est très long.
Ce fichier peut comporter énormément de ligne j'ai indiqué ici qu'une petite partie de janvier et février à fin octobre je suis à 1200 lignes d'où le temps du traitement.

Comme les 2 états sont issus du même tableau, une simplification peut être apportée, mais là j'ai atteint mes limites.
Mon projet en PJ explication
J'ai un onglet "Base" dans cet onglet j'ai aussi un tableau intitulé "base" source de mes TDC
Dans cet onglet lorsque je choisis un mois cellule C2 j'ai un code qui est généré.

1- le code va tout d'abord lancer une macro qui se trouve dans le module SUIVI
- Mise à blanc des états intermédiaires (FACTURE et FAE) qui servent à réaliser les TDC
- Section du tableau "Base" et copie sur l'état intermédiaire FAE
- Suppression des colonnes ne correspondant pas au mois sélectionné dans l'état FAE
- Suppression des lignes de cette colonne si j'ai la valeur vide ou ANNULEE
- Sélection de l'état intermédiaire FAE pour une copie dans l'état intermédiaire FACTURE : j'utilise cette étape intermédiaire car je n'aurais pas besoin dans mon état FAE de la colonne facture et dans mon état FACTURE de la colonne Programme
- Traitement de l'état FAE
- Suppression de toutes les valeurs différentes de FAE (à ce stade, je ne peux qu'avoir FAE, FACTURE ou ENCOURS)
- Tri par ordre croissant
- Traitement de l'état FACTURE
- Suppression de toutes les valeurs différentes de FACTURE (à ce stade, je ne peux qu'avoir FAE, FACTURE ou ENCOURS)
- Suppression des colonnes inutiles
- Tri par ordre croissant
- Protection de la feuille
2- Lancement du code MISE EN FORME ne concerne uniquement que la mise en forme de l'état des FAE avant d'obtenir le TDC final "ETAT DES FAE ATTENDU et enfin Actualisation de tous les TDC
J’ai réussi à réduire en le sauvegardant en xlb au lieu de xlsm.
Quand vous le télécharger il faudra le reconvertir en xlsm.
Le mot de passe de protection de l'onglet Base est ProgSN vous le retrouvez dans les modules
Ce fichier peut comporter énormément de ligne j'ai indiqué ici qu'une petite partie de janvier et février à fin octobre je suis à 1200 lignes d'où le temps du traitement.
En espérant avoir été clair et merci pour toute votre aide

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Vous avez en fin de l'état des facture un code qui explore toutes les cellules portant quelque chose en colonne A, et toutes celles qui doivent recevoir un total. En dehors d'en profiter pour mettre des bordures, rien n'empêcherait de modifier des .Interior.Color = RGB(255,255,0)
C'est bien d'avoir réussi à sortir les EN COURS.
Où en êtes vous de la refonte des FAE qui ne doit plus sortir le détail ?
À titre indicatif je vous livre les déclarations nécessaires et le début jusqu'au remplissage du tableau :

Sub RapportFAE()
Dim ColDate&, Te(), Le&, TLgn&(), LDéb1&, Ts(), Ls&, _
   BU As SsGroup, Acti As SsGroup, Prog As SsGroup, Presta As SsGroup, _
   Montant As Currency, Détail, C&, CelP As Range, Cel As Range

Rem. ——— Chargement des données
With FBase.ListObjects("Base").Range
   ColDate = WorksheetFunction.Match(FBase.[C2].Text, .Rows(1), 0)
   Te = .Rows(2).Resize(.Rows.Count - 1).Value
   End With

Rem. ——— Préfiltrage en vue utilisation fonction GroupOrg
ReDim TLgn(1 To UBound(Te))
For Le = 1 To UBound(Te)
   If Te(Le, ColDate) = "FAE" Then
      Ls = Ls + 1: TLgn(Ls) = Le: End If: Next Le
FFAE.Rows(4).Resize(20000).Delete
If Ls = 0 Then
   FFAE.[A3].Value = "IL N'Y A AUCUN ÉLÉMENT À LISTER"
   Exit Sub: End If
ReDim Preserve TLgn(1 To Ls)
MClassement.Préfiltrer TLgn

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Merci comme je disais pas trop de mérite pour les encours j'ai repris le code des FAE par contre je ne sais pas dans le code que je vous ai envoyé comme faire pour supprimer les colonnes RBU et interlocuteur (dans le code que je vous ai envoyé)

Enfin sur le rapport des FAE il reste à voir l'histoire des totaux de lignes dans les FAE pour une même BU même Programme et même prestation

Par exemple si j'ai 2 ligne dans le dauphin avec RBU Programme Interlocuteur Prestation identiques par exemple -100 et +120 ne me donner que la somme soit 20 sinon si la somme est égale à 0 alors ne pas mettre la ligne

Dans l'état des factures qui est un code bien différent comme peut -o,n inclure la notion "IL N'Y A AUCUN ÉLÉMENT À LISTER"

encore merci pour tout

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Ayez sous les yeux la feuille Cahier de charge: les numéros de colonnes en rouge indiquent ce qu'il faut mettre comme colonnes au GroupOrg et au Choose.
Votre code des EN COURS ne le respecte d'ailleurs pas. Il fallait un regroupement par Prestations, même si on ne veut pas de totalisations à ce niveau. Voue auriez eu meilleurs temps de partir du RapportFactu: c'était presque pareil.
Mais pour que ce soit plus clair et complet précisons d'abord dans le Cahier de charge, sous les colonnes à totaliser :
FAE : 4, 1, G (on ne veut pas le détail mais les niveau 4, 1 et général)
EN COURS : Dét, 2, G (on veut le détail, le niveau 2 et général)
FACTURE : Dét, 2, 1, G (on veut le détail et les niveaux 2, 1 et général)
Quand on ne veut pas le détail on ne peut pas bien sûr utiliser de fonction Excel, il faut cumuler soit même les montants de détail dans une variable. C'est le rôle de la Montant As Currency.

Bien sûr qu'il faut inclure partout et de la même façon ce IL N'Y A AUCUN ÉLÉMENT À LISTER

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Là vous m'avez perdu car je ne comprends pas ce qui composent
donc j'aurais du mettre

For Each Presta In GroupOrg(Te, 4, 2, , 3, 5) ' pourquoi il y a un vide entre 2 et 3
et
Ts(Ls, C + 2) = Détail(Choose(C, 3, 5,7)): Next C, Détail


Quand on ne veut pas le détail on ne peut pas bien sûr utiliser de fonction Excel, il faut cumuler soit même les montants de détail dans une variable. C'est le rôle de la Montant As Currency.

Pour le fichier factu j'ai fait cela mais cela ne fonctionne pas "la méthode Delete de la classe range a échoué"

Sub RapportFactu()
Dim ColDate&, Te(), Le&, TLgn&(), Ts(), Ls&, L1Prest&, Ls1erBU&, _
Presta As SsGroup, BU As SsGroup, Détail, C&, Cel As Range, Montant As Currency




Rem. ——— Chargement des données
With FBase.ListObjects("Base").Range
ColDate = WorksheetFunction.Match(FBase.[C2].Text, .Rows(1), 0)
Te = .Rows(2).Resize(.Rows.Count - 1).Value
End With

Rem. ——— Préfiltrage en vue utilisation fonction GroupOrg
ReDim TLgn(1 To UBound(Te))
For Le = 1 To UBound(Te)
If Te(Le, ColDate) = "FACTURE" Then
Ls = Ls + 1: TLgn(Ls) = Le: End If: Next Le

FFactu.Rows(5).Resize(20000).Delete

If Ls = 0 Then
FFactu.[A3].Value = "IL N'Y A AUCUN ÉLÉMENT À LISTER"
Exit Sub: End If

ReDim Preserve TLgn(1 To Ls)
MClassement.Préfiltrer TLgn

-

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Pour le rapport Factu c'est corrigé j'avais un problème de ligne

Voila il restera les encours car je bloque dans la mise en forme et les sous totaux
Je vous souhaite un excellent we et merci pour tout le temps accordé

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

pourquoi il y a un vide entre 2 et 3

Module MClassement, Function GroupOrg :

Rem. —— Renvoie une collection d'objets de type SsGroup. Ceux ci ont essentiellement
' une propriété Id (As Variant) et une autre Contenu (As Collection).
' Une propriété Tag leur a toutefois été ajoutée à toutes fin utiles à son exploitation.
' PlageOuTableau: La plage ou le tableau source de la collection désirée.
' ColOrd: liste de valeurs entières dont les signes représentent le sens croissant ou décroissant
' du tri des arguments, et les valeurs absolues leurs numéros d'ordre de colonnes source.
' Remarque: Cette liste supporte désormais en son sein un élément non numérique ou = 0.
' Celui ci marquera la fin des colonnes définissant l'organisation emboîtée du
' résultat, mais non la fin des colonnes spécifiables derrière, sur lesquelles
' portera encore le classement des lignes de détail.

J'ai donc laissé une spécification vide après le niveau 2 car pour les niveaux suivants il n'y a ni désignation de rubriques au début, ni cumul de détail au milieu ni totaux à la fin, ils ont juste besoin d'être classés. Ce ne sera plus le cas pour le nouveau RapportFAE: il faudra les 4 niveaux car on aura seulement besoin du cumul des détails pour le niveau 4. C'est pourquoi j'ai prévu pour celui ci 4 variables déclarées As SsGroup au lieu de 2 pour les autres. La structure algorithmique va donc bien évidemment être :

For Each BU In GroupOrg(Te, 2, 3, 5, 4)
   For Each Acti In BU.Contenu
      For Each Prog In Acti.Contenu
         For Each Presta In Prog.Contenu
            For Each Détail In Presta.Contenu
               Next Détail
            Next Presta, Prog, Acti
   Next BU

Voilà, voilà. Il ne vous reste plus qu'à mettre les instructions qui manquent là où il les faut, selon le résultat souhaité.



La mise en forme je l'avais faite à la main sur les colonnes entières. Peut être auriez vous intérêt à tout copier feuille Rapport FACTURE, le coller en 'Rapport EN COURS'!$A$1 puis supprimer les colonnes G:H et E.

À part ça vous ne devriez plus bloquer sur les en cours. C'est extrêmement proche des factures. La seule différence c'est qu'il ne faut pas sortir le total prestations, donc ne pas réserver de ligne munie du précurseur "=""R[" & L1Prest - Ls & "]C""" de sa formule.

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour j'espère que vous avez passé un bon week end
j'ai rajouté à la fin cette écriture dans mon état des encours mais bien sur cela fausse mon rapport à chaque actualisation
Columns("D:E").EntireColumn.Delete
J'ai retiré aussi quelques lignes

Sub RapportEnCours()
Dim ColDate&, Te(), Le&, TLgn&(), Ts(), Ls&, L1Prest&, Ls1erBU&, _
Presta As SsGroup, BU As SsGroup, Détail, C&, Cel As Range, Montant As Currency
Rem. ——— Chargement des données
With FBase.ListObjects("Base").Range
ColDate = WorksheetFunction.Match(FBase.[C2].Text, .Rows(1), 0)
Te = .Rows(2).Resize(.Rows.Count - 1).Value
End With


Rem. ——— Préfiltrage en vue utilisation fonction GroupOrg
ReDim TLgn(1 To UBound(Te))
For Le = 1 To UBound(Te)
If Te(Le, ColDate) = "EN COURS" Then
Ls = Ls + 1: TLgn(Ls) = Le: End If: Next Le
FEnCou.Rows(3).Resize(20000).Delete
If Ls = 0 Then
FEnCou.[A3].Value = "IL N'Y A AUCUN ÉLÉMENT À LISTER"
Exit Sub: End If
ReDim Preserve TLgn(1 To Ls)
MClassement.Préfiltrer TLgn

Rem. ——— Remplissage de l'image de l'état.
ReDim Ts(1 To 50000, 1 To 6): Ls = 0
For Each Presta In GroupOrg(Te, 4, 2, , 3, 5, 6)
Ls = Ls + 1: Ts(Ls, 1) = Presta.Id
L1Prest = Ls + 2
For Each BU In Presta.Contenu
Ls = Ls + 1: Ts(Ls, 2) = BU.Id
Ls1erBU = Ls + 1
For Each Détail In BU.Contenu
Ls = Ls + 1
For C = 1 To 4
Ts(Ls, C + 2) = Détail(Choose(C, 3, 5, 6, 7)): Next C, Détail
If Ls > Ls1erBU Then ' La dernière n'est pas la première, donc plusieurs.
Ls = Ls + 1
Ts(Ls, 2) = " Total " & BU.Id
'Ts(Ls, 6) = "=""R[" & Ls1erBU - Ls & "]C"""
End If: Next BU
If Ls1erBU > L1Prest Then Ls = Ls + 1 ' Début dernier BU plus loin que début Presta…
If Ls1erBU < Ls Then ' Donc: sauf s'il y en avait une seule dans la Prestation !
'Ts(Ls, 2) = "Total prestation " & Format(Presta.Id, "mmmm yyyy")
'Ts(Ls, 6) = "=""R[" & L1Prest - Ls & "]C"""
End If: Next Presta
Ls = Ls + 1
Ts(Ls, 1) = "Total général"


Ts(Ls, 6) = "=""R5C"""

Rem. ——— Production de l'état et ajout de formules.
FEnCou.Rows(5).Resize(20000).Delete
FEnCou.[A3].Resize(Ls, 6).Value = Ts
For Each Cel In FEnCou.[A3].Resize(Ls).SpecialCells(xlCellTypeConstants)
Cel.Resize(, 6).Borders(xlEdgeTop).Weight = xlThin
Next Cel
For Each Cel In FEnCou.[F3].Resize(Ls).SpecialCells(xlCellTypeFormulas)
Cel.FormulaR1C1 = "=SUBTOTAL(9," & Cel.Value & ":R[-1]C)"
'Cel.Borders(xlEdgeTop).Weight = xlMedium
Next Cel


End Sub

bonne journée

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour.

Il ne faut pas supprimer de colonnes: ça détruit les formats.

Pour que vos codes soient plus lisibles pour moi, voulez vous à l'avenir mettre entre crochets droits au début highlight=vb et à la fin /highlight

À part cela votre code semble bon sauf que vous avec oublié de ne pas reproduire la colonne 6 contrairement au cahier de charges, et qu'il reste du code qui ne me semble pas servir à grand chose entre le Next BU et le Next Presta vu qu'on ne veut jamais sortir de total Presta. Moi j'ai juste laissé Ls = Ls + 1 ' Sinon c'est pas beau je trouve…
Ah et puis le FEnCou.Rows(5).Resize(20000).Delete à la fin n'est plus utile: On le fait désormais au début pour qu'il ne reste plus, le cas échéant, que le "IL N'Y A AUCUN ÉLÉMENT À LISTER"

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour

Pour le tableau des encours je n'ai pas besoin de la colonne 6 celle des factures
Par contre je veux laisser la colonne prestation car logiquement, les encours s'annulent tous les mois mais il peut arriver que nous traînons des encours de l'année antérieure.
J'ai besoin dans cet état dans l’ordre :

1- du total par BU
2- Total par activités
3- les prestations


Concernant les modifications du code et la suppression de la colonne Facture je ne sais malheureusement pas faire et pourtant j'ai beau essayé Je me retrouve avec une erreur du type If sans end if hors si je regarde le code j'ai l'impression de les avoir .

Ne pas oublier que je suis novice je suis désolé dêtre pas à la hauteur mais je suis perdu

em. ——— Remplissage de l'image de l'état.
ReDim Ts(1 To 50000, 1 To 6): Ls = 0
For Each Presta In GroupOrg(Te, 4, 2, , 3, 5, 6)
   Ls = Ls + 1: Ts(Ls, 1) = Presta.Id
   L1Prest = Ls + 2
   For Each BU In Presta.Contenu
      Ls = Ls + 1: Ts(Ls, 2) = BU.Id
      Ls1erBU = Ls + 1
      For Each Détail In BU.Contenu
         Ls = Ls + 1
         For C = 1 To 4
            Ts(Ls, C + 2) = Détail(Choose(C, 3, 5, 6, 7)): Next C, Détail
      If Ls > Ls1erBU Then ' La dernière n'est pas la première, donc plusieurs.
         Ls = Ls + 1
         'Ts(Ls, 2) = "      Total " & BU.Id
         'Ts(Ls, 6) = "=""R[" & Ls1erBU - Ls & "]C"""
         End If: Next BU
   If Ls1erBU > L1Prest Then Ls = Ls + 1 ' Début dernier BU plus loin que début Presta…
  ' If Ls1erBU < Ls Then ' Donc: sauf s'il y en avait une seule dans la Prestation !
      'Ts(Ls, 2) = "Total prestation " & Format(Presta.Id, "mmmm yyyy")
      'Ts(Ls, 6) = "=""R[" & L1Prest - Ls & "]C"""
      End If: Next Presta
Ls = Ls + 1
Ts(Ls, 1) = "Total général"
Ts(Ls, 6) = "=""R5C"""

Rem. ——— Production de l'état et ajout de formules.
FEnCou.[A3].Resize(Ls, 6).Value = Ts
For Each Cel In FEnCou.[A3].Resize(Ls).SpecialCells(xlCellTypeConstants)
   Cel.Resize(, 6).Borders(xlEdgeTop).Weight = xlThin
   Next Cel
For Each Cel In FEnCou.[F3].Resize(Ls).SpecialCells(xlCellTypeFormulas)
   Cel.FormulaR1C1 = "=SUBTOTAL(9," & Cel.Value & ":R[-1]C)"
   'Cel.Borders(xlEdgeTop).Weight = xlMedium
   Next Cel

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Pour le tableau des encours je n'ai pas besoin de la colonne 6 celle des factures

Il ne reste donc que 5 colonnes à garnir. Alors pourquoi ReDim Ts(1 To 50000, 1 To 6) et pas ReDim Ts(1 To 50000, 1 To 5) ?
Pourquoi l'inclure à la fin au GroupOrg parmi l'ordre de classement des lignes de détail ?
Pourquoi l'inclure au Détail(Choose(C, 3, 5, 6, 7)) ?
Pourquoi garder la variable L1Prest ? Supprimez la des déclarations: elle servait à conserver le n° de la 1ère ligne de la Presta pour établir son total. Or vous n'en voulez pas de ce total.

Le total par activité c'est nouveau, ça. Ça ne figurait pas dans le cahier de charges. Il faut un SsGroup supplémentaire pour l'activité, alors et au GroupOrg inclure le 3 aux colonnes qui font l'objet de sous-groupes, avant l'argument vide.

Vous aviez plutôt un End If sans If, dans la mesure où si derrière un Then il y a immédiatement une instruction à la suite sur la même ligne, ça ne détermine plus un groupe If devant se terminer par un End If.

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Désolé tout simplement parce que je n'y comprend rien et je suis parti du rapport des factures.

Non vous avez raison je n'ai pas besoin d'un total par activités c'est bien conforme au cahier des charges d'origine seulement par BU.

J'ai beau essayé dans tous les sens je n'y arrive pas. j'essaierai de gérer à la main les encours pour pas vous prendre trop de temps.
Où si quelqu'un du forum peut m'aider à y voir plus clair je ne peux pas passez du niveau CP à Master d'un coup.



Par contre afin de finaliser et aller jusqu’au bout du projet concernant le rapport des FAE pouvez vous m'aider à obtenir la somme des lignes identiques même Activité Programme et Prestations car je n'y arrive pas et sans cela ce rapport ne me sera pas d'une grande utilité car c'est le plus important. Je ne sais rien ni même comme vous le dite les instructions
à insérer entre

Encore merci pour tout ce temps cette patience

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Alors c'est vraiment désespéré ?
Je vous ai pourtant indiqué tout le début nécessaire au poste #46,
et la structure de la suite, mais sans les instructions qui vont dedans, au #51

S'il avait néanmoins fallu un sous total par activité ç’aurait pu se faire comme dans la version GrpOrgMerlin258413(SsgAbandonné)

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Vous avez entièrement raison je suis un cas désespéré mais je vais analyser tout ça et je n'abandonne pas encore.

Je vous remercie pour tout.

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour si je désire intégrer dans le rapport FAE un sous total par activités. J'ai bien compris qu'il faut que je rajoute une variable : Acti As SsGroup. Dans le rapport initial j'ai une rupture par BU mais je ne sais pas comment rajouter le sous total et où


C'est abuser de vous demander encore votre aide ? Je sais ce n'était pas dans le cahier des charges intial

En vous remerciant et bonne soirée

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonsoir.
Normalement c'est fait dans le second fichier que j'ai joint au poste #57 à 14:41 pour les EnCours je crois.
C'est toujours le même principe: ajouter 1 à Ls puis installer la formule précurseur temporaire qui sera finalisé à la fin de la procédure.
Dans le RapportFAE le SsGroup Acti existe déjà. Il manque juste une variable Ls1Acti& pour y noter la 1ère ligne au début de l'Acti, et le précurseur de la formule à sa fin.

Edit. J'ai repéré des erreurs dans le code des EnCours pour le total Acti …qu'il ne fallait pas en fin de compte.
Par ailleurs un problème se pose s'il ne faut pas sortir les totaux pour des sommes ou paquets uniques: on serait presque obligé de mettre l'intitulé de l'activité sur une ligne séparée au début pour éviter toute confusion, comme pour les BU dans les factures.
Une autre chose me tracasse: on ne sort pas les montants qui s'annulent. Se pourrait-il que toute une activité s'annule, voire tout un BU, auquel cas il faudrait effacer aussi les intitulé de début correspondants ?