Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.image text in transcribed

Here is the excel sheet

image text in transcribed

Here is solution

image text in transcribedHERE 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Income Tax Fundamentals 2013

Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill

31st Edition

1111972516, 978-1285586618, 1285586611, 978-1285613109, 978-1111972516

More Books

Students also viewed these Accounting questions