Automatiser les processus d’allocation des ressources, Excel VBA

Automatiser les processus d’allocation des ressources, Excel VBA
Automatiser les processus d’allocation des ressources dans Excel à l’aide de VBA implique
plusieurs étapes, de la saisie des données jusqu’à l’optimisation de l’allocation en fonction de
critères spécifiques. Ce processus peut inclure des éléments comme la répartition des tâches,
l’affectation de ressources à des projets en fonction de leur disponibilité, etc.
Je vais vous fournir un exemple détaillé de code VBA pour un scénario où l’on alloue des
ressources à des tâches en fonction de leur capacité, tout en respectant certaines contraintes
(par exemple, le nombre de ressources disponibles par tâche).
Scénario
Supposons que vous avez une feuille de calcul contenant :
– Des ressources disponibles (par exemple, employés ou machines) avec des
informations sur leur capacité et leur nombre d’heures disponibles.
– Des tâches à accomplir, chacune ayant un besoin spécifique en ressources.
Objectif
Nous voulons automatiser l’allocation des ressources aux tâches en fonction de la capacité et
des besoins.
Étapes à suivre
1. Saisie des données dans une feuille Excel.
2. Création de la macro VBA pour automatiser l’allocation des ressources.
3. Affichage des résultats dans une nouvelle feuille.
Structure de la feuille Excel
Ressource   Capacité par Heure  Heures Disponibles  Total Capacité
Employé A     5                               20                             100
Employé B     3                               30                             90
Tâche     Heures Requises Ressource Assignée
Tâche 1   50
Tâche 2   40
Code VBA

Sub AllocationRessources()
' Variables
Dim wsRessources As Worksheet
Dim wsTaches As Worksheet
Dim i As Long, j As Long
Dim resNom As String
Dim resCapacite As Double
Dim resDisponibles As Double
Dim tacheHeures As Double
Dim totalAlloue As Double
Dim tacheNom As String
Dim heuresRestantes As Double
Dim allocation As Double
' Références aux feuilles de calcul
Set wsRessources = ThisWorkbook.Sheets("Ressources")
Set wsTaches = ThisWorkbook.Sheets("Tâches")
' Boucle pour traiter chaque tâche
For i = 2 To wsTaches.Cells(Rows.Count, 1).End(xlUp).Row
' Nom de la tâche et heures requises
tacheNom = wsTaches.Cells(i, 1).Value
tacheHeures = wsTaches.Cells(i, 2).Value
heuresRestantes = tacheHeures
' Allocation des ressources à la tâche
For j = 2 To wsRessources.Cells(Rows.Count, 1).End(xlUp).Row
' Récupération des informations sur la ressource
resNom = wsRessources.Cells(j, 1).Value
resCapacite = wsRessources.Cells(j, 2).Value
resDisponibles = wsRessources.Cells(j, 3).Value
' Vérification de la capacité de la ressource
If resDisponibles > 0 Then
' Calcul de l'allocation possible pour cette ressource
allocation = WorksheetFunction.Min(heuresRestantes,
resDisponibles)
allocation = WorksheetFunction.Min(allocation, resCapacite
* resDisponibles)
' Mise à jour de l'allocation
wsTaches.Cells(i, 3).Value = wsTaches.Cells(i, 3).Value &
resNom & " (" & allocation & " heures) ; "
heuresRestantes = heuresRestantes - allocation
wsRessources.Cells(j, 3).Value = resDisponibles -
allocation
' Si toutes les heures de la tâche sont allouées, on passe
à la tâche suivante
If heuresRestantes <= 0 Then Exit For
End If
Next j
Next i
MsgBox "Allocation des ressources terminée!", vbInformation
End Sub

Explication du code
1. Définition des variables :
– wsRessources et wsTaches sont utilisés pour référencer les feuilles de calcul
contenant les ressources et les tâches, respectivement.
– Les autres variables sont utilisées pour suivre les informations sur les
ressources et les tâches pendant le processus d’allocation.
2. Boucle principale pour les tâches :
– On boucle sur chaque tâche dans la feuille des tâches, en récupérant le nombre
d’heures requises.
– La variable heuresRestantes suit les heures non encore allouées à la tâche.
3. Allocation des ressources à la tâche :
– On boucle sur les ressources disponibles. Pour chaque ressource, on calcule
l’allocation possible en fonction des heures restantes pour la tâche et de la
capacité de la ressource.
– Si la ressource a encore des heures disponibles, elle est affectée à la tâche avec
un nombre d’heures limité par sa capacité et la demande de la tâche.
– Les heures disponibles de la ressource et les heures restantes de la tâche sont
mises à jour après chaque allocation.
4. Mise à jour dans la feuille :
– Les ressources allouées sont affichées dans la colonne « Ressource Assignée »
de la feuille « Tâches ».
– Les heures restantes des ressources sont mises à jour dans la colonne « Heures
Disponibles » de la feuille « Ressources ».
5. Fin de l’exécution :
– Un message apparaît pour indiquer la fin du processus d’allocation.
Conclusion
Cette macro VBA permet d’automatiser l’allocation des ressources en fonction des besoins des
tâches et de la disponibilité des ressources. Vous pouvez adapter ce code à différents
scénarios (par exemple, avec des contraintes supplémentaires comme des priorités de tâches
ou des types spécifiques de ressources).