An Efficient VBA Spreadsheet Algorithm and Model for the System Optimum Traffic Assignment

An Efficient VBA Spreadsheet Algorithm and Model for the System Optimum Traffic Assignment

Jae-Dong Hong (Industrial Engineering Technology, South Carolina State University, Orangeburg, SC, USA), Yuanchang Xie (Civil & Environmental Engineering, University of Massachusetts at Lowell, Lowell, MA, USA) and Ki-Young Jeong (Department of Engineering Management, University of Houston at Clear Lake, Houston, TX, USA)
Copyright: © 2012 |Pages: 17
DOI: 10.4018/ijaie.2012070104


The traffic assignment involves assigning traffic to a transportation network consisting of highways, arterials, or transit routes. To obtain an optimal traffic assignment scheme, a mathematical program (MP) should be formulated and solved. The process of formulating an MP requires identifying all possible paths connecting each origin-to-destination pair through the network, since the number of those paths turns out to be the number of the decision variables. Consequently, formulating and solving such an MP is difficult due to the large number of the decision variables and constraints. For that reason, practitioners prefer heuristics for a large-scale transportation network. In this paper, the authors suggest a new and efficient way of formulating an MP and develop a Microsoft Excel model with Visual Basic for Applications (VBA) to find the optimal assignment scheme for the traffic assignment problem. The developed Excel model with VBA can be easily expanded to a large-scale transportation network.
Article Preview


In this paper, we consider the traffic assignment problem of assigning traffic to a transportation network. The objective is to minimize total system travel time, subject to satisfying the traffic demand between origin to destination pairs. The problem has traditionally been formulated as a nonlinear integer programming problem with a large number of decision variables and constraints, which makes it difficult to formulate and solve.

The main objective of this study is to propose a simplified two-phase method of formulating the problem and develop an algorithm using Microsoft Excel model with VBA code to solve the problem with much less effort. The secondary objective is to provide insights to practitioners to show how the Microsoft Excel with VBA could be successfully used in a transportation network model instead of complex mathematical optimization tools such as CPLEX (IBM, 2010) and LINDO ( For these purposes, we illustrated the efficiency of our approach using a simple Microsoft Excel example with VBA code.

Complete Article List

Search this Journal:
Open Access Articles: Forthcoming
Volume 6: 2 Issues (2019): Forthcoming, Available for Pre-Order
Volume 5: 2 Issues (2018)
Volume 4: 2 Issues (2017)
Volume 3: 2 Issues (2016)
Volume 2: 2 Issues (2014)
Volume 1: 2 Issues (2012)
View Complete Journal Contents Listing