Créer un planning d'équipe

Excel ne sert pas qu’à faire des calculs ! Dans l’exemple que nous présentons ici, il va tracer, sur une frise chronologique, le diagramme des congés pris par les membres d’une équipe au cours de l’été 2010. Ce travail est effectué par une macro, dont nous détaillerons le fonctionnement. Bien entendu, nous n’obtiendrons pas le même résultat qu’avec un logiciel de planning ou Outlook. L’exercice est volontairement limité. Notre but est juste de montrer quelques-unes des possibilités du langage VBA. Nous nous adressons ici à des personnes connaissant parfaitement Excel.

Le résultat à obtenir

Le classeur que vous allez télécharger contient deux feuilles : dans la feuille Données, vous spécifiez les dates de congés de chaque membre de l’équipe. La macro Trace_Dessin, accessible via un bouton (un simple rectangle auquel on a affecté la macro via un clic droit) dessine le diagramme dans la feuille Dessin. Pour comprendre notre exemple, puis modifier la macro afin de l’adapter à vos besoins personnels, téléchargez notre classeur Excel (au format XSLM) sur notre site, à l’adresse http://t.01net.com/tc105633. Attention : Excel 2007 / 2010 doit accepter l’exécution des macros. Pour cela, activez le menu Développeur, cliquez sur Sécurité des macros, cochez Activer toutes les macros et validez.

Le calcul des jours ouvrés

Pour déterminer le nombre de jours ouvrés pris par chaque personne, nous n’utilisons pas encore la macro. Dans la feuille Données, figure en E5 la formule =NB.JOURS.OUVRES(B5;C5;Feries) qui donne le nombre de jours ouvrés (samedis, dimanches et fêtes exceptés) pris par chaque personne. Ici, Feries est le nom de la plage (B11:B12 dans notre exemple) où figurent les jours fériés de l’été. Si la fonction NB.JOURS.OUVRES n’apparaît pas dans Excel, cliquez sur le bouton Office (Excel 2007) ou Fichier (Excel 2010), puis Options Excel. Activez l’onglet Compléments, cliquez sur Atteindre, cochez Analysis Toolpak et validez toutes les fenêtres.

La frise chronologique

Dans la feuille Dessin, les lignes 2 et 3 représentent la frise chronologique. En ligne 3, nous avons placé toutes les dates successives (un jour par colonne), puis opté pour le format personnalisé “ jj ” pour n’afficher que le quantième du mois. En ligne 2, nous n’affichons que les deux premières lettres du jour de la semaine (Lu, Ma…). Les formats de dates n’offrent pas cette possibilité. Nous écrivons donc en B2 la formule =CHOISIR(JOURSEM (B3);”Di”;”Lu”;”Ma”;”Me”;”Je”;”Ve”;”Sa”) et la recopions sur toute la ligne 2.

Le fonctionnement de la macro

La macro Trace_Dessin commence par lire les données dans la feuille Données. Remarquez l’instruction Debut_Plage = [C1]. En plaçant une adresse de cellule entre crochets, vous pouvez en lire directement le contenu, comme vous le feriez avec Debut_Plage = Range(“ C1 ”). Value. Mais c’est plus court ! Il y a ensuite deux boucles imbriquées. La première (For Personne = 1 to Nb_Personnes) concerne les quatre membres de l’équipe. La seconde, for Jour = Debut to Fin, “ balaye ”, pour chaque individu, tous les jours de la période où il est en congé.

L’utilisation d’une fonction

Pour chaque jour balayé, nous devons déterminer s’il s’agit d’un samedi ou d’un dimanche, car les jours de week-end sont colorés en vert sombre sur le planning. Pour cela, nous allons exploiter la fonction Joursem. Mais pour utiliser une fonction Excel dans une macro, il faut faire précéder la fonction de la clause Application. WorksheetFunction et utiliser le nom anglais de la fonction (WeekDay au lieu de Joursem). Dans notre exemple, nous obtenons le numéro du jour de semaine (1 pour dimanche, 2 pour lundi, etc.) avec l’instruction JourSem = Application. WorksheetFunction. Weekday(Jour).

L’adressage relatif

Pour écrire une donnée dans une cellule ou en modifier la couleur de fond, comme nous l’avons fait avec la propriété Interior.ColorIndex, inutile de sélectionner cette cellule avec l’instruction Select : vous ralentiriez votre macro. Utilisez plutôt l’adressage relatif. Choisissez une cellule d’origine, sélectionnez-la, puis utilisez la commande ActiveCell.Offset(X, Y). La cellule adressée ici est celle située à X lignes et Y colonnes de la cellule d’origine.