Link¨oping University
Department of Mathematics
Division of Optimization
Oleg Burdakov
April 7, 2015
Computer lab information
TAOP24, Advanced course on optimization
Contents
1 Introduction
1.1 General lab information . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 The computer system, text editors and program runs . . . . . . . . . . . . .
1
1
2
2 Computer Excercise 1 — Network optimization
3
3 Vineopt: Visual Network Optimization
3.1 Basic window . . . . . . . . . . . . . . .
3.2 Types of network problems . . . . . . .
3.3 Menus . . . . . . . . . . . . . . . . . . .
3.4 Input of a new network . . . . . . . . .
3.5 Right button menu . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4 Computer Exercise 2 — Integer optimization
5 Computer Exercise 3 — Heuristics for traveling salesman problem
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
5
6
6
8
8
9
11
Computer lab info
1
TAOP24, Advanced course on optimization
Introduction
1.1
General lab information
Three computer exercises are included in the course. These cover the following:
Computer exercise 1: Network optimization
Computer exercise 2: Integer optimization
Computer exercise 3: Heuristics for traveling salesman problem
Registration for the computer exercises are made on some of the first lessons. Any late
registration can be made directly to the lecturer. Laboratory facilities are located in house
B at entrance B21, 2nd floor, hallway B and to the left. These are named Roxen, Glan,
Hunn and Boren (a.k.a Sj¨
osystemet). The computer lab exercises are organized as follows.
Students make computer exercises 1, 2 and 3 at their own. One computer lab session is
scheduled for assisting with exercise 3. It is not compulsory to attend this lab session.
The lab results are reported at each team of students to the lecturer for his approval.
The following regulations applies to all computer exercises:
• A team is normally to consist of 6 students.
• The examination consists in an oral team work presentation to the lecturer. The
date and time is to be agreed with the lecturer. For more information regarding the
presentations, read for each computer exercise on pages 3, 9 and 16, respectively.
The three parts (network, integer and heuristics) should be presented all together.
The total time for the presentation is assumed to be around 1 hour.
• It is allowed to split up the work among the team members. However, each member
should be able to explain the results of each of the tasks given to the team.
• The oral presentation for each part is to be performed no later than by Thursday,
May 28 .
Computer lab info
1.2
TAOP24, Advanced course on optimization
The computer system, text editors and program runs
Logging in on MAI’s computer system is done with a user name and password for MAI/ISY
which you should have available. The computers use the operative system LINUX. LINUX
commands are entered in a terminal window which can be opened by going to Applications
in the left upper corner, then choosing System Tools and Terminal. An alternative is to
choose Open Terminal after clicking the right mouse button. Thereafter one enters the
command TAOP24setup in the terminal window in order to define the application paths.
(Do not forget this!) It is possible to reuse and edit old commands by using the arrow
keys. Listed below are some common LINUX commands.
ls
Listing of content of current directory.
cd dir
Change directory to dir .
mkdir dir
Create a new directory dir.
more file
Print file on the screen with a stop after each page.
rm file
Remove file.
cp file1 file2 Copy file1 to file2 .
lpr file
Print file on the default printer.
Log out by choosing a proper option after clicking the EXIT-button at the bottom.
Text editor
Some applications need to create and edit text files (foremost Ampl). They can be found
in Applications → Acessories. The simplest is gedit.
Launching Ampl and Excel
In order to launch Ampl in the facilities one can write in the terminal window
ampl < kommandofil.run
where kommandofil.run is your own command file (use any name you want). The model
file, data file and command file are written and edited preferably in a text editor.
Computer lab info
2
TAOP24, Advanced course on optimization
Computer Excercise 1 — Network optimization
In this laboratory task the team shall formulate and solve a min-cost flow problem. The
purpose is to
• demonstrate the work sequence for practical solutions of an optimization problem.
• practice mathematical formulation.
The software Vineopt is available on MAI’s computers (see attached instructions on
how to use Vineopt). This software is to be used in solving the problem. Vineopt can
be launched from a terminal window by entering TAOP24setup and thereafter vineopt
For further instructions concerning the application, see Section 3.
Preparation
The computer exercise preparations by the team assume: a mathematical formulation of
the problem in the form of a network with specified arc costs, any upper and lower bounds
on the flow, and strengths of sources and sinks, in such a way that it can be solved by a
min-cost flow software.
Each team (at most 6 students) will receive three problems to solve. These are handed
out during scheduled time. If the problem description is found ambiguous the students in
the team are themselves to make assumptions if they find them motivated. If the students
are unsure of how to formulate the given problems, they can discuss their formulation with
the lecturer.
Presentation
The oral presentation is to mainly report the following:
1. The mathematical model (network model). The students should be able: to explain
the meaning of each detail of the network and also the relation of any nodal and arc
data with the original problem.
2. The result. The students should be able: to interpret the results, that is, to be
able to explain the arc flow in terms of the original problem; to explain any surprising/interesting results.
To make the discussions easier for everyone, attach printouts of the network models
with nodal strengths, arc data and the solution results. The oral presentation is to be
done no later than by Thursday, May 28.
Computer lab info
TAOP24, Advanced course on optimization
Computer lab info
3
TAOP24, Advanced course on optimization
Vineopt: Visual Network Optimization
Vineopt is an application for visualization and optimization of graph and network problems, like linear min-flow cost problems, cheapest road problem, cheapest generating tree
problems and travelling salemans problems. The application enables graphical input of
network data and draws the network on screen. One can change the network (that is,
change all the network data) and also change the graphical representation of it (zoom,
etc). Also, solvers for optimization problems can be called and the solution is then visualized in the network or is shown in tables.
The application is launched in a terminal window via the command
vineopt
Do not place the job in the background by entering & after vineopt. The window
works as a console and receives certain printouts. Also, is may be a good idea to keep the
emergency exit with ”Control”-c. One can give parameters in the command line, e.g, to
directly read in a problem, see the more in-depth manual under the help menu.
3.1
Basic window
When Vineopt is launched, a basic window is opened where most of the operations take
place, see figure 1.
Figure 1: The basic window with menus and submenus
Computer lab info
TAOP24, Advanced course on optimization
This window contains the following parts. On top, under the title row, is the main menu
located along with its submenus File, Optimization, Visualization, Changes and Help.
These are described closer in section 3.3.
The first status row below the graphical window shows which mode of change (mode)
that is currently active, thereafter the problem name (filename of data input) and the size
of the problem. Appearing last on the row are the coordinates for the mouse (in the scale
of the network). Coordinate window turns red when the application is doing calculations
or is handling input/utput, which indicates that one should wait a litte before starting
another activity.
Therebelow follows the second status row, which gives concise instructions on how to
operate in the current mode and occasionally information on what has just been done.
Furthest down, the third status row is located, which gives the problem type and also if
one has been editing the problem without saving.
3.2
Types of network problems
Vineopt can handle many different problem types based on different network types. One
fundamental problem type is the linear min-flow cost problem in directed networks, which
contains the following lists of data: arc data list, node list with demand, coordinate list
and node list with nodal names. For each specific problem these lists are available for
viewing under the menu Visualization.
The network types ”cheapest road” and ”undirected network” are used for directed
and undirected graphs with arc costs respectively (but without limitations on the flow).
Undirected networks are, for example, used in cheapest generating tree problems and
travelling salesman problems.
3.3
Menus
Submenus File (read/write data), Optimization (optimization), Visualization (visualisation, change how the network is shown), Changes (change of data) and Help (helpfunctions) are here described closer.
File
Here one can read in input data for a problem. (Input data contains information on the
network type, which is then set automatically). One can also save data under the current
name or a new name. The picture of the graph can be exported to a postscript file or
be printed out directly on any printer which can handle postscript format. The current
solution can be saved to file. One can write and read configurational data, that is, set
colours and so on. It is also possible to list and erase existing network files.
Optimization
Vineopt contains solvers for min-flow cost problems, cheapest road problems, cheapest
generating tree problems and travelling salesman problems. Some optimization codes
are freely available for academic (non-commercial) use. Several of the codes are of own
production.
Computer lab info
TAOP24, Advanced course on optimization
Visualization
How the network is drawn is decided here. One can, among other options, choose to show
or not show nodes, arcs, nodal names. One can have arcs with flow marked out more
distinctively. Arch costs, arc capacities and arc flows, along with demand data and nodal
prices can be drawn in the network. One can also choose how to draw the arcs connections
to the nodes.
One can select a namegiven node. It is possible to zoom in/out, make the nodes larger
or smaller and also move the network up/down/left/right in large or small steps. Also,
all the colours can be changed and arc and nodal markings can be moved. A background
picture (.gif format) can be shown in the graphical window. One can also see all the data
and solutions numerically in separate windows, each of which can be printed or saved to
file.
Changes
Here one configures what mode of change (mode) that is currently desirable. The configuration remains in effect until one changes it. One can also change, move and remove
nodes and arcs.
Choice of node is done in such a way that when the left mouse button is pressed, the
node closest to the cursor is selected (even if it is a little distant). Choice of arc is done
by first selecting initial node and final node. (The arc is not allowed to exist and therefore
one is unable to handle parallel arcs). The node closest the cursor is chosen as initial node
when the left mouse buttom is pressed down for the first time. The node closest to the
cursor is chosen as final node when the left mouse button is pressed down for a second
time (that is, when an inital node has already been chosen). The following modes are
available:
Move node Choose node in accordance with the description above. The node is moved
with the cursor as long as the left mouse button is held down.
Add node The node is placed where the cursor is when one presses the left mouse button.
It is given sink strength zero.
Delete node The node closest to the cursor is removed when the left mouse button is
pressed. All arcs which connect to the node is then also removed.
Change demand Choose node in accordance with the description above. A window will
appear where one is able to enter new demand (net sink strength) for the node in
question.
Add link Choose arc in accordance with the description above. When final node has
been chosen a menu will appear with arc cost (initiated as the Euclidean distance
between the nodes), arc capacity (initiated as a large number) and a lower limit
(initiated as zero). All of these numbers can be changed. Continue by pressing Save
to save.
Computer lab info
TAOP24, Advanced course on optimization
Change link Choose arc in accordance with the description above. Continue as when
one adds an arc.
Delete link Choose arc in accordance with the description above. The arc is removed as
soon as one selects a final node.
Move link Choose arc in accordance with the description above, then pick a new initial
node and final node.
Choose starting node The node closest to the cursor is chosen as inital node when the
left mouse button is pressed.
Choose ending node NThe node closest to the cursor is chosen as inital node when the
left mouse button is pressed.
A directed network can be made undirected, and an undirected can be made directed.
One is able to change data in table format. It is possible to add/remove many arcs in
different ways, e.g, such that one has a complete network. Several data can be changed at
once, e.g, add a constant to all arc costs / arc capacities or set all arc costs / arc capacities
to a certain value. The nodal names can be changed.
Network data can be controlled in different ways. One can sum up a total demand
(which is to be zero in any min-cost flow problem). One can control whether the triangle
inequality is valid, examine the valency of the nodes and also contol whether or not the
network is a connected one.
Help
Found here is a minor help text in a separate window (in Swedish or English), the complete
manual via postscript and also a list of existing shorthand commands.
3.4
Input of a new network
Start the input by choosing Add node in the Changes menu. Draw the nodes desired with
the right strength and preferably set descriptive names. Continue with arcs between the
nodes with the help of Add link in the Changes menu. Set correct upper and lower limits
on the arcs. Save the network from the File menu and thereafter solve the problem with a
solver found in the Optimization menu. The solution can be visualized in different ways,
e.g, nodal prices and reduced costs can be obtained from the application.
3.5
Right button menu
If one presses the right mouse button when the cursor is within the graphical window,
one can make certain changes related to the node closest to the cursor, with the help of a
menu that appears. One can remove the node (and all connecting arcs), switch name on
it and change its sink strengths. Also, one can remove or change the data for an arc that
connects to the node in question. The arc is chosen in a menu which contains all arcs that
connects to the node.
Computer lab info
4
TAOP24, Advanced course on optimization
Computer Exercise 2 — Integer optimization
In this computer exercise you are to formulate and solve integer problems. The purpose
is to
• demonstrate the work sequence for practical solutions of an optimization problem.
• practice mathematical formulation.
• use a commercial modeling language (Ampl).
The software Ampl is installed on MAI’s computers and is also available for download
(student version with a maximum of 300 variables and constraints), see the course webpage.
How to use Ampl is described in the course litterature.
Preparation
ration is in need of preparations by the team, a flow problem is to be formulated mathematically in the form of a network with arc costs, any upper and lower limits on the flow
and strengths on sources and sinks, in such a way that it can be solved by a min-cost flow
code.
The computer exercise is in need of preparations by the team, a problem is to be formulated mathematically as an integer problem with variables, target function and constraints.
An integer problem can contain both integer variables, binary variables and continuous
variables. Problems are to be written in Ampl format in order to be solved. To your
help there is a data file (labname.dat) and the necessary definitions are found in a model
file (labname.mod). To your help is also a command file (labname.run). What you need
to do is to put variable definitions, target function and constraints in the model file, also
one might want to change a little in the command file such that your result is printed. In
principle you should not need to change anything in the data file or the definitions in the
model file, but it is of course not forbidden to make changes to achieve, from your view,
a more simple formulation. However, you may not change the conditions of the problem.
Each team (at most 4 students) will receive two unique problems to solve. These are
handed out during scheduled time. If the problem description is found ambiguous the
students in the team are themselves to make assumptions if they find them motivated. If
the students are unsure of how to formulate the given problems, then they can go through
their formulation with the lecturer.
Presentation
The oral presentation shall mainly include the following:
1. The mathematical model: Be able to give and explain the assumptions made in the
model and also be able to account for the target function, variables and conditions.
2. The result: Be able to interpret the result, that is, being able to translate the
variables into words. Be able to explain for unexpected/interesting results. Be able
to discuss the reasonableness of the result (e.g, if all conditions are satisfied).
To make the discussions easier for everyone, attach prints of the Ampl models. The oral
Computer lab info
TAOP24, Advanced course on optimization
Computer lab info
5
TAOP24, Advanced course on optimization
Computer Exercise 3 — Heuristics for traveling salesman
problem
Introduction
This computer exercise aims at create understanding in how Excel and Visual Basic can
be used as programming environment in solving a complex optimization problem. The
purpose is to also show an ordinary and well studied problem type, and how it can be
solved by heuristics.
The problem to be solved is to decide in what order one shall drill a number of holes in
a metallic disc (done by an automated drilling machine) such that the total distance the
drill will need to move is minimized. The problem can be modeled as a Travelling Salesman
Problem (TSP) and in the computer exercise it is included in the task to implement two
heuristics for the TSP.
At the computer exercise an Excel-file (lab3holes.xls) is needed and can be found
on the course webpage http://www.mai.liu.se/∼olbur/kurser/TAOP24/
Observe that when you download or open an Excel-file containing macros, one often
receives a question concerning activation of macros (depending on the settings in Excel or
the browser). This file contains macros which must be activated in order for the application
to function.
Specified below are the preparation tasks, followed by a more detailed problem description. This is followed by the tasks to solve and lastly the demands concerning the written
report are specified. In the end of this section there are descriptions of the Excel-files
and details concerning the macro which contains the Visual Basic code. Descriptions of
heuristics for solving the Travelling Salesman Problem can be found in the course litterature.
Preparations
• Look at Figure 4 and suggest (manually) a sales route which visits all the nodes in
the figure. Write down the sequence of nodes in Table 1 (= Task 1a).
• Read about the heuristics Cheapest insertion and Farthest insertion in the course
litterature chapter 16.
• Write down a suitable Visual Basic code (on paper for example) (= Task 2a). The
syntax you will need to know in Visual Basic for implementation of the heuristics
are found in the end of this section.
Computer lab info
TAOP24, Advanced course on optimization
Problem description
The company Hole Makers is planning on producing a new product with a rate of 1000
units/day. In making the product, a part consists of drilling 25 holes (in two different
sizes) in a metallic plate. In order to do this an automated drilling machine is used. A
conveyor belt transports the metallic plate to the drilling machine, after which it is put
in place for drilling. After the drilling the plate is transported from the machine via the
conveyor belt, see Figure 2. During the moments when the plate is transported in and out
the drill head must be placed at the side in a so called resting point (corresponds to node
1 in Figure 3).
Figure 2: A schematic of the conveyor belt and the drilling machine
The usage of the drilling machine is a bottle neck operation in the company’s production line and the company does not want to use the machine for more than shift. Additional
production when going beyond 8 hours raises an overtime cost at approximately 1000 kr/h.
Time expenditure in the drilling machine is mainly due to the following moments:
• Placement and removal of every metallic plate, takes 10 seconds in total.
• Drilling of the holes, takes a total of 10 seconds for each metallic plate.
• Movement of the drill head.
• Change of drill heads.
The time it takes to move the drill head can be approximated as the total distance
it moves in the sequence (start and finish in node 1) divided by the movement speed v,
(v = 20 centimeters/second).
Because of the holes being of two different sizes one needs to include the time it takes
to change the drill head in the total drilling time. Change of drill head is necessary each
time two consecutive holes (nodes) in the sequence has a different size.
To gain clarity on the least time needed in order to move the drill head, the initial
time for changing the drill head is set to zero. This makes it equivalent to be solving a
TSP which starts in node 1 and vists all the other nodes (holes) given that one uses the
Euclidean distance between the nodes.
Computer lab info
TAOP24, Advanced course on optimization
In Figure 3 one can view a sales route (TSP-route) for the drilling problem given (a thin
line around a node indicates hole type 1 and a thicker line indicates hole type 2).
Figure 3: An example on solving. (TSP-route: 1 13 12 14 20 7 3 2 17 19 18 21 22 23 26
24 25 15 16 11 10 9 4 8 5 6 1).
Tasks
The program package Excel is found under Windows NT (Citrix) which is opened from
the menu Applications (toolbar menu at the bottom) and then click on the arrow above
the symbol which looks like a computer and there choose Windows. Excel is then found
the usual way by selecting Start → Programs → Excel.
When Excel has been opened, click on ”Enable Macros”.
Computer lab info
TAOP24, Advanced course on optimization
Task 1 – Ignore the change of drill size
To begin with we study the problem in finding a drilling sequence where one does not take
into account the need to change the size of drill heads (all holes have the same size). This
can be done by creating a TSP-route where one only uses the Euclidean distance between
the nodes. These distances are found in a data sheet ”Euclidean Dist.” in the Excel file.
a) [Preparation!] In Figure 4, start in node 1, suggest manually a good solution to
the TSP. Enter the sequence of nodes in Table 1.
Figure 4: The apperance of a metallic plate with 25 holes to be drilled
Sequence:
Node:
1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
1
Table 1: Your proposed TSP-route
Computer lab info
TAOP24, Advanced course on optimization
b) Enter your proposed sequence in the Excel sheet ”Problem”, coulmn ”Suggestion”.
c) Run the macro ”main” and choose ”Euclidean distances”, ”Draw suggested solutions” and then ”Draw simple solution”. In the end of this section there is more information on how to run macros in Excel. The results can be studied in the Excel sheets
”Sug solution” and ”Simple solution”. Compare the total distance in your proposed route
with the total distance in ”create simple path” which is shown in the Excel sheet ”Simple solution”.
Task 2 – Implementation of a heuristic
a) [Preparation!] Prepare on paper a Visual Basic code for the two heuristcs Cheapest
insertion and Farthest insertion such that you are later able to implement these two
heuristics. See ”Usage and modification of application” on page 19 for more information
about the code and the syntax used in Visual Basic.
b) Open the Visual Basic editor and write your code in the procedures ”create cheapest path”
and ”create farthest path”.
c) Run the macro ”main” to calculate the total distance which is obtained with your
new procedures and choose to draw all four solutions. Compare the obtained solutions.
Task 3 – Change of drill size in node 1
In the initial design proposal of the machine it is such that the drill head must be placed in
node 1 each time one wants to change the drill head size. The time it takes to change drill
head in node 1 is negligible and so the time consuming moment is to move the drill to node
1 each time the size is to be changed. If the machine is to drill two consecutive holes with
different sizes the drill needs to go via node 1. This can, in the TSP, be represented by a
modification of the distance between two holes of different sizes. These modified distances
can be studied in the distance table located in the sheet ”Change drill” in the Excel file.
These modified distances are then used if one chooses ”Use change drill distances” when
the macro is run.
Test your implemented heuristics on the problem where one take into account changing
of the drill head. Compare your solutions with the solution where one uses the heuristic
”create simple path”. The total distance in this solution is 442 cm, which means that it
takes 442/20 + 10 + 10 = 42.1 seconds to drill all the holes in the metallic plate. The total
time for a days work of 1000 units is then 11.69 hours.
Task 4 – Changing the drill size with the help of a cassette
The company is considering having a different design of the machine. The alternative
design proposal includes the possibility to change drill heads without the need to pass
node 1. This is achieved by mounting a cassette with several different drill head sizes on
the drill. However, this alternative demands that the drill pauses for half a second each
time the drill head is to be changed. This can be represented by adding 10 centimeters to
the Euclidean distances between two holes of different sizes. These modified distances can
be studied in the sheet ”Use cassette” in the Excel file and is used if one chooses ”Use
cassette distances” when the macro is run.
Computer lab info
TAOP24, Advanced course on optimization
The extra cost of this investment is estimated to 200 kr/day, which is based on both labor
and capital costs during the lifetime for this extra equipment.
Use your heuristics in order to compare the two alternatives of either changing the drill
head size in node 1 or changing the drill head size with the help of a cassette. Compare the
cost reduction for overtime (due to shorter operation time) with the cost of investment of
a cassette and give the company a recomendation of whether or not to invest in a cassette.
Task 5 – Solving a larger problem
Now go on and try your heuristics on a somewhat larger problem. Run the macro ”main”
and choose ”Use Euclidean distances - Large”. What are sequences/routes and total
distance obtained?
Results
The results are presented orally. Bring the following prints to the presentation as basis
for discussions:
1. Print of Visual Basic code for the two heuristics (Cheapest insertion and Farthest
insertion). Think of making some commentaries in the code so that it is easy to
follow the solution.
2. Answers to, and short commentaries to, tasks 1 to 5. As answer to the tasks it is
sufficient with a print of the ”Solution” sheet from Excel. However, do not forget
to make comments on the tasks.
The oral presentation is to be done no later than tuesday 08–05–13.
Computer lab info
TAOP24, Advanced course on optimization
Application shells in Excel for the TSP
This section contains four parts. Part 1 describes the Excel file you will be using in
the computer exercise. Part 2 exemplifies and describes briefly the macro modules which
are used in Excel. Part 3 contain some help on how to implement the procedures ”create cheapest path” and ”create farthest path”. Part 4 contain instructions on how to use
the operations which will most likely be needed in the tasks.
Contents of the Excel file
The Excel file contains a number of sheets which treat input and output, namely the
sheet ”Problems” and the four sheets which draws different solutions. The Excel file
also contains four sheets where the distance matrices for the different problems (specified
above) are found. The application is available in two macro modules.
Problem sheet
The Excel sheet ”Problems” contain most of the input to the application, see Figure 5
(dark shadowed).
Figure 5: The sheet ”Problems” with 10 nodes
Computer lab info
TAOP24, Advanced course on optimization
Input data contains the number of nodes, x, y coordinates for each hole to be drilled
and the distance matrices. Depending on which problem that is to be solved, different
distance matrices are used. They are saved as different sheets in the Excel file. x and
y coordinates for the nodes (holes) have been used to calculate the distance matrices and
for drawing the solutions.
The sheet ”Problems” also presents the numerical results. In the column Suggestion
it is possible to enter an arbitrary (but allowable) sequence of nodes and in the columns
Simple, Cheapest and Farthest are the solution sequences presented from each respective
heuristic. The total distance for each sequence is calculated and presented in this sheet as
well. All output is updated when the application is run.
Output sheet
The Excel sheets ”Sug solution”, ”Simple solution”, ”C solution” and ”F solution” contain the solutions to each respective heuristic. The larger holes are illustrated with larger
circles than the smaller holes. The sheets illustrates for the user the proposed solutions
from the different heuristics visually. These sheets are easy to print, just use the usual
print command in Excel.
Sheets for the distance matrices
These sheets contain the distance matrices for the different problems. The sheets contain
the following distances:
• Euclidean distances without taking into account of change of drill head size (”Euclidean Dist.”).
• Euclidean distances plus the distance forth and back to node 1 if two consecutive
holes in the sequence have different size. This corresponds to changing the drill head
size in node 1(”Change Drill”).
• Euclidean distances plus the distance forth and back to node 1 if two consecutive
holes in the sequence have different size. This corresponds a drill head change with
a cassette (”Use Cassette”).
The macro modules in Excel
The application is found the macro module ”Main Program”. The application contains the
code for the main program, read and write data, the simple heuristic ”create simple path”
and also shells to the heuristics ”create cheapest path” and ”create farthest path”, where
you are to modify andd add to the code. To be able to view and edit the macros which
inside the file, choose Tools → Macro → Visual Basic Editor from the menu.
Computer lab info
TAOP24, Advanced course on optimization
Usage and modification of the program
The program is run by choosing Tools → Macro → Macros and thereafter choosing Macro
Main. A simpler way is to enter the command Ctrl-r and a dialogue window will appear.
Select the sequences that are to be drawn and thereafter run the program by clicking the
Run button. There is no need to compile the code, just change the code in the program
and then run the macro, Excel will itself compile the code. It is sufficient to modify the
procedures ”create cheapest path” and ”create farthest path” where one can use certain
smaller parts of the code from the procedure ”create simple path”, see below. In need of
additional help one can use Excel’s help function, or by selecting a Visual Basic expression
with the mouse and press F1.
Sub create_simple_path(N As Integer, d() As Single, path() As Integer)
’This procedure is an example of a very simple heuristic for finding a path.
Dim i, tempused As Integer
Dim used(MaxNode) As Boolean
Dim temp As Single
For i = 1 To N
used(i) = False
Next i
path(1) = 1 ’Assuming node 1 is the start node
used(1) = True
path(2) = 1 ’The tour should end in node 1
For i = 1 To N - 1
temp = 100000
For j = 2 To N
If d(path(i), j) < temp And Not used(j) Then
temp = d(path(i), j)
tempused = j
End If
Next j
InsertElementInVector path, i + 1, tempused
used(tempused) = True
Next i
End Sub
Specifications for ”create cheapest path” and ”create farthest path”
The format of the output vector
In the procedure there is the vector ”path” which keeps track of the nodal sequence in
the TSP-route. The first element in this vector must always be node 1 and the second
element node 2 and so on. The last element however, is always node 1, which means that
the sequence always returns to node 1. This implies that the number of elements in the
vector is always one more than the number of nodes.
Computer lab info
TAOP24, Advanced course on optimization
Procedure InsertElementInVector
There is a procedure in the macro which includes a new node in an already existing vector.
This procedure has the arguments:
The vector to include the value in, Position in the vector to receive the value,
The value to insert.
Assume that five nodes have already been included in a sequence, the vector path has
the following composition: path = (1, 22, 2, 19, 6, 1, −, −, . . .). Assume that nextnode has
the value 9 and that intoposition has the value 4, then it follows that node 9 shall be
included between node 2 and 19 (that is, take position 4 in the new vector). The procedure
call is then:
InsertElementInVector path, intoposition, nextnode
After calling the procedures the vector will have the following composition:
path = (1, 22, 2, 9, 19, 6, 1, −, −, . . .).
Operations
Enter a sequence manually and calculate the total distance
In the sheet ”Problems” there is teh column ’Suggestion’. A sequence of nodes can in
this column can be entered manually. When one runs the macro the total distance is
calculated for such a sequence and the result will be put out in the row ’Distance’; column
’Suggestion’.
Running macros in Excel
Depending on whether you are in the Excel sheet or in the Visual Basic editor, one runs
macros a bit differently. If you are using the Excel sheet one runs the macros by choosing
’Tools → Macro → Macros’ in the menu and thereafter choosing the macro to be run (in
this computer exercise it is the macro ”main”) and then click Run. It is also possible to
do this faster by simply entering the command Ctrl-r. If you are using the Visual Basic
editor a macro in run by choosing ’Run → Run Sub/UserForm’ in the menu and thereafter
choosing which macro to be run (here the macro ”main”) and then click Run.
Opening the Visual Basic editor and writing code
In order to open the Visual Basic editor choose ’Tools → Macro → Visual Basic Editor’ in
the menu. When inside the editor another window opens to the left where one can choose
which macro modules are to be opened. The choosen (open) modules are shown in the
right window. In order to open a new module, choose the Excel arc, open Modules and
dubbleclick the right window.
To write new code or modify code, write the code in the window. Visual Basic checks
the fundamental syntax row by row, such that if you write any erroneous row Visual Basic
will give an error message. Commentaries can be written with usage of the sign ’ occurring
before the comment. It is very useful to make commentaries in the code, both to make it
easier for others and in this case the oral presentation. Use the tab-key to structure your
program nicely.