Acceleration code pour remplir un tableau

[Guigui]

Bonsoir à tous.

J'ai besoin de votre pour tenter de rendre plus rapide l’exécution d'un code que j'ai établis.
Actuellement, le code tourne pendant environ 6min ... !

Je suis tout a fait conscient que mon code doit être assez "sale" ou "une usine à gaz"
Je fais donc appel à vos connaissances pour voir ce qui est possible de faire. (tableau en variable, declaration correcte de variable ... ?)

Mon support est le suivant (je vous joint un exemple réduit)

Partant d'une liste d'agents comportant le type de contrat, la base horaire, la présence ou non suivant les mois et des catégorie regroupant les agents, je remplis un autre tableau (onglet "PREPA FICHIER CONSOLIDATION") qui va comptabilisé les items suivants :

Pour chaque mois ET pour chaque catégorie :
- le nombre de temps plein ou temps partiel total
- le nombre d'heure de contrat total
- le détail des temps plein ou temps partiel par type de contrat (CDI, CDD ..)

Mon exemple montre 9 agents et 2 catégories, le code est assez rapide (quoique ...) env 2 secondes
Mon fichier original comporte pas loin de 500 agents et 10zaine de catégorie ... 6min ..

Je ne maîtrise pas du tout les variables "tableau" (?), est ce une solution ?
Ou peut être une façon de déclarer correctement les variables ?

A noter que pendant l’exécution du code dans mon fichier original, "excel" passe en mode "NE RÉPOND PAS" après quelques seconde et se rétabli à la fin du code.

N'hésitez pas en cas de questions ... ou critique !

Merci par avance

Slts
Guillaume

 

[Jam]

Re : Acceleration code pour remplir un tableau

Salut Guigui, le forum,

Bon j'ai regardé ton code.
La première chose à faire c'est de définir tes variables. C'est un préalable OBLIGATOIRE pour un code lisible ET efficace.
Là, toutes tes variables sont équivalente à du Variant. Ça consomme de la mémoire, et ça oblige le moteur à analyser la variable avant de la traiter. Bref, ça perd du temps.
Autre chose, mets des commentaires dans ton code. Ça aide à le comprendre...là il faut passer en pas à pas pour savoir qu'est-ce qui fait quoi
Petit point à savoir: Lire une cellule est très consommateur de temps de calcul. Or tu passes ton temps (enfin ton programme) à lire des cellules. Il faudrait donc faire autrement. Une (la meilleur ) des solutions serait de transférer ton tableau en mémoire et de traiter ce tableau. L'inconvénient c'est qu'il faut que tu revois une grosse partie de ton code. Mais le gain de temps serait considérable.

Dernière chose: j'ai testé et n'ai pas rencontré de problème de durée avec ton fichier exemple. C'est quoi ta config ?

Bon courage

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour GUiGui,Jam
les conseils de Jam en pratique
voilà un exemple de code plus rapide
le résultat dans data
si tu as besoin de plus d'aide tu demandes(pas le temps d'aller plus loin)
indexer un tableau via dictionnary(origine JB)

Sub remplir_tableau_GLOBAL()
Dim Tbl, MonDico As Dictionary, MonDicoA As Dictionary, DateDebut As Date, DateFin As Date
Dim C As Byte, DerL As Long, L As Long, Ligne As Long, Indice As Long, Clé As String, CléBase As String

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
'MOIS_PRESENCE.Show

'tps = Timer

Application.Calculation = xlCalculationManual

DateDebut = "01/10/2013"
DateFin = "01/12/2013"

With Feuil7
 DerL = .Range("E65536").End(xlUp).Row
Tbl = .Range("A9:AC22") ' & derl)
End With

Set MonDico = New Dictionary

For C = 1 To UBound(Tbl, 2)
If Tbl(1, C) = DateDebut Then coldebut = C
If Tbl(1, C) = DateFin Then colfin = C: Exit For
Next C

        For L = 2 To UBound(Tbl, 1)
  MonDico(Tbl(L, 5) & "-" & Tbl(L, 14)) = Tbl(L, 5) & "-" & Tbl(L, 14)       'colonne E et N
Next L
'indexe le tableau
For Each Item In MonDico.Items
Set MonDicoA = New Dictionary

        For L = 2 To UBound(Tbl, 1)
            If Tbl(L, 5) & "-" & Tbl(L, 14) = Item Then 'colonne E et N
              CléBase = Item
              Clé = CléBase
              Indice = 1
            Do While MonDicoA.Exists(Clé)
              Clé = CléBase & Indice
              Indice = Indice + 1
            Loop
             MonDicoA(Clé) = L
    End If

        Next L
'écrit dans feuille
        lb = 10

        CléBase = Item
        Clé = CléBase
        Indice = 1
        Do While MonDicoA.Exists(Clé)
            Ligne = MonDicoA(Clé)
            With Feuil2    'résultat dans data
                .Range("A" & lb).Value = Tbl(Ligne, 5)
                .Range("B" & lb).Value = Tbl(Ligne, 14)
              For C = coldebut To colfin
         If lb = 10 Then .Cells(9, C - 12).Value = Tbl(1, C)
                .Cells(lb, C - 12).Value = Tbl(Ligne, C)
                Next C
'                .Range("D" & Lb).Value = Tbl(Ligne, 15)
'                .Range("E" & Lb).Value = Tbl(Ligne, 18)
'                .Range("F" & Lb).Value = Tbl(Ligne, 19)
'                .Range("G" & Lb).Value = Tbl(Ligne, 28)
'                .Range("H" & Lb).Value = Tbl(Ligne, 29)
'                .Range("I" & Lb).Value = Tbl(Ligne, 30)
'                .Range("J" & Lb).Value = Tbl(Ligne, 31)
'                .Range("K" & Lb).Value = Tbl(Ligne, 32)
'                .Range("M" & Lb).Value = Tbl(Ligne, 14)
'                .Range("Q" & Lb).Value = Tbl(Ligne, 16)
            End With
            lb = lb + 1
            Clé = CléBase & Indice
            Indice = Indice + 1
        Loop

Next Item

Application.Calculation = xlCalculationAutomatic

End Sub

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonjour à tous, Jam, Bebere,
Jam,
Merci pour ces conseils.
La déclaration des variables, je ne le faisais pas, pour gagner du temps .. c'est le comble
je vais essayer de reprendre ca

Concernant la mise en tableau je suis preneur mais patauge completement avec cette façon de faire !!
De l'aide serai la bienvenu
ou un bon tuto s'il exsite un.

Concernant ton dernier point, ma config à la maison est la suivante :
Intel core 2 quad Q6600 2.4ghz // 4go RAM // win7 64bits
Au travail, même "lenteur"
Intel core I5-3470 3,20GHz // 8 Go RAM / Win 7 64bits

Mon chere Bebere,
Merci pour ta proposition mais je bloque dés la 1ere ligne.
la déclaration "MonDico As Dictionary" me remvoie "Type défini par l'utilisateur non défini"

Je ne saisi pas ta derniere phrase "indexer un tableau via dictionnary(origine JB)"

Merci à vous 2
Slts

Guillaume

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Re,

J'ai reussi à réduire suffisament mon fichier original mais en gardant la totalité des agents et catégorie.
Permet de mieux se rendre compte de l'usine à gaz !!

Jam, peux tu le tester chez toi et me dire le temps que le code met pour tourner ?

Merci par avance

 

[Jam]

Re : Acceleration code pour remplir un tableau

Guigui, Bebere,

Bebere, l'utilisation du Dictionnary, si elle est une bonne chose en terme de qualité de code, en est une autre en terme d'efficacité (un Dico c'est lent). L'utilisation de Dictionnary est donc à mon sens inopportun dans le cadre du programme de Guigui.
L'idéal serait plutôt de coller le tableau en mémoire et de travailler dessus en mémoire. Vitesse de traitement attendus: quelques secondes pour plusieurs milliers d'enregistrement

Guigui, n'ayant malheureusement pas beaucoup de temps à y accorder tu trouveras ci-après quelques bouts de code et conseils qui devraient t'aider à y voir plus clair et à améliorer ton code:

'Pour mettre dans la procédure remplir_tableau_GLOBAL()
Dim colBH As Integer        'Colonne de la Base Horaire
Dim colCONTRAT As Integer   'Colonne du Type de Contrat
Dim colCONSO As Integer     'Colonne de Consolidation


Dim agent As Range
Dim cell As Range


Dim departR As Integer      'Ligne de départ du tableau
Dim departC As Integer      'Colonne de départ du tableau


Dim TP As Integer
Dim H_TP As Integer
Dim TPAR As Integer
Dim H_TPAR As Integer
Dim CDI_TP As Integer
Dim CDI_TPAR As Integer
Dim TTL_CDI As Integer      'Total des CDI
Dim CDD_TP As Integer
Dim CDD_TPAR As Integer
Dim TTL_CDD As Integer      'Total des CDD


Dim BASE_HOR As String
Dim CONTRAT As String


Dim colMois As Long
Dim rowMois As Long
Dim Ligne As Integer


Dim colMOIS_EN_COURS As Integer
Dim RowCATEGORIE As Long

• Il ne sert à rien de mettre tes variables à 0. Elles le sont par défaut, surtout si tu les types correctement.
• Pourquoi utiliser 2 fois des For each cell/agent ? Tu peux sélectionner toute la plage et utiliser à la place des For i=débutcolonne To Fincolonne et imbriquer la même chose avec les lignes. Ce sera plus efficace.
• De même tu te compliques la vie à déterminer les cellules de début et de fin comme ci-dessous

    'Recherche des colonnes Base horaire, type contrat et Consolidatino
    With [9:9]
        colBH = .Find("Base Horaire").Column
        colCONTRAT = .Find("Type Contrat").Column
        colCONSO = .Find("Categorie Fichier consolidation").Column
        départC = .Find([MOIS_DEPART]).Column
    End With

• Ah, et pour departR c'est forcément = à 9
• Avec le bout de code suivant tu peux sélectionner la plage à analyser en 2 lignes de code

    scelldépart = [9:9].Find([MOIS_DEPART]).Address
    Range(scelldépart, Range(scelldépart).End(xlDown).End(xlToRight)).Select

• Pour finir, dans tes If...Then, je ne te vois pas tester les cellules comportant "FDC". Si elles sont ignorées, autant rajouter:

If Not cell.value="FDC" then
            'EFFECTIFS TEMPS PLEIN + HEURES
            If (cell = "P" Or cell = "P > ABS") And Range(BASE_HOR) = [TEMPS_PLEIN] Then
            (....)
End If

Cela fera sauter tout les autres tests er gagner beaucoup de temps

Et un p'tit dernier pour la route:

 With Worksheets("PREPA FICHIER CONSOLIDATION")
            colMOIS_EN_COURS = .[1:1].Find(cellZone).Column
            RowCATEGORIE = 1
            
            '#Jam: un petit With supplémentaire ;)
            With .Cells(RowCATEGORIE, colMOIS_EN_COURS)
                .Offset(3, 0) = TP
                .Offset(4, 0) = H_TP
                .Offset(6, 0) = TPAR
                .Offset(7, 0) = H_TPAR
                .Offset(9, 0) = CDI_TP
                .Offset(10, 0) = CDI_TPAR
                .Offset(11, 0) = TTL_CDI
                .Offset(13, 0) = CDD_TP
                .Offset(14, 0) = CDD_TPAR
                .Offset(15, 0) = TTL_CDD
            End With
            
        End With

Bon courage

PS./ Avec ma config (portable i5, RAM: 4Mo, Win7 64...donc pas un foudre de guerre), cela a pris moins d'une minute pour tourner. C'est pas très long, mais cela peut effectivement aller beaucoup plus vite

 

[Jam]

Re : Acceleration code pour remplir un tableau

Guigui,

Petit conseil de base, va dans les options de l'IDE et coche la case "déclaration des variables obligatoire". Comme cela à chaque fois que tu ajoutes un module, tu auras la ligne Option Explicit en en-tête qui force la déclaration des variables

Ah, essaye de donner des p'tits noms à tes variables qui soient plus facile à lire et comprendre...et commente ton code aussi. Pour celui qui vient derrière pour t'aider c'est du temps de gagné dans l'analyse. Et ça n'a aucun impact sur la vitesse du code

Bon courage

 

[Bebere]

Re : Acceleration code pour remplir un tableau

Jam
bien au contraire,c'est ce qu'il y a de plus rapide comme code
avant de dire des choses aussi catégoriquement renseigne toi
GuiGui
il faut cocher microsoft.runtime dans références

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonsoir,

Jam, merci pour toutes ces astuces. je regarde tout ça et te tiens au courant.

Bebere, je vais tester également. Par contre, une question, est-ce qu'il faut cocher "microsoft.runtime" une seul fois ou bien faut il cocher dans tous les excels qui vont ouvrir mon fichier ? (différents utilisateurs)
(dans ce cas ... c'est simplement irréalisable en l'état !

Merci à vous

Slts
Guillaume

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Re Jam,

Alors j'ai testé certaines de tes astuces mais reste quelques zones d'ombre

* J'ai changer les variables INTEGER qui comptabilisaient les heures en LONG
j'atteignais un dépassement de capacité (32 767)
Dans le même style si je veux garder les décimales de mes heures de contrat (151,66 iso 152) je dois déclarer en SINGLE ??

* Pour les variables à 0 en début OK, j'ai compris le principe de déclarer. il faut pourtant que je les reset à 0 quand je change de mois sinon je fini ma période avec 5512 agents (au lieu de 300 et quelques)

* Nickel le fait de passer sur les cellules avec "FDC", j'ai rajouté également les "ABS LD" (non traité aussi)

* je n'ai pas saisi comment remplacer ta proposition dans mon code

Pourquoi utiliser 2 fois des For each cell/agent ? Tu peux sélectionner toute la plage et utiliser à la place des For i=débutcolonne To Fincolonne et imbriquer la même chose avec les lignes. Ce sera plus efficace.

Je vais voir pour peut être déterminer une "date de fin" pour limiter le traitement sur quelques mois au lieu de remplir le tableau sur plus d'un an (plus long et pas utile dans mon projet.

Tu parlais de "tableau", connais tu un tuto pour débuter dans ce domaine ? ou bien peux tu m'expliquer les bases de fonctionnement ?
Je vais essayer de comprendre le "tableau" de bebere (s'il s'agit bien de cela)

Merci encore

Slts
Guillaume

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour GuiGui,Jam
ton code s'exécute en un peu plus de 2 minutes,chez moi
cocher les références se fais une fois si tu distribues le fichier,c'est la même chose pour userform cocher MicrosoftForms 2.0 objets library
si tu veux en savoir plus sur les variables va sur le site de MDF,excellent tuto
oui c'est cela employer des tableaux,je te ferais un code du moins en partie ce week end
edit:tu pourrais faire par mois,par période,par année ce qui est intéressant pour toi

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonsoir à vous.

Merci bebere, je regarde ca ce week end.

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui,le forum
voilà un code(module1) pour temps 151,67
résultat dans data

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonjour à tous.

Bebere, merci pour ce code .. trés rapide !!

2 questions cependant si tu me permet.

* Ce code est-il utilisable sur des PC, où la coche "Microsoft runtime" n'a pas été coché auparavant ?

* le résultat en feuille data, ok. Mais comment le transférer sur ma page "PREPA FICHIER CONSOLIDATION" ?

par code ?
Du style que j'avais déjà effectuer en bouclant sur les "CATEGORIES" ??

par formule ?
j'ai essayer avec un index/equiv mais il me faut 3 critères pour choisir la ligne
(ex : "STRUCTURE" et "01/02/2014 et "P") or avec un index/equiv je n'arrive qu'a choisir qu'un critère)

Merci pour ton aide.

Slts

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui
si tu distribues le classeur pas de problème avec même version excel
tu peux aussi faire comme suit
Dim MonDico as Object
Set MonDico = CreateObject("Scripting.Dictionary")
à toi de dire quelle présentation tu préfères,il faut changer le code qui écrit dans la feuille
j'ai mis le résultat dans data pour laisser ta feuille comme elle est