Question
Please do this in VBA microsoft. I will upvote. Here is the excel sheet Here is solution HERE IS THE CODE FOR THE SOLUTION EXCEL.
Please do this in VBA microsoft. I will upvote.
Here is the excel sheet
Here is solution
HERE IS THE CODE FOR THE SOLUTION EXCEL. Thank you!!
Option Explicit
Option Base 1
Sub EX_1GenerateDistanceMatrix() Dim strAns As String Dim intNCities As Integer, i As Integer, j As Integer wsDistance.Activate 'Get the number of cities to be in the Traveling Salesman Problem intNCities = InputBox("Enter number of cities in the TSP network.", "Generate TSP inputs.", 5) 'Clear all the contents ActiveSheet.UsedRange.Clear ' Write Values to cells A1 and A3 Range("A1") = "TSP" Range("a3") = "Distance" 'Generate Distance Matrix, also write headers to distance matrix With Range("a3") 'Write headers to worksheet For i = 1 To intNCities .Offset(i, 0) = "City " & i .Offset(0, i) = "City " & i .Offset(i, i) = 0 For j = i + 1 To intNCities 'Generate distances (matrix is symmetric with 0's in the diagonal) .Offset(i, j) = WorksheetFunction.RandBetween(50, 100) .Offset(j, i) = .Offset(i, j) Next j Next i End With End Sub
Sub EX_2NearestNeighbor() Dim iSeg As Integer, i As Integer, j As Integer, iStart As Integer Dim nCities As Integer, nowAt As Integer, nextC As Integer Dim intTD As Integer, intMaxDist As Integer, intMinDist As Integer Dim rngA As Range Dim Dist() As Integer Dim blnVisited() As Boolean Set rngA = Range("A3") With rngA 'Find nCities nCities = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count 'redim Dist() and blnVisited() ReDim Dist(nCities, nCities) ReDim blnVisited(nCities) 'read Distance matrix from Excel, assign to Dist() For i = 1 To nCities For j = 1 To nCities Dist(i, j) = .Offset(i, j) Next j Next i 'Finding the longest distance and assign to variable intMaxDist intMaxDist = 0 For i = 1 To nCities For j = i + 1 To nCities If Dist(i, j) > intMaxDist Then intMaxDist = Dist(i, j) Next j Next i 'Mark all cities unvisited For i = 1 To nCities blnVisited(i) = False Next i 'Write Output Headings .Offset(nCities + 2, 0) = "From" .Offset(nCities + 3, 0) = "To" .Offset(nCities + 4, 0) = "Distance" .Offset(nCities + 5, 0) = "Tot Distance" 'initialize total distance intTD intTD = 0 'Define iStart = Starting City Number 'Use city 1 as the starting city (iStart), iStart = 1 'Cycle through all cities to find nearest neighbor excluding already visited cities 'Report back to excel the optimal route ' Current segment starting city (nowAT) blnVisited(iStart) = True nowAt = iStart For iSeg = 1 To nCities - 1 'Find the closest city for nowAt intMinDist = intMaxDist + 1 For i = 1 To nCities If blnVisited(i) = False And Dist(nowAt, i) . Based on EX_2, how can you improve the nearest neighbor Heuristic algorithm? 1. First allow user to identify starting city (iStart) 2. Loop through all possible starting cities (1 to nCities) and pick the shortest total distance route Due before class 9.2, 10/28/2021, starts Submit one file "HWTSP_yourlastname.xlsm" to Canvas; your file should include two subs: one to allow user specify starting city; the other to find the shortest route among different starting cities. Hint: For the second sub, make sure you initialize cities as unvisited before finding shortest total distance for each iStart city. Note: Use the distance matrix produced in EX1 as the input for this HW, you don't need to generate the distance matrix again. D E F G I City 4 86 82 A B 1 Traveling Salesman Problem 2 3 Distance City 1 City 2 4 City 1 0 5 City 2 62 6 City 3 86 7 City 4 80 8 City 5 76 9 10 From 1 11 To 2 12 Distance 62 13 Tot Distance 298 City 3 62 0 82 88 84 City 5 80 88 85 0 84 0 85 70 76 84 70 84 0 1 N 3 5 3 3 5 70 3 82 4 4 84 4 1 80 14 15 16 D E LL G H J 1 TSP 2 City 1 City 9 City 2 0 85 80 56 82 93 66 89 70 62 City 3 85 0 60 52 94 99 63 56 78 64 City 4 80 60 0 65 54 79 72 50 82 95 City 5 56 52 65 0 70 90 66 77 82 51 City 6 City 7 City 8 66 63 79 72 90 66 0 75 99 75 0 100 99 100 0 66 96 98 58 69 94 62 66 63 50 77 66 96 98 0 91 81 City 10 70 62 78 64 82 95 82 51 58 62 69 66 94 63 91 81 0 59 59 0 14 15 From 10 9 5 8 7 6 1 4 2 7 5 6 4 10 51 107 3 8 50 328 8 2 56 384 59 166 58 224 54 278 63 447 100 547 93 640 16 To 17 Distance 18 Tot Distance 19 20 21 . Based on EX_2, how can you improve the nearest neighbor Heuristic algorithm? 1. First allow user to identify starting city (iStart) 2. Loop through all possible starting cities (1 to nCities) and pick the shortest total distance route Due before class 9.2, 10/28/2021, starts Submit one file "HWTSP_yourlastname.xlsm" to Canvas; your file should include two subs: one to allow user specify starting city; the other to find the shortest route among different starting cities. Hint: For the second sub, make sure you initialize cities as unvisited before finding shortest total distance for each iStart city. Note: Use the distance matrix produced in EX1 as the input for this HW, you don't need to generate the distance matrix again. D E F G I City 4 86 82 A B 1 Traveling Salesman Problem 2 3 Distance City 1 City 2 4 City 1 0 5 City 2 62 6 City 3 86 7 City 4 80 8 City 5 76 9 10 From 1 11 To 2 12 Distance 62 13 Tot Distance 298 City 3 62 0 82 88 84 City 5 80 88 85 0 84 0 85 70 76 84 70 84 0 1 N 3 5 3 3 5 70 3 82 4 4 84 4 1 80 14 15 16 D E LL G H J 1 TSP 2 City 1 City 9 City 2 0 85 80 56 82 93 66 89 70 62 City 3 85 0 60 52 94 99 63 56 78 64 City 4 80 60 0 65 54 79 72 50 82 95 City 5 56 52 65 0 70 90 66 77 82 51 City 6 City 7 City 8 66 63 79 72 90 66 0 75 99 75 0 100 99 100 0 66 96 98 58 69 94 62 66 63 50 77 66 96 98 0 91 81 City 10 70 62 78 64 82 95 82 51 58 62 69 66 94 63 91 81 0 59 59 0 14 15 From 10 9 5 8 7 6 1 4 2 7 5 6 4 10 51 107 3 8 50 328 8 2 56 384 59 166 58 224 54 278 63 447 100 547 93 640 16 To 17 Distance 18 Tot Distance 19 20 21
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started