Planning : calcul et macro lente
Bonsoir le forum,
J'essaye d'automatiser un planning.
A partir de 12 mois (1 feuille par mois contenant +/- 35 personnes environ) je fais un récap (feuille "récapgéné") des codes horaires. (exemple: le nombre de fois qu'une personne travaille de matin (code "1") les samedis compris entre deux dates).
- Les formules (sommeprod) permettant de calculer ce récap se basent sur la feuille BD.
- Cette feuille BD est générée via une macro qui me liste l'ensemble des codes horaires par personnes et par jour.
Le temps d'éxécution de la macro et les calculs sont très lents. (c vrai cela génère pas mal de lignes)
2 questions :
1-Est-il possible d'optimiser le code de ma macro (dans le module1). (j'ai déjà désactiver le calcul auto et l'activescreenupdating pdt l'éxécution)?
2- Est-il possible de transformer mes calculs de la feuille récapgéné via une fonction vba?
(j'ai cherché mais là je galère sur les "function" vba).
Merci d'avance pour intérêt et si vous avez des pistes.
Cdlt, Al49
ci joint le fichier
oups pas de fichier
Help, je cherche comment posté mon fichier il est trop lourd
Bonjour,
J'ai l'impression qu'il n'y a pas grand chose à faire : cette BD était une fausse bonne idée car les multiples SOMMEPROD à rallonge sont une plaie...
C'est sans doute pour cela que la plupart des gestionnaires RH font des totaux mensuels sur chaque ligne avec NB.SI (ensuite YAPUKA faire une consolidation sur la récap...)
A mon avis seul les BD* (fonctions de Base de données) peuvent concurrencer les SOMMEPROD sur ce terrain mais cela demanderait tellement de mise au point pour la prog que le jeu n'en vaut pas la chandelle.
Je serai curieux de voir si quelqu'un d'autre relève le défi...
A+
Bonsoir
Merci d avoir regardé
J ai essayé la sommation mensuel mais l inconvénient est que je ne peux faire de récapitulatif entre une date de début et de fin (à la semaine).
De plus si une personne vient à changer de place entre deux mois la formule de récap ne fonctionne plus.
Tant pis j vais essayer de trouver autre chose.
A moins que qqun veut s y essayer comme tu le dis.
Pourquoi tu as la première ligne vide dans la feuille BD ?
Bonjour,
Je vais plancher là-dessus, mais compte tenu de mes obligations (et de la complexité), n'attend pas de retour avant une semaine...
Y a-t-il une raison particulière (impossibilité) pour que ce fichier ne soit pas en .xlsm ?
A+
Bonjour et avant tout Merci galopin01.
Pas de soucis pour l attente.
En ce qui concerne la version c que nous sommes actuellement en transition entre deux versions excel (97 vers 2010)
Cependant les personnes utilisant ce fichier sont toutes sur 2010.
Bonne journée et encore merci
Bonjour,
Ci-joint la première étape de ma réflexion.
J'ai juste "normalisé" les noms et adapté les formules en conséquence.
Idem pour la macro ou j'ai besoin de repères fiables.
Il me semble que le gain de temps est déjà perceptible.
J'ai encore un problème avec tes fériés (Pourquoi avoir sorti Pâques des fériés???)
Me dire si tu es Ok avec ma présentation des fériés.
Si l'ensemble est correct et te convient, je tenterai la suppression des SOMMEPROD
A+
Bonsoir Galopin01
Quelle rapidité, c'est nickel.
Pour la date de Pâques c'est une erreur en faisant une version du fichier pour le forum. Donc pas de soucis à l'intégrer dans la liste des "fériés".
Sinon RAS ça me va et la rapidité est meilleure effectivement.
NB: je travaille pour que l'horaire que l'on saisie ou que l'on modifie dans un onglet des mois se fasse en même temps dans la BD afin d'éviter une mise à jour à chaque instant de cette BD, mais mon niveau VBA me permet pas d'être aussi rapide.
J'ai également essayé rapidement les BDNBVAL pour remplacer les SOMMESPROD mais là je débute
(exp :=BDNBVAL(A1:D10204;iDW;(DateW>DEBUT)*(DateW<FIN)*(NameW="AAA1")) me donne #VALEUR les matrices ne sont pas de même taille sans doute).
Bref, je cherche.
En attendant ton boulot me va impeccable. Merci
Y a ballon maintenant, bonne soirée
Cdlt, Al49
Non, non les BD** ne fonctionnent pas comme ça... j'ai essayé mais dans ce cas de figure c'est décevant. J'abandonne donc cette hypothèse.
Je suis finalement arrivé à bout des féries mais à l'horizontale comme toi. ça ne me satisfait pas trop (du tout !) mébon... en attendant c'est encore quelques secondes de gagnées... (Finalement résolu sur le .xlsb...)
La mise à jour de la BD au fil de l'eau et des modifs j'y crois pas trop (du tout !) Je vais juste essayer de voir si je peux encore gratter un peu sur mes formules parce que il me semble qu'il y a encore à faire et je te tiens au jus.
Edit : Je viens de tirer encore un peu sur la ficelle j'arrive à 3" env. sur mon tabazou pour les recalculs : je pense que c'est Top difficile de faire mieux...
Nota : Il y aurait surement intérêt à enregistrer ça en xlsm, voire même en xlsb. Difficile de le mettre en évidence mais il me semble bien qu'en .xlsb le recalcul est encore plus rapide (2" à peine)....
j'ai mesuré le gain de temps (Mise à jour de la base de données compris) entre la V2 et la version en .xlsb en laissant le recalcul automatique !
V2 : 14.71484
V .xlsb : 6.148438
Hum... Et compare un peu la taille des fichiers !
Je n'ai pas essayé avec ton classeur d'origine...
A+
Bonjour Galopin01
J'ai regardé et ça me plaît. J'avoue que j'ai mis un peu de temps a déchiffrer, par contre 2 questions :
1) je bloque sur la formule des fériés du format .xlsb
2) Après recherche il est dit que le format .xlsb ne peut lire les macros alors que la mise à jour de la BD fonctionne
Je vais mettre en application tes formules au boulot (ça risque de prendre un peu de temps (il faut que je retape tout en dehors de mes horaires et la com internet est filtrée (normal)).
Merci et bon Dimanche
Bonjour,
Non, non pas de souci pour les macros le seul inconvénient est que ce format n'est pas compatible avec les tablettes et les téléphones. (non xml) et les versions d'Excel antérieures à 2007 (Quoique... il y a un
)
Il y a peut-être aussi des problèmes de compatibilité avec les personnalisations de rubans mais ce n'est pas du tout certain. A vérifier le cas échéant.
Pour les fériés l'ancienne formule renvoyait Vrai si ((DateW=Fériés_N)+(DateW=Fériés_N1)) était vérifié.
De la même manière (NB.SI(Feries;DateW)) renvoie 1 (Vrai) si la formule est vérifié.
Je te joins un explication détaillée de l'ensemble des modifs.
Pour le code VBA pas la peine de tergiverser tu utilises d'emblée le code de la V6.xlsb
A+
Merci pour les explications c nickel
Quand au complément sur ma version au boulot j ai intégré, un commandbar qui permet d'afficher les horaires prédéfinis (dans une plage de cellules) et lorsque l on remplies les cellules d un mois on vient capter la valeur + la mise en forme de la cellule du code horaire. Je vais voir si ça fonctionne sinon j ai vu que dans un tuto de Sébastien il était possible de créer un combo box qui aurait un effet identique et lisible par toutes les versions excel (reste à le créer ).
Avant tout ça je vais remettre mon fichier au propre y a du taf.
A+
Bonsoir,
ça dépend comment tu l'as intégré : Si tu passes seulement par le menu Fichier/Option il n'y aura pas de problème.
par contre on ne pas utiliser .xlsb avec le Custom UI Editor : Si tu as customisé avec c'est mort.
Dans ce cas le plus simple c'est d'enregistrer le tout en .xlsm : 450 ko c'est déjà pas mal !
Tiens moi au jus...
Si tu as le temps essaie aussi de me montrer comment ets montée ta CommandBar... Finalement je me suis rendu compte qu'il était possible de personnaliser le ruban à partir du fichier .xlsm puis de le convertir en .xlsb
A+
Bonsoir
J'ai finalement retranscrit le fichier cela fonctionne à merveille ça n'a rien a voir avec la première version ça torche maintenant.
Pour le comandbar je l ai supprimé malgré qu il fonctionne sous xlsb et je suis passé par une listbox.
cela permettra si problème de version de fonctionner.
Je suis encours de validation avec les personnes qui vont l utiliser. Si ok je cloturerai.
Planning validé cooool un grand merci je m attaque au calcul des heures cumulées (positives ou négatives) ...