Automating transportation and logistics planning in Excel using VBA

Automating transportation and logistics planning in Excel using VBA can significantly enhance process efficiency and reduce human errors. Here is a detailed VBA code example that can be used to automate a part of the transport and logistics planning, such as assigning deliveries to drivers or planning routes. This example scenario includes the following steps:

1. Data Entry: An Excel table contains delivery-related information, including departure address, destination, delivery date, cargo weight, etc.
2. Automation of Assignment: The VBA code will analyze the data and assign each delivery to a driver or vehicle, based on criteria (such as vehicle capacity, proximity, etc.).
3. Route Planning: The code can also plan the route based on distance or priority.

Structure of the Data in Excel

Let’s assume you have a table in Excel with the following columns:

• A: Delivery ID
• B: Departure Address
• C: Destination Address
• D: Cargo Weight
• E: Delivery Date
• F: Assigned Driver
• G: Assigned Vehicle
• H: Delivery Status (e.g., « Planned », « In Progress », « Delivered »)

Example of VBA Code

Sub TransportLogisticsPlanning()
    ' Declare variables
    Dim ws As Worksheet
    Dim row As Long
    Dim departureAddress As String
    Dim destinationAddress As String
    Dim weight As Double
    Dim deliveryDate As Date
    Dim availableDriver As String
    Dim availableVehicle As String
    Dim distance As Double
    Dim maxWeight As Double
    Dim vehicleCapacity As Double   
    ' Set reference to the planning table (Excel sheet)
    Set ws = ThisWorkbook.Sheets("Deliveries") ' Replace "Deliveries" with the name of your sheet   
    ' Initialize weight limits (e.g., max weight of a vehicle)
    vehicleCapacity = 1000 ' Example capacity in kg   
    ' Loop through all rows in the table (starting from row 2)
    row = 2
    Do While ws.Cells(row, 1).Value <> ""       
        ' Retrieve delivery data
        departureAddress = ws.Cells(row, 2).Value
        destinationAddress = ws.Cells(row, 3).Value
        weight = ws.Cells(row, 4).Value
        deliveryDate = ws.Cells(row, 5).Value       
        ' Check if the cargo weight is within the vehicle's capacity
        If weight <= vehicleCapacity Then           
            ' If the weight is within the limit, search for an available driver and vehicle
            availableDriver = FindAvailableDriver(deliveryDate)
            availableVehicle = FindAvailableVehicle(deliveryDate)           
            ' If a driver and vehicle are available, assign them to the delivery
            If availableDriver <> "" And availableVehicle <> "" Then
                ws.Cells(row, 6).Value = availableDriver ' Assign the driver
                ws.Cells(row, 7).Value = availableVehicle ' Assign the vehicle
                ws.Cells(row, 8).Value = "Planned" ' Set status to "Planned"
            Else
                ws.Cells(row, 8).Value = "No resources available"
            End If           
        Else
            ' If the weight exceeds the vehicle's capacity, display a message
            ws.Cells(row, 8).Value = "Weight too high"
        End If       
        ' Move to the next row
        row = row + 1
    Loop
End Sub

Function FindAvailableDriver(deliveryDate As Date) As String
    ' Example function to find an available driver for the given delivery date
    Dim driver As String
    driver = ""   
    ' Simplified: the example returns a fictional driver if available
    ' Add logic here to search in a table of available drivers
    If deliveryDate >= Date Then
        driver = "Driver 1" ' Replace with dynamic search
    End If  
    FindAvailableDriver = driver
End Function

Function FindAvailableVehicle(deliveryDate As Date) As String
    ' Example function to find an available vehicle for the given delivery date
    Dim vehicle As String
    vehicle = ""  
    ' Simplified: the example returns a fictional vehicle if available
    ' Add logic here to search in a table of available vehicles
    If deliveryDate >= Date Then
        vehicle = "Vehicle A" ' Replace with dynamic search
    End If
    FindAvailableVehicle = vehicle
End Function

Code Explanation

1. Variables and Data Structure:
   • The code starts by declaring variables for the worksheet (ws), the delivery data for each row (departure address, destination, weight, etc.), and auxiliary functions to determine available resources (driver and vehicle).
2. Loop Through Data Rows:
   • The code loops through each row in the planning table, starting at row 2 (assuming row 1 contains headers).
   • For each delivery, it checks if the cargo weight is within the vehicle’s capacity (vehicleCapacity). If yes, it proceeds to check for available drivers and vehicles for the specified delivery date.
3. Auxiliary Functions:
   • The functions FindAvailableDriver and FindAvailableVehicle are simplified examples that return an available driver and vehicle. In a real application, these functions could perform searches in other sheets or tables that contain the schedules and availability of drivers and vehicles.
4. Assigning Resources:
   • If a driver and vehicle are available for the specified delivery date, the code assigns these resources to the delivery by updating the corresponding columns in the Excel table (driver and vehicle assigned).
   • The delivery status is updated to « Planned » if the assignment is successful, or to « No resources available » or « Weight too high » if the planning fails.
5. Delivery Status:
   • The status of the delivery is updated, either to « Planned » if successfully assigned, or to « No resources available » or « Weight too high » if there are issues with assigning resources.

Customization and Extension

• Dynamic Resource Search: You can replace the FindAvailableDriver and FindAvailableVehicle functions with dynamic searches in availability tables that contain actual schedules of drivers and vehicles.
• Distance Calculation: You can integrate APIs like Google Maps to calculate the distance between departure and destination addresses and optimize routes.
• Priority Management: You can add logic to assign priorities to urgent deliveries and allocate resources accordingly.

Conclusion

This VBA code provides a foundation for automating the planning of deliveries and logistics resources in Excel. It can be customized based on your specific needs, such as integrating advanced features for route optimization or managing a larger number of resources.