Calendrier des absences automatisé - Vitesse de chargement

Hello le forum,

Je viens aujourd'hui sollicité votre aide car mes connaissances ne me permettent pas de faire mieux actuellement.

J'ai réalisé il y a déjà quelques temps, un calendrier pour gérer les présences et absences du personnel. Il est utilisé régulièrement, marche très bien.
Cependant, plus la base de données s'alourdie plus il devient lent (logique...), mais je suis sûr qu'il y a d'autre moyen afin de le rendre plus rapide.

Vous verrez que lorsque les mois et années changes, le calendrier se charge en supprimant toutes les données, les rechargeant et appliquant différentes mise en forme.
J'ai remarquée que la procédure prenant le plus de temps était la celle qui colorie les cellules selon les notes. (Module note, ColorNote)

Mais certainement que les autres procédures peuvent être améliorer aussi pour alléger le temps de chargement.
La macro rafraichir m'avait été proposé par Steelson sur le forum, je l'ai très peu modifié depuis.

Pour récapituler, est-ce que vous auriez des méthodes qui m'aiderais à effectuer un chargement plus rapide ?

PS : Vous remarquerez que j'ai enlever toutes les fonctions qui n'ont pas de lien avec la vitesse du calendrier.

Merci d'avance !
Kilian

bonjour,

Je n'ai pas trop creusé parce que je ne veux ronger l'os a Steelson qui est assez grand pour y retrouver ses petits...mébon, au lieu de faire 3 colonnes, Intérim, Stagiaire et Apprenti, tu n'en aurais fait qu'une avec "I", "S" ou "A" tu n'aurais pas besoin de boucler 3 fois sur toute ta BD donc déjà tu diviserais par 3 le temps de traitement.

A+

Hello Galopin01,

Merci pour ta réponse.

Effectivement comme j'ai ajouté par la suite apprenti et stagiaire, j'ai bêtement dupliquer le code ( La facilité...).
Je vais me pencher la dessus.

Par contre, pour la procédure ColorNote, je boucle et met le texte en rouge directement, est-ce qu'il y aurais un moyen qui me permettrais de récupérer se résultat, le stocker et mettre la mise en forme et une fois à la fin ? Je me dis que ceci réduirais considérablement le temps de traitement.

Aussi peut-être qu'il existe un autre moyen de parcourir mon tableau, car la feuille BD_ABS grandit et rallonge constamment mon temps de traitement.

A+,
Kilian

Encore une fois je n'ai pas planché sur votre sujet, car le travail de Steelson me semble correct, mais comme dans beaucoup de cas chaque développeur à ses propres habitudes, cela m'obligerait à tout reprogrammer si je ne veux pas perdre un temps fou à m'adapter à la production de Steelson.

Ce qui est chronophage ce sont les interactions directes sur le classeur. Pour l'essentiel Steelson a travaillé sur des Arrays ce qui bien.

Malheureusement les Arrays ne gèrent pas les mises en formes donc les pertes de temps sont dues à 99 % par les mises en formes cellules par cellules : Je n'ai pas analysé plus à fond le code, mais j'ai cru comprendre que pour l'essentiel ces mises en forme pourraient être remplacées par des MFC (Mise en Forme Conditionnelle). Sous réserve (peut-être) de quelques modifications de codification pour la colonne "Type" :

Là encore une codification alphanumérique ou purement numérique serait bien suffisante. Une BD ne se lit pas comme un journal (surtout si elle pilotée par VBA donc inconnue de l'utilisateur lambda). Donc vous n'avez pas de nécessité d'utiliser des traductions littérales à la codification cela ne fait qu'alourdir le classeur inutilement...

Si vraiment il n'y a pas de possibilité de traiter par MFC, en dernier ressort, une solution serait de rajouter une colonne à la DB (colonne codée elle aussi en alphanumérique) ; Nouvelle colonne qui traduirait de manière stockable dans un Array ce que vous êtes obligé de déchiffrer péniblement dans votre Feuille.

Ainsi au lieu de parcourir sans cesse votre feuille vous n'auriez à parcourir que l'Array (ce qui divise le temps de traitement par 20...)

Aujourd'hui c'est stade et demain... Dimanche. On verra si Steelson se manifeste. Sinon je verrai la semaine prochaine si je me lance dans votre truc. Ce n'est pas inintéressant... Mébon ! C'est toujours chiant de reprendre le travail de quelqu’un d'autre...

A+

Galopin,

Je ne demande pas de revoir le travail de Steelson, qui est très bien fait.
De plus il m’a aider pour une partie de ce projet il y a plus d’un an, et je ne demande pas à ce qu’il reprennent forcément du temps sur ce travail.

Comme je l’ai dit, la procédure la plus lente est bien une mise en forme que tu le souligne également.

J’ai bien essayer de le faire via MFC mais sans succès ( je m’y prend certainement mal).

Je ne demande pas à ce qu’on me ponde un code tout fait que je ne comprendrais pas, bien au contraire, ceci ne ferais qu’étouffer mon apprentissage en VBA.

Un simple piste sur le fonctionnement que tu évoques serait amplement suffisant.

J’ai essayer un code du style « Set MaPlage =« mais là je suis bien incapable de trouver la bonne syntaxe ou bonne fonction pour y arriver.

Ton explication sur cette colonne avec un Array m'intéresse mais je n’arrives pas à me l’imaginer.

Bien entendu, ne prend pas de temps sur ton week-end sur ce problème, c’est loin d’être bloquant, le fichier fonctionne très bien, il s’agit simplement d’une amélioration pour le bien de l’utilisateur.

Je te remercie d’ores et déjà pour tes réponses et te souhaites un agréable week-end,

Kilian

Je pense qu'il va falloir que tu revoies les choses peut-être autrement. En programmation les choses évoluent tellement vite que l'on est vite dépassé si l'on ne s'adapte pas... C'est encore plus vrai quand on débute. Ne te fais aucune illusion le bon programme c'est celui que tu utiliseras 10 ou 20 ans, à la condition que tu l'auras revu de fond en comble au moins une dizaine de fois. En adaptant au fur et à mesure ce que tu as trouvé de meilleur...

Malheureusement, je ne crois guère possible de remettre juste un pansement sur ta jambe de bois. En programmation quand tu déclares une variable publique dans un module, tu es bien forcé d'en tenir compte dans le reste de ton classeur et comme tu as une tripotée de déclarations de ce genre. Moi je ne commence pas à envisager quelque chose tant que je sais que quelque chose risque de me les briser... et ce n'est que la face émergée de l'iceberg. De nombreuses choses on un impact sur le reste : La manière de nommer tes variables et des tas d'autres routines de programmation...

Ces déclarations à la noix hors Sub en tête de module pour moi c'est incompatible avec ma programmation donc tant que je n'ai pas tout les éléments en main je suis coincé.

Donc je vais effectivement te donner des pistes... Totalement incompatibles avec l'existant. C'est la raison pour laquelle beaucoup de programmeur, y compris (surtout) les professionnels ne se donnent pas la peine de reprendre ce genre de programmation : Cela leur prendrait dix fois plus de temps que de refaire le programme en entier...

Donc on essaie de comprendre le besoin, et on essaie de le traiter au mieux. Mais c'est le rarement en rajoutant une rustine par-ci par là : On abouti en général à une usine à gaz. Et quand on s'en rend compte c'est là qu'on se dit : Bon il va être temps de recommencer sur de meileures base...

Bon enfin moi c'est comme ça que je fonctionne.

Donc comme pour l'instant je suis un peu disponible, je vais réfléchir sur ton truc et je te montrerai ce a quoi je suis arrivé... Si mon essai est concluant ! Après, tu en feras bien ce que tu en veux... Je ne t'en voudrai pas si tu jettes.

Au reste s'il y en a qui ont d'autres idées, ils ne se priveront pas pour les exprimer... C'est comme ça que ça marche sur les forums...

A+

Pour compléter ma réflexion sur les choses compatibles et celles qui ne le sont pas et sur les améliorations souhaitables.

La première chose à faire serait d'adopter Option Explicit en tête de tes modules.

La plupart des programmeurs y compris les professionnels s'y plient sans état d'âme : C'est une saine habitude de programmation.

Ensuite les déclarations : Sauf cas très particulier ça se place dans chaque Sub juste après la première ligne. Et on n'en sème pas ensuite en tant que de besoin au fil du programme sinon après celui qui veut savoir... ben il faut qu'il se tape de nombreuses pages de lecture pour savoir ce qui lui pend au nez...

Allez encore un exemple il y a dans ta Sub MEF un paragraphe :

With Range("Tableau2")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Pour le même résultat, moi j'écris :

Range("Tableau2").Borders.LineStyle = xlNone

A ton avis qu'est-ce qui est le plus rapide pour VBA ? Se taper les 10 lignes de ton code ou la mienne ?

Bon je veux pas polémiquer hein ! c'est juste un exemple... Mébon des exemples comme ça j'en ai toutes les 2 lignes. Alors si vraiment tu veux qu'on ne touche à rien... Ben...

A+

Galopin01,

Merci pour ton retour.

J'ai bien compris que ma façon d'écrire du code ne te convient pas. Mais l'important il me semble, c'est que je m'y retrouves.
Le résultat de mon programme aujourd'hui est le fruit d'une multitude de version que je m'efforce d'améliorer au fur et à mesure que mes connaissances augmente, alors non je ne suis pas professionnel, et non je ne me repose pas sur mes lauriers.

Je prend bonne note de tes remarques et vais tâcher de les appliquer au mieux si elles peuvent m'aider dans mes futures projets.

J'ai l'impression avec ton texte de te demander de revoir l'entièreté de mon programme, ce qui est loin d'être le cas. Comme je l'ai dit, je veux simplement quelques pistes, conseils pour m'aider à m'améliorer.

Pour Option Explicit, j'ai aussi comme mot d'ordre de toujours le mettre, et je place mes variables dessous car elles sont répétitives et m'évite de les replacer dans chaque Sub, mébon comme tu dis, c'est ma cuisine.

Pour ton exemple concernant ma MEF, bien entendu ta solution est meilleur et je vais de suite l'appliquer.
Je n'ai jamais dit que je ne voulais qu'on touche à rien, juste jeter un coup d'œil et me dire ce qu'il y aurais à changer en me donnant des suggestions.
Ce que tu fais et je t'en remercie, mais attention à la forme que tu y met.

Je te laisse profiter de ton week-end, et si tu as des remarques ou suggestion pour m'aider à progresser, je les prend volontiers !

A+,
Kilian

Oui, oui... T'inquiète... Galopin est un vieux chnok et j'ai gardé mon vocabulaire d'antan et je parle encore de tête de nègre, de pute et de pédé et ça n'a rien à voir pour l'estime que je porte à certaines personnes. Mon truc c'est le contenu : Pas le papier cadeau... L'important c'est de se comprendre ! Je suis très capable de comprendre que certaines personnes sont possiblement plus compétentes que moi dans de nombreux domaines. Mais si elles le sont alors elles doivent certainement être capable de comprendre quand j'exprime des faits probablement justes...

D'ailleurs en principe quand je répond à quelqu'un c'est que j'estime qu'il est en mesure de comprendre sinon je m'abstient...

Bon ceci dit je pense qu'on ira pas plus loin ensemble je viens de découvrir tout un placard de déclarations de variables alphabétiques auxquelles je suis allergique.

Dim dlg$, lig$, nb%, derL%, i%, j%, m%, a%, x%, Y%, d%
Dim tablo()
Dim motifs, DemiMotifs As Object, cle As Variant
Dim jours, matricules, TABLEAU, absences, Demi
Dim WsC As Worksheet, WsD As Worksheet
Dim Jour As Range, inte As Range

Ça rend le truc incompréhensible et surtout non modifiable car on ne peut pas faire de rechercher/remplacer pour une lettre de l'alphabet. donc je vais certainement laisser tomber.

Mon conseil pour t'améliorer c'est d'abandonner ce genre de cuisine. Même les professionnels (que je ne suis pas !) évitent ce genre de déclarations qui produisent du code en "plat de nouilles". Bon enfin tu en fais ce que tu veux hein... Là encore ce n'est pas de la critique juste pour démolir mais bien pour faire progresser : Les seules déclarations à mettre en tête de modules sont celles qui ont un caractère indispensable à cette place. Pas celles que tu juges moins fatigantes à répéter :

Les Declare PtrSafe Function SetWin ... ont vocation à être en tête de module pour le reste NON ! Seule Option Explicit est de rigueur. Et si tu dois poser un Dim quelconque ce n'est pas parce qu'on utilise " i " à tous les étages mais pour transmettre la valeur du " i " d'une macro à une autre ou à un UserForm...

L'histoire des MEF : C'est simplement une hypothèse de travail, Hein je ne suis pas assez avancé dans l'analyse du truc pour être catégorique.

Il y a d'ailleurs un truc que je comprend pas trop dans le remplissage du planning : Tu as éclaté les gens en trois catégories les

Interim
Apprenti
Stagiaire

Et les autres ceux qui sont No partout et Yes nulle part, je n'ai pas vu comment ils remontent au planning ?

Tu peux me déniaiser sur ce point ? Sinon je vais pas passer ma nuit la-dessus...

A+

Galopin,

Je prend bonne note pour la déclaration des variables en tête de module.

Pour les catégories, à la base il n'y avait que la catégorie Intérim (et donc une seule colonne), que je reprend dans le Sub Interim.

A la base, je reprenais juste si = Yes, alors ajoute une couleur de fond au calendrier.
Mais comme tu me l'a souligner, il est effectivement plus judicieux de ne faire plus qu'une colonne et qu'une procédure.

Je penses qu'ici l'amélioration serait de revoir le fonctionnement du changement de fond des cellules en une fois et non pas à chaque passage dans la boucle, qu'en penses-tu ?

A+,
Kilian

Bien sur !

...Mais là je te répond "à l'intuition. Il faut quand même tester... Je vais le faire en n'utilisant que la colonne G et les initiales I, A, S.

A+

EDIT : Je te donne également le module MiseEnPage un peu optimisé :

Sub MEF()
Dim i%, plage As Range
Application.ScreenUpdating = False
With Sheets("Calendrier")
    .Unprotect
    Range("Tableau2").Borders.LineStyle = xlNone
    For i = 1 To 5
        Set plage = .ListObjects("Tableau2").ListColumns(i).DataBodyRange
        With plage
            .BorderAround LineStyle:=xlContinuous
            .BorderAround Weight:=xlMedium
        End With
    Next i
    For i = 1 To 25 Step 5
        Set plage = Range(.ListObjects("Tableau2").ListColumns(i + 5).DataBodyRange, _
            .ListObjects("Tableau2").ListColumns(i + 9).DataBodyRange)
        With plage
            .BorderAround LineStyle:=xlContinuous
            .BorderAround Weight:=xlMedium
        End With
    Next i
    .Protect
End With
Application.ScreenUpdating = True
End Sub
Sub RESETINTERIOR()
Dim plage As Range
Application.ScreenUpdating = False
With Sheets("Calendrier")
    .Unprotect
    Set plage = .Range(.Cells(5, 6), .Cells(.Range("A" & .Rows.Count).End(xlUp).row, 30))
        With plage.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    .Protect
End With
Application.ScreenUpdating = True
End Sub

Hello Galopin,

Merci pour ton retour sur le module MEF.

ça marche évidemment très bien, et je le comprend !

A+,
Kilian

J'ai passé presque toute la journée dessus hier et j'en ai encore pour un moment parce que je suis obligé de tout reprendre...

La on on n'est plus dans le domaine de la petite soluce rapide et on sort un peu de la finalité du forum...

Donc je laisse tomber ce sujet provisoirement. Ça motivera peut-être quelqu'un d'autre pour fourrer son dedans...

Si j'arrive à déployer quelque chose, je te l'enverrai mébon pour l'instant fait comme tu peux.

A+

Hello Galopin,

Merci de ton retour, pas de soucis je comprend bien.
Merci beaucoup pour tes remarques et le temps que tu as passée sur ma demande.

Le sujet reste donc ouvert, et si quelqu'un d'autre veux bien me donner des pistes, je suis preneur !

A+,
Kilian

Hello le forum,

N'ayant toujours pas trouver de moyen, je me tourne aujourd'hui encore vers vous.

Est-ce que quelqu'un pourrais m'aiguiller sur la façon de faire une variable plage de cellule non contigu, que je colorie en une seule fois svp

Merci d'avance,
Kilian

Bonjour,

Sub Test()
Dim rng As Range
Set rng = Range("A7:F7, I7:L7,N7:P7")

    rng.Interior.Color = 255

End Sub

ou encore

Sub Test()
Dim rngPier As Range
Dim rngPaul As Range
Dim rng As Range

Set rngPier = Range("A7:F7, I7:L7,N7:P7")
Set rngPaul = Range("A9:H9, M9:P9,Q9")
Set rng = Union(rngPier, rngPaul)

    rng.Interior.Color = 255

End Sub

A+

bonjour Kilian1906,

avec un minimum d'interference avec le fichier et le max en mémoire.

(PS. le temps dans le message est plus petit que la présicion d'excel et tous les variables sont des variants, s'ils ne sont pas explicit déclarés)

Sub ColorNote2()
     Dim arrC, arrC1, arrDB, loC, loDB, UN As Range, iL, iC, Row5, t, MyMin, MyMax
       t = Timer

     With Sheets("Calendrier")
          .Unprotect
          Set loC = .ListObjects("Tableau2")     'ce tableau
          arrC = loC.DataBodyRange.Value2     'données >>> array
          arrC1 = loC.DataBodyRange.Columns(1).Value     'matricules >>> array
          Set UN = loC.HeaderRowRange.Cells(1)     'set UN avec une cellule dehors le databodyrange
          Row5 = loC.Parent.Rows(5).Resize(, loC.ListColumns.Count).Value2     'lignes avec les dates
          MyMin = Application.Min(Row5)     'date début
          MyMax = Application.Max(Row5)     'date fin

          arrDB = Sheets("DB_ABS").ListObjects("absences").DataBodyRange.Value2     ' ce tableau >>> array

          loC.DataBodyRange.Offset(, 5).Resize(, loC.ListColumns.Count - 5).Font.ColorIndex = 1     'côté droite de ce tableau Font.Colorindex 1

          For i = 1 To UBound(arrDB)     'boucle données DB_ABS
               If arrDB(i, 6) <> "" Then     'll y a un remarque
                    If WorksheetFunction.Median(MyMin, MyMax, arrDB(i, 2)) = arrDB(i, 2) Then     'Abscence de est dans ce mois
                         iC = Application.Match(arrDB(i, 2), Row5, 0)     'colonne date correspondant
                         iL = Application.Match(arrDB(i, 1), arrC1, 0)     'row employee correspondant
                         If IsNumeric(iL) Then Set UN = Union(UN, loC.DataBodyRange.Cells(iL, iC))                              'matricule employee trouvé, ajoute cette cellule à UN
                    End If
               End If
          Next

          Set UN = Intersect(UN, loC.DataBodyRange)     'eliminer tout ce qui est dehors le tableau
          If Not UN Is Nothing Then
               UN.Font.ThemeColor = xlThemeColorAccent5
               MsgBox "colorer les cellules " & UN.Address & vbLf & "réalisé en " & Format(Timer - t, "0.00\s"), vbInformation, UCase("colornote2")   '----> effacer ou bloquer cette ligne plus tard
          End If

          .Protect
     End With
End Sub

Hello Galopin, BsAlv,

Merci à tous les deux pour vos réponses.
Galopin, j'ai fait des tas d'essaye dans ce style sans réussir à faire quoi que soit... J'ai dû mal mis prendre.

BsAlv, ton code marche merveilleusement bien. Je vais me pencher sur ton code pour en comprendre les lignes.

Merci beaucoup à vous deux ! Sujet désormais clos.