# Trip Generation Modeling�Cross-Classification

Shared by:
Categories
Tags
-
Stats
views:
15
posted:
9/14/2012
language:
Latin
pages:
20
Document Sample

```							Visual Basic for Applications:
Excel
CE 573 Transportation Planning
Lecture 3
Objectives
•   Beginning a macro
•   Declare variables
•   If-then-else statements
•   For-Next loop
•   Able to organize solution to problem
•   Retrieving and outputting arrays
•   Debugging

9/14/2012            Michael Dixon         2
Starting a Macro in Excel
• Turn on Visual Basic toolbar

• Open the Visual Basic Editor

• Insert a programming module

9/14/2012               Michael Dixon   3
Starting a Macro in Excel

Your cursor is now in the Module View…and you are ready to program

9/14/2012                       Michael Dixon                            4
Starting a Macro in Excel (cont.)
• Starting a sub procedure
• Declaring a variable
• Activating workbooks and
worksheets
• Populating a variable

9/14/2012           Michael Dixon   5
Starting a Macro in Excel (cont.)

9/14/2012     Michael Dixon     6
Using an IF-THEN-ELSE statement

9/14/2012    Michael Dixon    7
Using a FOR-NEXT loop

9/14/2012           Michael Dixon   8
Pseudo Code Concepts
• Identify the problem
• Break solution down into independent
• Create outline (or flow chart) of

9/14/2012           Michael Dixon        9
Pseudo Code Example
• Problem: Trip production generation using
cost-bin
– Establish size of problem
– Determine where to get input data
– Get input data
– Do calculations
– Output results

9/14/2012              Michael Dixon       10
Pseudo Code Outline Example
(cont.)
• Establish size of problem
– Number of zones
– Number of income levels
– Number of auto ownership levels
• Where to get input data: (spreadsheet, worksheet, cell
– Zone households: Spreadsheet (Homework 2), input_data, “I4”
– Autos per household: Spreadsheet (Homework 2), input_data,
“B4”
– Trip rates: Spreadsheet (Homework 2), input_data, “B14”
– Trip purpose percentages: Spreadsheet (Homework 2),
input_data, “F14”

9/14/2012                    Michael Dixon                           11
Pseudo Code Outline Example
(cont.)
• Get input data
– Select location
• Do calculations (say we have read in all of the input
data)
– Calculate trips generated (productions for each of the three trip
purposes)
• Loop over each zone
– Loop over income levels
» Loop over auto levels
» Loop over trip purposes
» {equation calculating trip production}
» End loop
» End loop
– End loop
• End loop

9/14/2012                              Michael Dixon                       12
Pseudo Code Outline Example
(cont.)
• Output results
– Go to the output location
– Output trip productions for each zone by trip
purpose
• Loop over zones
– Loop over trip purposes
» Output trip production value
– End loop
• End loop
• end
9/14/2012                        Michael Dixon         13
Outputting Arrays

9/14/2012         Michael Dixon   14
Hot Keys and Debugging
•   Run program  F5
•   Run to curson  Ctrl + F8
•   Quick watch  Shft + F9
•   Toggle breakpoint  F9
•   Debug often
•   Watch variable window

9/14/2012            Michael Dixon   15
Assignment

9/14/2012      Michael Dixon   16
Appendix

9/14/2012     Michael Dixon   17
Retrieving and Outputting Arrays
• Get input data (example using variant)
– Productions
Dim P as variant

Sub get_data()
Range(“N4:N8”).select
P = selection.value
End sub

9/14/2012                          Michael Dixon   18
Retrieving and Outputting Arrays
• Get input data (example using loop)
– Productions
Dim i, number_zones as Integer
Dim P(5) as Double

Sub get_data()
Range(“N4”).select
For i = 1 to number_zones
P(i) = activecell.offset(i, 0)
Next
End sub

9/14/2012                           Michael Dixon         19
Retrieving and Outputting Arrays
• Get input data (example using variant)
– Productions
Dim P As Variant
Dim r, c As Integer

Sub get_data()
Range("N4").Select
P = Selection.Resize(Number_zones, Number_zones)
End Sub

9/14/2012                      Michael Dixon                    20

```
Related docs
Other docs by HC120915012021
Invoice
EastChicago MidyearEvaluation pie charts
Excel Staffing Services - DOC