XL 2016 lenteur code VBA

[sandydavneo]

Bonjour à tous,

je me permets de vous solliciter car je rencontre un souci sur un fichier créé par un modérateur du forum.
C'est un fichier incroyable, qui permet de changer les mois automatiquement en inscrivant une simple date et qui permet de calculer les jours fériés, nuits, jours ouvrés et dimanches sur des agents de sécurité.
J'ai récupéré cette matrice et j'ai formalisé un tableau pour mon utilisation.
Malheureusement, dès que je rentre des données, le fichier rencontre des lenteurs incroyables. A tel point, que par moment, le fichier plante...
Je n'y connais pas grand chose en programmation VBA et je n'ose surtout pas toucher ces lignes de programmation, peur de tout fracasser !!!

Svp, crack d'Excel et amis modérateurs, auriez-vous une solution miracle ?
Ou bien une autre façon de calculer ce que je recherche au travers de ce tableau fort utile mais inutilisable pour le moment ?

Je vous remercie par avance pour votre contribution.
Bon courage et bonne journée.

 

[fanch55]

Bonjour,
Je n'ai pas de problème avec ce classeur, mais je dois avouer que j'ai 32 Go de mémoire .
Je suis surpris par le résultat affiché quand j'ai fait ce test :

Je pense que @Bernard_XLD pourra vous apporter plus d'éclairage ...

 

[Dranreb]

Bonjour.
À mon avis la fonction Determine_Heures est lente parce qu'elle interroge tout au long des Range indiqués en paramètre ByRef
Essayez de mettre ByVal devant chacun et, au début de la fonction, une série d'instructions toutes de la même forme, la première étant: If TypeOf Jour_Ref Is Excel.Range Then Jour_Ref = Jour_Ref.Value
Ou demandez à l'auteur de le faire.

 

[Bernard_XLD]

Bonjour sandydavneo, fanch55, Dranreb, le forum

Je me souviens de cette fonction, adaptée à son problème, que j'avais monté en live pour Baptistou sur ce fil
https://excel-downloads.com/threads/jours-feries-dimanche-heures-de-nuit.20067046/#post-20506614
Je vais regarder votre fichier.

Cordialement, @+

 

[sandydavneo]

Bonjour à tous,
je vous remercie vivement pour toutes vos remarques et temps passé...
Bernard, merci beaucoup également, si je pouvais garder ce fichier, ce serait top car il est fait pour notre quotidien compta...
Dans l'attente, je vous souhaite une bonne après-midi.
David

 

[Bernard_XLD]

Bonjour,
Je n'ai pas de problème avec ce classeur, mais je dois avouer que j'ai 32 Go de mémoire .
Je suis surpris par le résultat affiché quand j'ai fait ce test :

Je pense que @Bernard_XLD pourra vous apporter plus d'éclairage ...

@fanch55 , je n'arrive pas à reproduire ton problème, et le 01/01/22 devrait être en fond jaune car férié
peux tu de nouveau essayer avec la version de ce post ?

@sandydavneo , quelques lenteurs chez moi mais rien de vraiment gênant. J'ai rectifié quelques erreurs de formules (modifications malheureuses ?) dans le tableau, j'ai mis à jour TJF (les accents avaient tous été modifiés dans la fonction ????) et j'ai passé les arguments de Determine_Heures en Byval comme le suggérait Dranreb, j'ai renseigné l'intégralité du mois en données et cela reste quasi instantané chez moi en temps de traitement, pouvez vous re essayer ?
Si vous avez toujours des problèmes, merci de préciser votre type d'ordinateur et si vous travaillez sur une version 64 bit d'Excel 2016, ou en ressource partagée.

Cordialement, @+

[Fichier mis à jour]

 

[sandydavneo]

Bernard,

tout d'abord merci beaucoup pour ces correctifs mais malheureusement c'est toujours la même chose... Le fichier est très long dès que l'on veut faire un copier-coller de 4 cellules par exemple.

J'ai regardé la version de ma collectivité d'Excel :
Microsoft Office Professionnel Plus 2016 32 bits (16.0.05332.1000)
De plus, les ordinateurs que nous utilisons sont équipés de 8Go de mémoire vive, Intel Core i5-1135G7 à 2.40 Ghz (autant dire que ce n'est pas folichon au niveau puissance !!)

Voilà ce que j'ai pu voir... Comment puis-je faire ?

Encore merci à toi.

 

[Bernard_XLD]

plutôt légères en mémoire les configs ! des portables en plus, très certainement avec des disques durs 5400 tours. Dès que la mémoire vive sature, ce qui doit être souvent le cas, l'ordi utilise le DD pour l'émuler ce qui est très très lent en comparaison. Vous êtes en windows seven ou windows 10 ?
Votre message m'a donné une idée, j'ai vérifié les MFC et il y avait effectivement des beans dedans, suite à des copier coller cellules certainement. Sur un fichier contenant des MFC, il vaut mieux faire des copier coller valeurs uniquement sur les cellules à modifier sans toucher aux cellules contenant des formules, les copier coller cellules coupent et dupliquent les MFC ce qui peut finir par ralentir considérablement un fichier.
Je les ai rectifié ce qui devrait améliorer un peu les choses.
Si c'est toujours lent, certainement dû à vos configs trop light (pas vraiment de solution dans ce cas), vous pouvez dans le menu formule, désactiver le calcul automatique le temps de vos mises à jour, et le réactiver ensuite ou le laisser désactivé et le lancer ponctuellement par la touche F9.

 

[sandydavneo]

Bernard,
je vous remercie pour tout.
Il est vrai que le fichier est plus efficace comme cela et beaucoup moins long.
Je vais essayer de faire de nouveaux tests un peu plus poussés avec ce nouveau fichier.
Vous êtes phénoménal sur ce logiciel !!!
Bravo et bonne fin de journée.

 

[sandydavneo]

Bernard,
je me permets de vous demander encore une chose:
Sur ce tableau, les agents ont tout de même beaucoup de saisie informatique à effectuer...
Serait-t-il possible de simplifier la saisie ?
Je m'explique :
Nous avons des évènements qui démarre à une date et qui finissent 7 jours après (par exemple une manifestation du vendredi 12/08 au vendredi 19/08 de 8h00 à 23h00).
Dans ce tableau, nous devons faire 7 fois la manipulation pour arriver à nos fins. C'est certainement pour cela que les calculs sont nombreux et de ce fait, très long.
Auriez-vous une solutions pour n'inscrire qu'une seule fois la date de début et la date de fin ? De façon à retrouver les calculs des heures effectuées en dimanche jour et nuit, en férié jour et nuit et en ouvré jour et nuit ?

J'espère avoir été clair dans mes explications.
Pour mieux comprendre, je vous joins un tableau de nos évènements.
Je suis même prêt à retravailler sur une autre forme de tableau si vous avez une idée.

Merci encore énormément pour votre contribution.
Bien à vous.

 

[Bernard_XLD]

Re,

Auriez-vous une solutions pour n'inscrire qu'une seule fois la date de début et la date de fin ?

On peut pratiquement tout faire avec Vba, je vais regarder mais pas trop le temps dans l'immédiat, alors si ce n'est pas trop urgent, oui c'est possible.

Cordialement, @+

 

[TooFatBoy]

Bonjour,

Il est vrai que le fichier est plus efficace comme cela et beaucoup moins long.
Je vais essayer de faire de nouveaux tests un peu plus poussés avec ce nouveau fichier.

Attention : la formule de la colonne "Total Mission" est fausse.

Un jour, s'il est effectivement constitué de 24 heures, est représenté sous Excel par la valeur 1 et non par 24. 😉

Donc au lieu de la formule

=SI(L4="";"";SI(K4<L4<24;L4-K4;SI(L4=0;24-K4;24+L4-K4)))

on pourrait utiliser une formule alternative comme par exemple celle-ci :

=SI(L4="";"";L4+(L4<=K4)-K4)

 

[TooFatBoy]

Ci-joint une proposition, dan laquelle j'ai effectué quelques modifications :
- modification de la formule de la colonne Total mission,
- modification de la formule des colonnes Coût HT,
- suppression de toutes les MFC inutiles (deux suffisent),
- suppression de la forme matricielle (qui me semblait inutile) des formules utilisant la fonction Determine_Heures,
- suppression de l'utilisation de la fonction TJF dans la MFC (qui ralentissait trop les calculs), et remplacement par l'utilisation d'un tableau de jours fériés.

 

[sandydavneo]

Bonjour TooFatBoy,
incroyable mais vrai !!!
Le fichier ne ralentit plus du tout sur mon ordinateur...
JE TE REMERCIE GRANDEMENT !!!
Je vais faire des tests dans la journée mais cela me semble topissisme.

Je vais mettre à jour tout le tableau avec tes nouvelles formules.

Je réitère ma demande faite à Bernard au cas où:
Nous avons des évènements qui démarre à une date et qui finissent 7 jours après (par exemple une manifestation du vendredi 12/08 au vendredi 19/08 de 8h00 à 23h00).
Dans ce tableau, nous devons faire 7 fois la manipulation pour arriver à nos fins.
Auriez-vous une solutions pour n'inscrire qu'une seule fois la date de début et la date de fin ? De façon à retrouver les calculs des heures effectuées en dimanche jour et nuit, en férié jour et nuit et en ouvré jour et nuit ?

Encore merci pour ta contribution et encore bravo pour tes compétences dans ce logiciel.
Bien à toi.

 

[sandydavneo]

Re,

On peut pratiquement tout faire avec Vba, je vais regarder mais pas trop le temps dans l'immédiat, alors si ce n'est pas trop urgent, oui c'est possible.

Cordialement, @+

Bonjour Bernard,
Je sais bien que l'on vous en demande beaucoup...
Merci à toi.
Je patienterai le temps qu'il faudra afin d'avoir un tableau certifié conforme...
Bonne journée à toi.