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

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui
refait en partie code userform
les index colonnes des dates sont dans dans les listbox
pour les cacher mettre columnwidth =0 dans userform_initialize
nbcat(me fait penser à un comptage et non à un tableau) est inutile, tblagent faisait la même chose

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonsoir le forum, bebere,

Le fait d'avoir changer les definitions de datedébut et datefin (colDeb et colFin) preserve bien la suppression des valeurs déja inscrites mais lors du final " Feuil1.Range("A1").Resize(UBound(TblResult, 1), UBound(TblResult, 2)) = TblResult", tout est "écrasé".
Une idée pour écraser la colonne A (ok) ensuite passer les colonnes "figés" et finir de transposer le tableau avec les valeurs ?
ex : on écrase la colonne A (pour remttre les titre de lignes et nom de catégorie..)
on ne touche pas à janvier, février (les valeurs précédentes sont maintenus)
on commence l’écriture dans les cellules que sur les colonnes Mars, Avril ....

Je ne vois nulle-parts ou est utilisé le Tblagent ?
En faite je voulais au lieu de "figer" dans le dure les différentes catégories, les récupérer en enlevant les doublons bien sur, depuis la colonne Q de la feuil7, mais ne sachant pas le faire directement, j'ai fait une étape dans la feuille DATA!
Donc Nbcat recupere le nombre de categories différentes et je m'en sert pour boucler "nbcat" fois pour definir la variable TblTypeContrat(b + 2) = Range("data!A" & b)


Vois tu ou je veux en venir pour les 2 points ?

Merci

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui,le forum
un autre code plus complet
changement dans code userform et code consolidation

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Merci Bebere,

Peux tu me confirmer, pour ne pas louper quelque chose, que le code démarre via le USF maintenant et non plus le bouton en feuil1 ?

Je regarde les nouveautés et reviens vers toi (mais ça m'a l'air magique !!

Guillaume

 

[Bebere]

Re : Acceleration code pour remplir un tableau

oui à partir de l'usf

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Re,

C'est parfait bebere.
Je ne maîtrise pas encore la création du tableau et le final au moment de l'appliquer dans la feuille mais bon ..

Un grand merci pour le temps passé sur mon projet.

Guillaume

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Un dernier ajustement bebere si tu me permet :

l'insertion des mois dans le tableau ne prend pas en compte le changement d'année
ex: dateDébut = 01/10/2013 DateFin = 01/01/2014
la boucle sur x ne se fait pas (for x = 10 to 1) ...

    For x = Month(DateDebut) To Month(DateFin)
        TblResult(C, 1) = CDbl(DateSerial(Year(DateDebut), x, 1))
  'MsgBox Format(TblResult(C, 1), "mmmm yy")
        TblResult(C, 2) = "Prévisionnel"
        TblResult(C + 1, 2) = "Réalisé"
        C = C + 2

    Next x

Une idée pour prendre en compte le changement d'année ?

 

[pierrejean]

Re : Acceleration code pour remplir un tableau

Bonjour Gugui

En l'absence de mon ami Bebere (que je salue)

For x = Month(datedebut) To Month(datefin) + 12 * (Year(datefin) - Year(datedebut))

 

[poulpor78]

Re : Acceleration code pour remplir un tableau

Bonjour,

A tout hasard, je n'ai pas ouvert le fichier mais j'ai lu le code sur la première page. Je vois des instructions dans le code qui engendrent des rafraîchissements d'écrans.
Par contre, je n'ai pas vu l'instruction au début : application.screenupdating=false.

Est-il possible de tester cela ? C'est souvent l'affichage qui est en retard dans nos codes.

Poulpor

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui,Pierre Jean,Poulpor
merçi Pierre Jean(salutation maître) tu me facilites la vie
le mien était plus long
le fichier avec le code de Pierre Jean
pour le moment dans le code il n'y a pas de screenupdating

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Bonsoir à tous,

Merci PierreJean pour ta proposition.
Bebere, as tu rajouté uniquement le code de PierreJean dans la PJ ?

Je vais mettre en variable les numéros de colonne (ex 13 dans : MonDicoA(Tbl(L, 13)) )
car dans mon fichier actif, j'ai supprimé des colonnes du coup ca ne pointe plus là ou il faut ...

Je compte définir ces numéros de colonne de la façon suivante :

ex : la colonne 13 est celle des "base horaire"
dim colBH as long
colBH = [9:9].Find("Base horaire").Column

et ensuite :
MonDicoA(Tbl(L, colBH))

Voyez vous un autre moyen plus "propre" pour mettre en variable ces num de colonne ?

Merci par avance

Slts

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui
tbl contient les données de la feuille new tbd
Tbl(L, 13) 13 est l'indice de la colonne M
si tu as supprimé des colonnes il suffit de mettre l'indice de la nouvelle colonne(exemple colonne J indice 10)

 

[Guigui]

Re : Acceleration code pour remplir un tableau

Oui j'entend bien mais c'étais pour eviter de corriger le code a chaque fois en cas de suppression ou rajout de colonne dans le futur (au cas ou).

 

[pierrejean]

Re : Acceleration code pour remplir un tableau

Re

Une proposition par une fonction

Function ColBH()
For n = 1 To Sheets("NEW TDB").Cells(9, Columns.Count).End(xlToLeft).Column
  If Sheets("NEW TDB").Cells(9, n) = "Base Horaire" Then
    ColBH = n
    Exit Function
  End If
Next
End Function

 

[Bebere]

Re : Acceleration code pour remplir un tableau

bonjour Guigui,Pierre Jean
tu as "base horaire" dans la 1ère ligne de tbl
une boucle et colbh est initialisé(prepare)
changement effectué