Automate resource allocation processes in Excel with VBA

The code allocates resources to tasks based on their availability and capacity.

Scenario

Let’s assume you have a worksheet containing:

• Resources: employees, machines, etc., with information about their capacity (e.g., work hours per day) and available hours.
• Tasks: each task has a required number of hours that need to be allocated from available resources.

Goal

We want to automate the allocation of resources to tasks based on resource capacity and task requirements.

Excel Sheet Structure

Resources Sheet:

Tasks Sheet:

VBA Code

Sub AllocateResources()
    ' Variables
    Dim wsResources As Worksheet
    Dim wsTasks As Worksheet
    Dim i As Long, j As Long
    Dim resName As String
    Dim resCapacity As Double
    Dim resAvailable As Double
    Dim taskRequiredHours As Double
    Dim totalAllocated As Double
    Dim taskName As String
    Dim remainingHours As Double
    Dim allocation As Double   
    ' References to the sheets
    Set wsResources = ThisWorkbook.Sheets("Resources")
    Set wsTasks = ThisWorkbook.Sheets("Tasks")   
    ' Loop through each task
    For i = 2 To wsTasks.Cells(Rows.Count, 1).End(xlUp).Row       
        ' Get task name and required hours
        taskName = wsTasks.Cells(i, 1).Value
        taskRequiredHours = wsTasks.Cells(i, 2).Value
        remainingHours = taskRequiredHours       
        ' Allocate resources to the task
        For j = 2 To wsResources.Cells(Rows.Count, 1).End(xlUp).Row           
            ' Get resource info
            resName = wsResources.Cells(j, 1).Value
            resCapacity = wsResources.Cells(j, 2).Value
            resAvailable = wsResources.Cells(j, 3).Value           
            ' Check if resource is available
            If resAvailable > 0 Then
                ' Calculate the possible allocation for this resource
                allocation = WorksheetFunction.Min(remainingHours, resAvailable)
                allocation = WorksheetFunction.Min(allocation, resCapacity * resAvailable)               
                ' Update allocation in task sheet
                wsTasks.Cells(i, 3).Value = wsTasks.Cells(i, 3).Value & resName & " (" & allocation & " hours) ; "
                remainingHours = remainingHours - allocation
                wsResources.Cells(j, 3).Value = resAvailable - allocation               
                ' If task hours are fully allocated, move to the next task
                If remainingHours <= 0 Then Exit For
            End If
        Next j
    Next i
    MsgBox "Resource allocation completed!", vbInformation
End Sub

Code Explanation

1. Variable Definitions:
   • wsResources and wsTasks represent the worksheets for resources and tasks, respectively.
   • The other variables are used to keep track of resource and task information during the allocation process.
2. Main Loop for Tasks:
   • The outer loop goes through each task in the « Tasks » sheet, retrieves the required hours, and tracks the remaining hours needed for allocation.
3. Resource Allocation:
   • The inner loop iterates over each resource in the « Resources » sheet. For each resource, it checks if it is available (has remaining hours).
   • The allocation is calculated by considering the remaining required hours for the task and the available capacity of the resource.
   • The allocated resource and the hours assigned are updated in the « Tasks » sheet, and the available hours for that resource are updated.
4. Updating the Sheets:
   • The allocated resources are displayed in the « Assigned Resource » column of the « Tasks » sheet.
   • The available hours for each resource are updated in the « Available Hours » column of the « Resources » sheet.
5. End of Process:
   • After the process completes, a message box pops up indicating that the resource allocation is finished.

Conclusion

This VBA macro automates the process of allocating resources to tasks based on the resource capacities and the task requirements. You can modify the code to accommodate different scenarios, such as adding task priorities, resource types, or additional constraints for more complex allocations.