Trucking Logistics

msofficeshare logo

Requirement:  Visual Basic Knowledge

File to download:  Trucking Logistics.xlsm

Related article:  Find the Coordinates of a Shape Object

Trucking logistics is never an easy task for manufacturers and retailers.  The difficulty level grows exponentially when a company adds a distribution center or a new customer.  Things can be easy to get out of hand when a company tries to plot the best truck route to save money and reduce delivery time.  What can be worse is that most companies have to plan for trucking deliveries every day.  Big companies like Walmart and Amazon can afford to pay software companies to tailor make a logistics software for their needs.  Small to median size companies will have difficulties to afford such luxury.  Many of such companies use paper and pen to do trucking logistics on a frequent basis.  This article and the attached file may be helpful to reduce some of the workload.

 

The below logistics will be limited to one distribution center but with multiple customer locations.  If your company has multiple regional distribution centers or warehouses, you can consider using multiple copies of this file, for example, one for east region and another for west region.  

 

When I designed the program, there were several concerns that I had:

(1)  A truck may be used to delivering products to several customers at one trip.  For example, Company A may send out one truck from Houston to multiple customers at San Antonio, Phoenix, Los Angeles, San Francisco, and then Seattle.  This route makes perfect sense because they are on the same path.  However, there will be limited capacity in one truck.  Let's say a truck can only take 100 boxes of products.  If total orders from all customers are 180 boxes, at least two trucks must be sent to deliver all the products.   If the first truck has already delivered all the orders for customers in San Antonio, Phoenix, and Los Angeles.  The second truck is only needed to deliver products to San Francisco and Seattle.  There is no need to stop by San Antonio, Phoenix, and Los Angeles again.

 

(2)  Secondly, there might be limitations of time or number of stops for the delivery of one particular truck.  A truck may only have 8 hours delivery time frame and can only delivery to 5 locations in one day, especially for local deliveries.  The time that a truck might be stuck in a traffic jam and the loading time must be taken into considerations as well.  If a company's products are perishable or with other time constraints, that may also limit the number of stops that a truck can make.

 

(3)  On the other hand, if I have a customer in New York, it may not make any sense to deliver to New York after the delivery to customers in Seattle.  It should be more cost and time effective if I send separate trucks to the customers in east coast and west coast.  Therefore, there should be a maximum distance that warrants a separate truck to be used.

(4)  The orders from each customer vary every time.  A customer may order 50 boxes today, 22 boxes tomorrow, 97 boxes a day after tomorrow, and 0 box the second day after tomorrow.  Therefore, the program must allow such delivery flexibility.

 

The logic behind of the truck logistics program is to have the Visual Basic module to find the shortest distance after one location is delivered.  The example in the "Trucking Logistics.xlsm" file (click the file to download) illustrates how it works.  The example shows that my company, MSOfficeShare (abbreviated as MSOS), has created a very popular product that 15 big Fortunate 500 companies are buying on a regular basis.  MSOS will deliver the product from its warehouse in Houston to their headquarters accordingly.

First of all, the personnel of Operations Department needs to put in today's orders from the customers.  The number of units can be boxes, pallets, bags, and whatever is appropriate.  The number of units can be zero if the related customers did not place an order for that particular day.  If the number of units is zero for some particular customers, the program will skip the deliveries for those customers.

pic1 truck logistics
pic2 truck logistics

Sub ArrangeTruck()
    Call ErasePrevResult
    Call CalcRoute
End Sub
Sub ErasePrevResult()
    Range("E2:Z100").Select
    Selection.ClearContents
    
    For Each MyShape In ActiveSheet.Shapes
        If Left(MyShape.Name, 6) <> "Button" Then MyShape.Delete
    Next MyShape
    
    Sheets("Route").Select
    For Each MyShape In ActiveSheet.Shapes
        MyShape.Delete
    Next MyShape
    
    Sheets("Map").Select
    Range("A1:R40").Select
    Selection.Copy
    Sheets("Route").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    
    Sheets("Calc").Select
End Sub

Sub CalcRoute()
    Application.ScreenUpdating = False
    Dim DestCount As Integer, n As Integer, MaxLoad As Integer, MaxStop As Integer, TotalPallet As Integer, MaxMile As Single
    Dim RemainLoad As Integer, DeliverLoad As Integer
    Dim TruckRoute As Integer
    Dim StLouisGrp As Integer, PhoenixGrp As Integer, DallasFWGrp As Integer
    Dim PrevPosition As String, RouteNum As String
    Dim BeginShapeName As String
    Dim WH_XCoord As Double, WH_YCoord As Double, XCoordBeg As Double, YCoordBeg As Double
    
    DestCount = Application.WorksheetFunction.CountIf(Range("OrderRange"), ">= 0")
    'Debug.Print "DestCount", DestCount
    
    ReDim DestLoad(DestCount), DestName(DestCount)
    
    MaxLoad = Range("MaxLoad").Value
    MaxStop = Range("MaxStop").Value
    MaxMile = Range("MaxMile").Value
    
    BeginShapeName = "Donut 0"     'initialize variable
    
    Sheets("Calc").Select
    WH_Abbrev = Range("WH_Abbrev").Value
    WH_XCoord = Range("WH_XCoord").Value
    WH_YCoord = Range("WH_YCoord").Value
    XCoordBeg = WH_XCoord
    YCoordBeg = WH_YCoord
    
    'Read data into array
    Sheets("Calc").Select
    Range("BegDestination").Select
    For n = 1 To DestCount
        DestLoad(n) = ActiveCell.Value
        DestName(n) = ActiveCell.Offset(0, -1).Value
        ActiveCell.Offset(1, 0).Select
        
        'Debug.Print n, "DestName", DestName(n), "DestLoad", DestLoad(n)
    Next n
    
    TotalPallet = 0
    
        
    'First Round Allocation.  Any location with ordered Units > Max Units will assign a truck
    TruckRoute = 1
    
    Range("BegResult").Select
    For n = 1 To DestCount
        If DestLoad(n) > MaxLoad Then
            Call TruckRouteNum(TruckRoute, RouteNum)
            ActiveCell.Value = RouteNum & "Truck Route (total " & MaxLoad & " Units): " & WH_Abbrev & " -> " & DestName(n) & " (" & MaxLoad & " Units)"
            Call PlotWHLocation(BeginShapeName, WH_XCoord, WH_YCoord)
            'Debug.Print "BeginShapeName", BeginShapeName
            Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
            Sheets("Calc").Select
            TruckRoute = TruckRoute + 1
            DestLoad(n) = DestLoad(n) - MaxLoad
            ActiveCell.Offset(1, 0).Select
            
            TotalPallet = TotalPallet + MaxLoad
        End If
        
        'Debug.Print n, "DestName", DestName(n), "DestLoad", DestLoad(n)
    Next n
    NextResult = ActiveCell.Address
    
    
    'Second Round Allocation.  Plot remaining truck route by using the shortest distance, starting from warehouse
    ReDim LocationName(DestCount), LocationMileage(DestCount), LocationLoad(DestCount)
    Dim ShortestDist As Long, ShortestLocation As String, ShortestLoad As Integer
    Dim FirstLocation As String, SecondLocation As String, ThirdLocation As String, FourthLocation As String, FifthLocation As String
    Dim SixthLocation As String, SeventhLocation As String, EighthLocation As String, NinethLocation As String, TenthLocation As String
    Dim FirstLoad As Integer, SecondLoad As Integer, ThirdLoad As Integer, FourthLoad As Integer, FifthLoad As Integer
    Dim SixthLoad As Integer, SeventhLoad As Integer, EighthLoad As Integer, NinethLoad As Integer, TenthLoad As Integer
    Dim SecondPhrase As String, ThirdPhrase As String, ForthPhrase As String, FifthPhrase As String
    Dim SixthPhrase As String, SeventhPhrase As String, EighthPhrase As String, NinethPhrase As String, TenthPhrase As String
    Dim NumStop As Integer
    Dim TotalLoad As Long
    
    
    Do
        TotalLoad = 0
        
        Sheets("Mileage").Select
        Range("FirstLocation").Offset(0, 9).Select
        For n = 1 To DestCount
            ActiveCell.Value = DestLoad(n)
            ActiveCell.Offset(1, 0).Select
            TotalLoad = TotalLoad + 1
        Next n
    
        Range("Location").Activate
        ActiveCell.Value = WH_Abbrev    'Always start with the warehouse
        BeginLocation = WH_Abbrev
        
        NumStop = 1
        TruckLoad = 0
        NextTruckLoad = 0
        SecondPhrase = ""
        ThirdPhrase = ""
        FourthPhrase = ""
        FifthPhrase = ""
        SixthPhrase = ""
        SeventhPhrase = ""
        
        Do
            Range("FirstLocation").Offset(0, 1).Select
            n = 1
            ShortestDist = 9999
            For n = 1 To DestCount
                LocationName(n) = ActiveCell.Value
                LocationMileage(n) = ActiveCell.Offset(0, 7).Value
                LocationLoad(n) = ActiveCell.Offset(0, 8).Value
                
                If (LocationMileage(n) > 0) And (LocationMileage(n) < ShortestDist) And (LocationLoad(n) > 0) Then
                    ShortestDist = LocationMileage(n)
                    ShortestLocation = LocationName(n)
                    ShortestLoad = LocationLoad(n)
                End If
                
                ActiveCell.Offset(1, 0).Select
                
                'Debug.Print n, "LocationName", LocationName(n), "LocationMileage", LocationMileage(n)
            Next n
            'Debug.Print ShortestLocation, ShortestDist, NumStop
            
            If (NumStop > 1) And (ShortestDist > MaxMile) Then    'No limit of the distance for the first stop, but limit to distance for subsequent stops)
                Exit Do
            End If
            
            If TruckLoad + ShortestLoad > MaxLoad Then
                Exit Do
            End If
            
            For n = 1 To DestCount
                
                If (DestName(n) = ShortestLocation) And (DestLoad(n) > 0) Then
                    If NumStop = 1 Then
                        FirstLocation = DestName(n)
                        FirstLoad = DestLoad(n)
                        Call PlotWHLocation(BeginShapeName, WH_XCoord, WH_YCoord)
                        'Debug.Print "BeginShapeName", BeginShapeName, "FirstLocation", FirstLocation
                        Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                    Else
                        If NumStop = 2 Then
                            SecondLocation = DestName(n)
                            SecondLoad = DestLoad(n)
                            SecondPhrase = " -> " & SecondLocation & " (" & SecondLoad & " Units)"
                            Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                        Else
                            If NumStop = 3 Then
                                ThirdLocation = DestName(n)
                                ThirdLoad = DestLoad(n)
                                ThirdPhrase = " -> " & ThirdLocation & " (" & ThirdLoad & " Units)"
                                Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                            Else
                                If NumStop = 4 Then
                                    FourthLocation = DestName(n)
                                    FourthLoad = DestLoad(n)
                                    FourthPhrase = " -> " & FourthLocation & " (" & FourthLoad & " Units)"
                                    Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                Else
                                    If NumStop = 5 Then
                                        FifthLocation = DestName(n)
                                        FifthLoad = DestLoad(n)
                                        FifthPhrase = " -> " & FifthLocation & " (" & FifthLoad & " Units)"
                                        Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                    Else
                                        If NumStop = 6 Then
                                            SixthLocation = DestName(n)
                                            SixthLoad = DestLoad(n)
                                            SixthPhrase = " -> " & SixthLocation & " (" & SixthLoad & " Units)"
                                            Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                        Else
                                            If NumStop = 7 Then
                                                SeventhLocation = DestName(n)
                                                SeventhLoad = DestLoad(n)
                                                SeventhPhrase = " -> " & SeventhLocation & " (" & SeventhLoad & " Units)"
                                                Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                            Else
                                                If NumStop = 8 Then
                                                    EighthLocation = DestName(n)
                                                    EighthLoad = DestLoad(n)
                                                    EighthPhrase = " -> " & EighthLocation & " (" & EighthLoad & " Units)"
                                                    Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                                Else
                                                    If NumStop = 9 Then
                                                        NinethLocation = DestName(n)
                                                        NinethLoad = DestLoad(n)
                                                        NinethPhrase = " -> " & NinethLocation & " (" & NinethLoad & " Units)"
                                                        Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                                    Else
                                                        TenthLocation = DestName(n)
                                                        TenthLoad = DestLoad(n)
                                                        TenthPhrase = " -> " & TenthLocation & " (" & TenthLoad & " Units)"
                                                        Call PlotRoute(TruckRoute, BeginShapeName, DestName(n), WH_XCoord, WH_YCoord, XCoordBeg, YCoordBeg)
                                                    End If
                                                 End If
                                              End If
                                           End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                        
                        TruckLoad = TruckLoad + DestLoad(n)
                        DestLoad(n) = 0
                        NumStop = NumStop + 1
                        BeginLocation = DestName(n)
                    End If
                
                    Sheets("Calc").Select
            Next n
            
            'Debug.Print "TruckLoad", TruckLoad
            
            Sheets("Mileage").Select
            Range("FirstLocation").Offset(0, 9).Select
            For n = 1 To DestCount
                ActiveCell.Value = DestLoad(n)
                ActiveCell.Offset(1, 0).Select
            Next n
            
            Range("Location").Activate
            ActiveCell.Value = ShortestLocation
    
        Loop Until (TruckLoad > MaxLoad) Or (NumStop = MaxStop + 1)
        
        Call TruckRouteNum(TruckRoute, RouteNum)
        
        Sheets("Route").Select
        Call InsertText(TruckRoute, RouteNum, XCoordBeg, YCoordBeg)
        
        Sheets("Calc").Select
        Range(NextResult).Activate
        
        ActiveCell.Value = RouteNum & "Truck Route (total " & TruckLoad & " Units): " & WH_Abbrev & "-> " & FirstLocation & " (" & FirstLoad & " Units)" & SecondPhrase & ThirdPhrase & FourthPhrase & FifthPhrase & SixthPhrase & SeventhPhrase & EighthPhrase & NinethPhrase & TenthPhrase
        Call FontColor(TruckRoute)
        TotalPallet = TotalPallet + TruckLoad
        
        TruckRoute = TruckRoute + 1
        ActiveCell.Offset(1, 0).Select
        NextResult = ActiveCell.Address
        
        TotalLoad = 0
        For n = 1 To DestCount
            TotalLoad = TotalLoad + DestLoad(n)
        Next n
        
        BeginLocation = WH_Abbrev
        
    Loop Until TotalLoad = 0
    
    Range(NextResult).Offset(1, 0).Select
    ActiveCell.Value = TotalPallet & " Units will be shipped in " & (TruckRoute - 1) & " trucks."
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    
    
    
    Sheets("Route").Select
    Range("A1:R40").Select
    Selection.Copy
    Sheets("Calc").Select
    ActiveCell.Offset(3, 0).Select
    ActiveSheet.Paste
    Range("A1").Select
    
    Application.ScreenUpdating = True
End Sub
Sub TruckRouteNum(TruckRoute, RouteNum)
    
    Select Case TruckRoute
        Case 1
            RouteNum = "1st "
        Case 2
            RouteNum = "2nd "
        Case 3
            RouteNum = "3rd "
        Case 4
            RouteNum = "4th "
        Case 5
            RouteNum = "5th "
        Case 6
            RouteNum = "6th "
        Case 7
            RouteNum = "7th "
        Case 8
            RouteNum = "8th "
        Case 9
            RouteNum = "9th "
        Case 10
            RouteNum = "10th "
        Case 11
            RouteNum = "11th "
        Case 12
            RouteNum = "12th "
        Case 13
            RouteNum = "13th "
        Case 14
            RouteNum = "14th "
        Case 15
            RouteNum = "15th "
        Case 16
            RouteNum = "16th "
        Case 17
            RouteNum = "17th "
        Case 18
            RouteNum = "18th "
        Case 19
            RouteNum = "19th "
        Case 20
            RouteNum = "20th "
    End Select
End Sub

Secondly, the users need to determine the static parameters.  These parameters should not be changed frequently once put in.  For example, a company will keep the same truck for several years, so the "Max # of Units in a truck" will stay the same for several years until the company replaces such truck with one that as higher capacity or the company redesigns its packaging.  If a customer orders more than the maximum capacity of a truck load, the program will determine that at least two trucks will be sent out.

The "Max # of Truck Stop" is a constraint that relates to the second concern that listed above.  In the above example, a truck can only deliver up to 8 stops.  Once exceeds that, a second truck will instead be sent.  My program is limited to a maximum of 10 stops per truck.

 

The "Max miles for Subsequent Stop" is another constraint related to the third concern listed above.  For example, seperate trucks might be more cost effective (and probably also time effective) to deliver to customers in east coast and west coast.  Computer can not tell if a customer is at east coast or west coast, unless you insert a data column to define it by regions, directions, or geographical locations.  A better way is to impose a limit of distance how far a truck will go from one location to another.  For example, if the current stop is Seattle, the truck will not go to New York City for the next stop because this will exceed the imposed limitation ("Max miles for Subsequent Stop") of 650 miles.  Instead, the program will automatically assign a second truck to deliver to New York City.

 

Please bear in mind that these static parameters can also be used for scenario analysis.  For example, you can vary the "Max # of Truck Stop" from 5 to 7 to see how the truck route may look like.  When customer locations are close enough, you may want to have the same truck to make more stops.  If your truck is more than 5 years old and you consider to replace it with a bigger truck, you can see how much saving you may get by changing the capacity parameter ("Max # of Units in a truck").  Please remember this program only suggests the truck routes based on certain logistics.  A human may have better knowledge to know the best way to achieve time and cost effectiveness of the deliveries.

 

The foundation of this Visual Basic module is to find the shortest distance from one location to another.  The logic works like this:

(1)  The program will first scan the customer orders and see if one or more particular customers have order that exceeds the capacity of a truck load.  If that happens, a truck will first be assigned to such order.  The remaining order will be assigned later.  For example, if Tesla orders 150 units of my products and a truck load capacity is 100 only.  A truck will purely dedicated to the first 100 units to Tesla.  The remaining 50 units will be later assigned to another truck,  probably with products to be delivered to customers other than Tesla.

 

(2)  The program will go through a looping mechanism to assign customer orders one by one based on the shortest distance.  For example, the program will first check the closest customer from Houston warehouse.  The program calculates that Exxon Mobile is the closest customers so the number of ordering will be assigned to Truck #1.  If Exxon orders less than a truck load capacity, the program will look for a customer that has the shortest distance from Exxon Mobile.  Then the program finds Walmart.  The process will continue until either 1 of the 3 events occurs (whichever comes first):

    (a)  The truck has reached its capacity.

    (b)  The distance of the next closest customer is larger than the constraints of the "Max miles for Subsequent Stop".

    (c)  All customers' orders are fulfilled.

truck logistics logo

The calculation of the shortest distance will be based on the latitude and longitude of the customer locations.  My program requires the users to put in such data in the "Mileage" worksheet in the Excel file.  The latitude and longitude of a location can be found in the website, http://www.latlong.net/.  

pic3 truck logistics

The distance between two locations is calculated based on a rather complicated formula which involves certain mathematical functions such as SIN, COSIN, ACOSIN, and RADIAN.  The reason of such complication is that the earth is not flat.  It is a globe.  The distance between 2 points on the map is actually a curve, not a straight line.  The formula I used is based on this website (http://bluemm.blogspot.com/2007/01/excel-formula-to-calculate-distance.html).  This calculated distance is more like a flight distance rather than a road distance.  Therefore, the distance calculated is really an approximation.  This is one important limitation of this program.

 

The "Mileage" worksheet interfaces with the VB module.  When the truck makes a delivery stop, the current location will be updated in the "Mileage" worksheet (cell B3).  The worksheet will re-calculate the distance related to the current location and update column "I".  The remained # of units will also be updated to column "J" after each delivery.  The program will use looping to search for the next closest location for the truck route.  Such process will continue (through looping) until all the units are assigned to truck(s).  The calculated result will show up as in the below illustration.

pic4 truck logistics

However, the above statements are not just "boring", they are missing an important part of what this program intends to be:  to help the user make the right decision.  Every program or automation has limitations that sometimes warrants the interference from a human being.  By looking at the above statement, most people will have difficulties to tell if the computer has calculated the most optimal solution for the delivery routes.  A far better way is to give a visual aid to help the users.  A map with plotting routes will be a perfect complement.

pic5 truck logistics

To make the map more user-friendly, I put in different colors for different truck routes to emphasize the difference.  The colors will match exactly with the ones in the result statements.  I also considered that some users might be color-blinded and some colors may be too similar to tell the difference.  Therefore, I also inserted the wording of the truck route (1st Truck, 2nd Truck, 3rd Truck, ... etc.) on top of the very last delivery stop to pinpoint exactly what route it is to avoid any doubt and confusion.

 

The map plotting is actually the fun and challenging part.  It turned out that this is the most time consuming part, but it is also the most rewarding part of creating this program.

 

Below is the main VB modules of the program.  The CalcRoute module performs looping.  Each looping writes the result to the "Mileage" worksheet by updating the Location and # of units.  The result feeds back to the CalcRoute module and performs the subsequent looping until one of the three constraints is hit.  In addition, every time it loops, it calls on a subroutine  "PlotWHLocation" or "PlotRoute".   "PlotWHLocation" is only for the first trip: it always starts from our warehouse locatiion.  After that, the "PlotRoute" module takes up the subsequent routes.

"PlotWHLocation" module only does one thing: put a shape to the starting point.  In this case, the shape is a donut and the beginning location is Houston.   "PlotRoute" module will do more.  After the subsequent delivery location is found by the main module ("CalcRoute"), it will put a new donut on the map for such subsequent delivery location.  Then the module will plot an arrow from the beginning location to the ending location.  

There are 8 connecting points on a donut shape.  To make the lines plotted look nicer, I put in the below VB codes.  Please note that I only put in 4 conditions.  If you are a perfectionist, you may want to create up to 8 conditions to make the arrow plotting look evern nicer.

pic6 truck logistics


    If (XCoordBeg < XCoordEnd) And (YCoordBeg < YCoordEnd) Then
        SpikeBeg = 7
        SpikeEnd = 3
    End If
    
    If (XCoordBeg > XCoordEnd) And (YCoordBeg > YCoordEnd) Then
        SpikeBeg = 3
        SpikeEnd = 7
    End If
    
    If (XCoordBeg < XCoordEnd) And (YCoordBeg > YCoordEnd) Then
        SpikeBeg = 7
        SpikeEnd = 3
    End If
    
    If (XCoordBeg > XCoordEnd) And (YCoordBeg < YCoordEnd) Then
        SpikeBeg = 3
        SpikeEnd = 7
    End If
    
    'Debug.Print SpikeBeg, SpikeEnd
    
    If (XCoordBeg <> XCoordEnd) And (YCoordBeg <> YCoordEnd) Then
        ActiveSheet.Shapes.AddConnector(msoConnectorStraight, XCoordBeg, YCoordBeg, XCoordEnd, YCoordEnd).Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
        Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes(BeginShapeName), SpikeBeg
        Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes(EndShapeName), SpikeEnd
        
        Call RouteFormat(TruckRoute)
    End If

To make the chart more informative and fun, I put in two VB modules for colors, one for arrows and another for fonts.  Therefore, different route will be shown in different colors.  Some colors may look very similar to each other and some users may be color blind, so I later added another VB module (InsertText) to add the illustration of the route to the map.  The entire plotting code is listed in below.

Sub PlotWHLocation(BeginShapeName, TFS_XCood, TFS_YCood)
    Sheets("Route").Select
    ActiveSheet.Shapes.AddShape(msoShapeDonut, TFS_XCood, TFS_YCood, 10, 10).Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset1
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
        .Solid
    End With
    BeginShapeName = Selection.ShapeRange.Name
End Sub

 


Sub PlotRoute(TruckRoute, BeginShapeName, DestName, TFS_XCood, TFS_YCood, XCoordBeg, YCoordBeg)
    'Dimension these as arrays of unknown size. "Single" means single precision:
    Dim XCoordEnd As Double, YCoordEnd As Double, Distance As Double
    Dim BegLatitude As Double, BegLongitude As Double, EndLatitude As Double, EndLongitude As Double
    Dim LatFactor As Double, LongFactor As Double
    Dim TFSLat As Double, TFSLong As Double
    Dim SpikeBeg As Integer, SpikeEnd As Integer
    Dim BegLocation As String, EndLocation As String, EndShapeName As String
    ReDim Retailer(100)
    
    Sheets("Mileage").Select
    
    'Default Begin Location is the warehouse
    EndLocation = DestName
    
    'Debug.Print BeginLocation, EndLocation
    
    Range("LocationData").Select
    
    n = 1
    
    For n = 1 To 100
        Retailer(n) = ActiveCell.Value
        If Retailer(n) = EndLocation Then
            EndLatitude = ActiveCell.Offset(0, 5).Value
            EndLongitude = ActiveCell.Offset(0, 6).Value
            XCoordEnd = ActiveCell.Offset(0, 9).Value
            YCoordEnd = ActiveCell.Offset(0, 10).Value
            Exit For
        End If
        ActiveCell.Offset(1, 0).Activate
    Next n
    
    
    'Debug.Print "Ending Location", EndLocation, EndLatitude, EndLongitude, XCoordEnd, YCoordEnd
    Sheets("Route").Select
    ActiveSheet.Shapes.AddShape(msoShapeDonut, XCoordEnd, YCoordEnd, 10, 10).Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset1
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
        .Solid
    End With
    EndShapeName = Selection.ShapeRange.Name

    'Debug.Print "XCoordBeg", XCoordBeg, "YCoordBeg", YCoordBeg, "XCoordEnd", XCoordEnd, "YCoordEnd", YCoordEnd
    
    
    If (XCoordBeg < XCoordEnd) And (YCoordBeg < YCoordEnd) Then
        SpikeBeg = 7
        SpikeEnd = 3
    End If
    
    If (XCoordBeg > XCoordEnd) And (YCoordBeg > YCoordEnd) Then
        SpikeBeg = 3
        SpikeEnd = 7
    End If
    
    If (XCoordBeg < XCoordEnd) And (YCoordBeg > YCoordEnd) Then
        SpikeBeg = 7
        SpikeEnd = 3
    End If
    
    If (XCoordBeg > XCoordEnd) And (YCoordBeg < YCoordEnd) Then
        SpikeBeg = 3
        SpikeEnd = 7
    End If
    
    'Debug.Print SpikeBeg, SpikeEnd
    
    If (XCoordBeg <> XCoordEnd) And (YCoordBeg <> YCoordEnd) Then
        ActiveSheet.Shapes.AddConnector(msoConnectorStraight, XCoordBeg, YCoordBeg, XCoordEnd, YCoordEnd).Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
        Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes(BeginShapeName), SpikeBeg
        Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes(EndShapeName), SpikeEnd
        
        Call RouteFormat(TruckRoute)
    End If
    
    XCoordBeg = XCoordEnd
    YCoordBeg = YCoordEnd
    BeginShapeName = EndShapeName
End Sub

 


Sub RouteFormat(TruckRoute)
    
    With Selection.ShapeRange.Line
        Select Case TruckRoute
            Case 1
                .ForeColor.RGB = RGB(255, 0, 0) 'Red
            Case 2
                .ForeColor.ObjectThemeColor = msoThemeColorAccent2  'Brown
            Case 3
                .ForeColor.RGB = RGB(0, 128, 0) 'Dark Green
            Case 4
                .ForeColor.RGB = RGB(0, 0, 255) 'Blue
            Case 5
                .ForeColor.RGB = RGB(204, 0, 0) 'Bergundy
            Case 6
                .ForeColor.RGB = RGB(102, 0, 51) 'Dark Brown
            Case 7
                .ForeColor.RGB = RGB(0, 255, 0) 'Neon Green
            Case 8
                .ForeColor.RGB = RGB(119, 119, 119) 'Gray
            Case 9
                .ForeColor.RGB = RGB(255, 255, 0) 'Yellow
            Case 10
                .ForeColor.RGB = RGB(112, 48, 160)  'Purple
            Case 11
                .ForeColor.RGB = RGB(255, 0, 0) 'Red
            Case 12
                .ForeColor.ObjectThemeColor = msoThemeColorAccent2  'Brown
            Case 13
                .ForeColor.RGB = RGB(0, 128, 0) 'Dark Green
            Case 14
                .ForeColor.RGB = RGB(0, 0, 255) 'Blue
            Case 15
                .ForeColor.RGB = RGB(204, 0, 0) 'Bergundy
            Case 16
                .ForeColor.RGB = RGB(102, 0, 51) 'Dark Brown
            Case 17
                .ForeColor.RGB = RGB(0, 255, 0) 'Neon Green
            Case 18
                .ForeColor.RGB = RGB(119, 119, 119) 'Gray
            Case 19
                .ForeColor.RGB = RGB(255, 255, 0) 'Yellow
            Case 20
                .ForeColor.RGB = RGB(112, 48, 160)  'Purple
        
        End Select
        .Visible = msoTrue
        .Transparency = 0
        .Weight = 1.5
    End With
End Sub


Sub FontColor(TruckRoute)
    With Selection.Font
        Select Case TruckRoute
            Case 1
                .Color = -16776961  'Red
            Case 2
                .Color = -16737793  'Brown
            Case 3
                .Color = -16744448  'Dark Green
            Case 4
                .Color = -65536     'Blue
            Case 5
                .Color = -16763905  'Bergundy
            Case 6
                .Color = -13434778   'Dark Brown
            Case 7
                .Color = -16711936  'Neon Green
            Case 8
                .Color = -8355712   'Gray
            Case 9
                .Color = -16724788  'Yellow
            Case 10
                .Color = -6279056   'Purple
            Case 11
                .Color = -16776961  'Red
            Case 12
                .Color = -16737793  'Brown
            Case 13
                .Color = -16744448  'Dark Green
            Case 14
                .Color = -65536     'Blue
            Case 15
                .Color = -16763905  'Bergundy
            Case 16
                .Color = -13434778   'Dark Brown
            Case 17
                .Color = -16711936  'Neon Green
            Case 18
                .Color = -8355712   'Gray
            Case 19
                .Color = -16724788  'Yellow
            Case 20
                .Color = -6279056   'Purple
        End Select
            
        .TintAndShade = 0
    End With
End Sub

 



Sub InsertText(TruckRoute, RouteNum, XCoordBeg, YCoordBeg)
    Dim WordLength As Integer
    If Left(RouteNum, 2) = 10 Then
        WordLength = 10
    Else
        WordLength = 9
    End If
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, XCoordBeg - 20, _
        YCoordBeg - 20, 70, 22).Select
        
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = RouteNum & " Truck"
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Line.Visible = msoFalse
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, WordLength).ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, WordLength).Font
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        Call FontColor(TruckRoute)
    End With
    
End Sub

Another problem I encounted when I created this program is that the map that I used is "flattend".  The earth is a global.  Many map companies flattend their maps to make it looker "better".  The latitudes that are closer to the north and south poles will have much distortion than the ones close to the equator.  That's why in some maps, you may see that Russia is bigger than the whole African continent, which we know that it is not true.  As a  result, that will be almost impossible to put the shape in the correct position of the map but using latitdue and longitude.

 

I have another article to illustrate this.  Please visit my another artilce "Find the coordinates of a Shape Object".