Trip Generation Modeling�Cross-Classification

							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
• Selecting a spreadsheet range
• 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
  tasks
• Create outline (or flow chart) of
  instructions for each task




9/14/2012           Michael Dixon        9
            Pseudo Code Example
• Problem: Trip production generation using
  cost-bin
• Independent tasks:
     – 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
  address) or filename
     – 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
     – Read in data to your arrays
• 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