请教一下各位excel大神!救救孩子吧!
我模仿写出TSP(travelling salesman problem) 的VBA代码。但是运行不了。同时有些地方我是照搬,我分析的时候觉得有点疑惑,希望大神们可以点开我的附件帮忙修改或者指点一下我。
1,我在调用的两个function中分别引用了三个餐数:current_city,the_city,next_city。
route(x)=next_city(route(x-1))
if dist(current_city,x)<short_dist
the_city=route(x)
这三个语句我不太能理解,怎么这三个参数就可以直接用上,并且赋值了?
例如第一个,我一开始声明起点route(0)=1(1是B城市),然后route(1)=next_city(1),中文意思应该是想表达下一个城市是这个,再下一个是那个,然后组成一条城市链(B-D-E-F-H······我随便编的)也就是答案。但是这个等式我觉得没有表达这个意思。还有current_hotel,一开始都没有给这个参数赋值,这个current_City和x城市之间的距离是怎么得出来的,current_City是从哪个城市开始。总而言之,我不理解这几个参数为什么是这样表达。
2,假设中间核心代码都是正确的,我如何计算整个程序运行的时间。里面好几个sub,好几个function,我还要添加button按钮,里面代码是用来计算程序运行时间,本身也是一个sub,那么我应该怎么安排这个顺序,然后计算整个程序的运行时间?
3,redim dist(city_count-1,city_count-1)这里为什么要重新redim,并且这个括号里面的意思是什么?
4,Ubound(city)和Ubound(route)本质上不是一个意思嘛,为啥在function里面要变成Ubound(route)?是因为我一个是围绕城市,一个围绕路径route?
啊,我觉得我已经尽力去模仿+搬运这个代码和理解这个代码,但是仔细揣摩之后,感觉有些地方真的让我挺纠结的,就是我感觉我懂他想干嘛,但是我不懂为什么表达的这么直接,是否漏掉细节。同时,这个代码运行不了,好多bug,而且感觉是一些细节bug,我真是要头秃了。
Option Explicit
Dim city() As String
Dim route() As Integer
Dim dist() As Integer
Sub calc_route()
Dim x As Integer
route(0) = 1
For x = 1 To UBound(city)
route(x) = next_city(route(x - 1)) 'i cant understand next_city
Next
For x = 0 To UBound(route)
Debug.Print route(x), city(route(x))
Next
End Sub
Function next_city(current_city As Integer) As Integer
Dim x As interger
Dim short_dist As Integer
Dim close_city As Integer
short_dist = 32767
For x = 0 To UBound(route)
If Not been_there Then
If dist(current_city, x) < short_dist Then
short_dist = dist(current_city, x)
close_city = x
End If
End If
next_city = close_city
End Function
Function been_there(the_city As Integer) As Integer
Dim x As Integer
For x = 0 To UBound(route)
If the_city = route(x) Then
been_there = True
Exit Function
End If
Next
been_there = False
End Function
Sub init()
Dim x As Integer
Dim y As Integer
Dim city_count As Integer
city_count = Range(distance.Range("a2"), distance.Range("a2").End(xlDown)).Count
ReDim city(city_count - 1)
ReDim route(city_count - 1)
ReDim dist(city_count - 1, city_count - 1)
For x = 0 To UBound(city)
city(x) = distance.Cells(x + 2, 1)
route(x) = -1
Next
For y = 0 To UBound(city)
For x = y + 1 To UBound(city)
dist(y, x) = distance.Cells(y + 2, x + 2).Value
dist(x, y) = dist(y, x)
Next
Next
End Sub
Sub Button1_click()
Dim start_time As Double
Dim end_time As Double
Dim total_time As Double
start_time = Timer
end_time = Timer
total_time = end_time - start_time
MsgBox total_time & "seconds"
End Sub