Automatiser votre outil de planning avec VBA

Dans un article précédent (dont voici le lien), nous avons vu comment automatiser certains aspects d’un planning en utilisant uniquement des fonctions Excel. Dans cet article, nous allons voir comment rajouter des fonctionnalités pour automatiser votre planning avec VBA.

Le planning ainsi créé permet de générer une visualisation automatique en utilisant les formules Excel et la mise en forme conditionnelle. Dans cet article, nous aborderons comment automatiser encore plus en utilisant du code VBA. Les deux points suivants seront abordés : générer un planning macro et gérer les dépendances entre les tâches.

Le premier point permet de n’avoir à faire qu’un seul planning qui sera la version détaillée. En effet, l’outil viendra créer automatiquement un planning macroscopique qui sera plus lisible du point de vue du projet.

Le second point permettra de gérer les dépendances entre les tâches et ainsi mettre à jour les dates en fonction des autres. Cela vous assurera que toutes vos tâches sont à jour même avec les mises à jour successives.

1. Générer un planning macro

Pour définir les budgets et les jalons d’un projet, on utilise souvent un planning macroscopique. Cependant, de mon point de vue, pour créer ce planning macroscopique, il est bien nécessaire de lister toutes les tâches et actions à réaliser pour calculer le temps et le budget requis sur chaque étape du projet. Dit autrement, il faudra donc réaliser le planning détaillé afin de créer le planning macroscopique. Les deux sont alors liés. L’idée est alors de ne faire que le planning détaillé et de générer automatiquement le planning grossier à partir du premier. Nous passerons par du code VBA pour réaliser cette tâche.

La première étape consiste à organiser notre fichier de planning. Nous reprendrons l’exemple utilisé dans cet article. Pour remplir notre planning, nous aurons alors trois niveaux de découpage :

• Séparation par projet ou phase du projet : permet de définir les grandes étapes
• Liste des tâches à réaliser pour mener à bien le projet ou l’étape
• Liste des actions par tâche

Lecture et stockage des données

Avec ces trois niveaux, vous pourrez déjà aller assez loin dans le découpage de votre projet. L’étape suivante consiste à créer le code VBA qui permettra de lire ce planning et de le réduire automatiquement. Dans l’exemple présenté, nous enlèverons un niveau en ne gardant que les projets et les tâches. Le programme incorporera automatiquement chaque action dans son niveau supérieur respectif.

Avec cet outil, vous pourrez donc aller loin dans l’analyse de votre projet et ainsi obtenir un résultat macroscopique automatiquement sans avoir à tout refaire. Votre outil calculera alors toutes les dates automatiquement ce qui limitera bien entendu le risque d’erreur.

Avant toute chose, nous allons réfléchir et déterminer ce que doit faire le script :

• Boucle sur le tableau des actions pour récupérer et stocker toutes les informations (projet, tache, action, dates, …)
• Pendant cette première boucle, créer une liste qui contient tous les projets différents. Elle nous servira par la suite.

Voici un exemple qui traite les points évoqués. Tout n’est pas présenté mais le coeur du programme est là. Par exemple, certaines variables, commençant par « col » sont initialisées préalablement. Elles servent à récupérer les informations dans la feuille Excel. Pour interagir avec la feuille Excel, la variable « ws_action » est définie pour représenter la feuille via la commande suivante :

Set ws_action = ActiveWorkbook.Sheets("Suivi des actions")

Les premières lignes consistent à initialiser les variables utiles pour la boucle.

Ensuite, la boucle « while » nous sert pour traiter chaque ligne, les unes après les autres. La boucle s’arrête quand elle rencontre une ligne vide. Pour plus d’infos concernant les boucles, voici un article qui traite du sujet.

Enfin, toutes les informations sont stockées dans un tableau qui s’appelle « liste_actions ».

Le bloc restant est utilisé pour créer un tableau qui contient une liste unique des différents projets.

Ecriture des données dans la feuille Excel

A ce stade du programme, les informations ont été stockées dans un tableau et une liste des différents projets est disponible. Le programme va ensuite boucler sur cette liste de projets afin de calculer les dates de début et de fin pour chaque tâche. La dernière étape consiste à écrire le résultat dans une autre feuille Excel.

    ' boucle sur les projets
    ligne = 9 ' initialisation de la ligne pour écrire
    For i = 0 To UBound(liste_projet)
        
        ligne = ligne + 1
        ligne_projet = ligne
        projet = liste_projet(i)
        debut_projet = "01/01/2100"
        fin_projet = "01/01/1900"
        avancement = 0
        charge_total = 0
        
        ' récupère la liste des tâches
        ' (projet, tache, ressource, debut, fin, avancement, charge)
        liste_tache = fonctions.get_tasks_from_project(liste_actions, projet)

        n_tache_proj = UBound(liste_tache, 1)
        n_col = UBound(liste_tache, 2)
                
        'Ecriture de la ligne projet
        For j = 0 To UBound(liste_tache, 1)
            ligne = ligne + 1
            'Ecriture de la tâche dans la liste du planning macro
            ' Projet - Tache - Ressource - Debut - Fin - Avancement - Charge
            For k = 0 To n_col
                ws.Cells(ligne, k + 2) = liste_tache(j, k)
            Next k
        Next j
        
    Next i

Comme évoqué précédemment, le programme utilise la liste des projets pour boucler dessus. Ensuite, à partir du nom du projet, une fonction renvoie la liste des tâches associées. Cette fonction a été créé spécifiquement pour réduire les actions dans les tâches associées. Elle met également à jour les dates de début et de fin.

Avec cette liste de tâche, une boucle « For » parcourt tous les éléments en ligne puis une autre boucle « For » parcourt les colonnes. La variable « ws » est la variable dans laquelle nous avons stocké la feuille Excel où nous allons écrire le résultat.

La première image ci-dessous présente le planning complet avec toutes les tâches et actions en détails. La liste est longue et la visualisation du planning peut vite devenir compliquée à cause du nombre de ligne.

Cette seconde image présente la génération automatique du planning macroscopique. Le planning simplifié se fait par un simple clic sur le bouton bleu. Si vous voulez savoir comment assigner une macro à un bouton sous Excel, voici un exemple dans cet article. Une fois que la moulinette a terminé, la liste est alors bien plus courte et on ne retrouve que les différents projets et les tâches associées.

A droite du tableau, je vous présente une autre manière de présenter votre planning, cette fois-ci la granularité est en semaine. Cela vaut ce que ça vaut mais cela peut vous donner des idées par la suite.

2. Gérer les dépendances entre les tâches

Ce point est particulièrement intéressant, de mon point de vue. En effet, dans un planning, les tâches se succèdent, s’enchaînent, se superposent. Certaines peuvent être faites en parallèles mais d’autres sont dépendantes d’une précédente.

Avec un fichier simple comme nous venons de faire, vous pouvez gérer cela en insérant des formules au lieu de renseigner les dates. Par exemple, la date de début de la tâche 2 sera égale à la date de fin de la tâche 1 + 1 jour. En reprenant les illustrations précédentes, on aurait dans la cellule J11 la formule suivante :

« =L10+1 »

Cependant, en faisant cela, vous risquez de faire des erreurs de liens dans le cas où vous rajouteriez des lignes par exemple. L’idée est de rajouter deux colonnes. La première servira à identifier les tâches par un identifiant unique. La seconde sera pour indiquer si la tâche possède un ou plusieurs prédécesseurs. Si elle n’en a pas, la case reste vide. Si la tâche doit attendre la fin d’autre, on pourra l’indiquer en remplissant son numéro d’identification dans cette colonne. Le fichier Excel détectera automatiquement la tâche en amont et viendra mettre à jour automatiquement la date de début.

La méthode Worksheet_change en VBA

Pour réaliser cette opération, de manière automatique et totalement transparente, nous allons utiliser la méthode « Worksheet_Change ». Ceci se traduit par du code VBA derrière une feuille Excel. A chaque changement dans la feuille, cette méthode sera appelée. L’argument récupérée par cette méthode est la cellule modifiée ou la plage de cellules. A partir de cela, vous pourrez donc travailler sur votre planning à chaque changement, ou pas si la cellule modifiée n’a rien à voir avec le planning.

Pour écrire du code qui sera exécuté derrière une feuille Excel, il faut ouvrir l’interface VBA (ALT+F11). Ensuite il faut réaliser un clic droit sur la feuille et cliquer sur « Code ».

Private Sub Worksheet_Change(ByVal target As Range)
 
    
End Sub

Voici les étapes qu’aura à réaliser le programme :

• Détecter un changement dans la feuille, réalisé automatiquement par la méthode Worksheet_Change
• Vérification que la cellule modifiée ait un impact sur le planning
• si c’est le cas, le tableau est parcouru pour mettre à jour les dates d’entrée en fonction des prédécesseurs

Les critères de contrôle pour savoir si la cellule a un impact sur le planning sont les suivants :

• une seule cellule a été changée
• la seule mise à jour appartient la colonne E (n°5), J (n°10) ou K (n°11)

Si ces critères sont respectés, le programme va alors scanner toutes les actions en vérifiant si un ou plusieurs prédécesseurs ont été renseignés. Si c’est le cas, les dates de fin de ces prédécesseurs vont être récupérées. La date la plus avancée est conservée et on rajoute 1 jour pour démarrer la tâche le jour ouvré suivant. Finalement, la date est inscrite dans la cellule de la tâche en cours de traitement.

Private Sub Worksheet_Change(ByVal target As Range)
 
    Dim ws As Worksheet, ws_db As Worksheet

    '------------------------------------------
    ' Critère pour quitter la procédure
    ' Target contient plusieurs cellules
    If target.Cells.Count > 1 Then Exit Sub
    
    '------------------------------------------
    ' si on met un prédecesseur on check et on met à jour (colonne 5)
    ' si on change une date de début, on remet tout à jour (colonne 10)
    ' si on change une durée d'une tâche, on remet tout à jour (colonne 11)
    If target.Column = 5 Or target.Column = 10 Or target.Column = 11 Then
        ' Desactivation des évènements lors de l'exécution
        Application.EnableEvents = False
        
        ' Stockage de la feuille de calcul dans la variable ws
        Set ws = ActiveWorkbook.ActiveSheet

        ' Lancement de la procédure de mise à jour
        update_predecessor ws
        
        ' Réactivation des évènements sur la feuille
        Application.EnableEvents = True
    End If
    
End Sub

Mise à jour et écriture de la date de début

Cette portion de code précédente permet de contrôler les modifications apportées à la feuille. Le code exécute les étapes décrites précédemment. Voici maintenant une portion de code permettant de boucler sur le tableau des actions pour les mettre à jour suivant leur prédécesseur. On considère que les tâches sont insérées dans l’ordre chronologique.

' Iniitialisation des variables pour la lecture du tableau
line0 = ligne_debut_recherche + 2
i = line0
Do While ws.Cells(i, 1) <> ""
    If ws.Cells(i, col_recup_dep) <> "" Then
        
        ' La fonction get_pred_list renvoie une liste avec les numéros des lignes des prédécesseurs
        ' si une erreur ou si aucun prédécesseur n'est trouvé, la fonction renvoie 0
        line_list = fonctions.get_pred_list(ws, i, col_recup_dep, line0)
        If line_list(0) <> 0 Then
            
            ' Initialisation d'une date antérieure
            date_critique = "01/01/1900"
            
            ' boucle sur tous les prédécesseurs
            For j = 0 To UBound(line_list)
                line = line_list(j)

                'date_fin_pred : date de fin du prédecesseur
                'jour_suivant : prochain jour ouvré
                'mise à jour de la date de début de la tâche
                date_fin_pred = ws.Cells(line, col_recup_fin)
                
                ' on compare la date de fin pour récupérer la date la plus éloignée
                If date_fin_pred > date_critique Then
                    date_critique = date_fin_pred
                End If

            Next j
            
            ' calcul du jour suivant en utilisant la fonction Excel adéquat
            jour_suivant = Application.WorksheetFunction.WorkDay(date_critique, 1, JoursAbsences)
            ' écriture dans la cellule
            ws.Cells(i, col_ecrire_debut) = jour_suivant
        End If
    End If
    
    i = i + 1
Loop

Enfin, j’ai délibérément omis de pas montrer la déclaration des variables car cela aurait alourdi l’article sans grande valeur ajoutée à mon sens.

Conclusion

Au travers de cet article je vous ai montré comment mettre en place une automatisation sur un planning en VBA. Grâce à la méthode « Worksheet_change » vous pouvez récupérer facilement les changements dans la feuille et réaliser une procédure suivant vos besoins. L’exemple présenté concerne un planning mais vous pouvez parfaitement imaginer faire la même chose pour toute autre application.

En rajoutant des fonctionnalités automatiques avec un peu de code VBA, vous passez au niveau supérieur avec votre outil. En effet, les modifications rendent votre fichier plus simple, plus flexible et plus robuste.

Si vous avez des questions, n’hésitez pas à laisser un commentaire, j’y répondrai avec plaisir. Merci de m’avoir lu.
Benjamin