Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 5 of 5

building an optimal route using VBA

This is a discussion on building an optimal route using VBA within the MapPoint Desktop Discussion forums, part of the Map Forums category; I'm building an Excel model which has a VBA module which needs to read in a set of locations (lat/long ...

  1. #1
    maddog 2 is offline Junior Member White Belt
    Join Date
    Jan 2004
    Posts
    5

    building an optimal route using VBA

    I'm building an Excel model which has a VBA module which needs to read in a set of locations (lat/long data) from Excel and find an optimal route around the points - as if you were using the "optimize stops" button in MapPoint.

    Could anyone help me with the code?

    I know how to add in individual pushpins using VBA code but not a large set of points (say 150) to add to the route.

    Version is MP2002 Europe

  2. #2
    maddog 2 is offline Junior Member White Belt
    Join Date
    Jan 2004
    Posts
    5

    Re: building an optimal route using VBA

    okay, I've sussed out how to read the points in

    now I just need to optimise the stops

    my current line of code is: (oRoute is my MapPoint.Route object)

    oRoute.Calculate

    but I need something which will not just find the route as they were inputted but the optimal order.

  3. #3
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: building an optimal route using VBA

    Maybe it's a little non-intuitive, but the Optimize method is a method of the WayPoints collection, not the Route like you might think.

    Check out the help file

    WayPoints is a property of the Route object.

    WayPoints itself is a Collection.

    WayPoints has the Optimize method and an IsOptimized property.

    Here is the description and code sample for the Optimize method from the Help file (actually copied from the MapPoint 2004 help file so it might be a little different from yours).

    Optimize method

    Reorders the intermediate stops on a route so that the travel time between the start and end points is the most efficient; similar to clicking Optimize Stops on the Route menu.

    Applies to
    Collections: Waypoints

    Syntax
    object.Optimize

    Part Description
    object Required. An expression that returns a Waypoints collection.

    Remarks

    The RouteAfterOptimize event of the Map or MappointControl object occurs after the stops on the route have been optimized.

    After stops are optimized, the route no longer exists on the map. Be sure to use the Calculate method on the Route object.

    For more information about optimizing the stops on a route, see the Optimize the stops on your route procedural topic.

    Example

    Code:
     
      Sub OptimizeRoute()
      Dim objApp As New MapPoint.Application
      Dim objMap As MapPoint.Map
      Dim objRoute As MapPoint.Route
      'Set up the application
      Set objMap = objApp.ActiveMap
      Set objRoute = objMap.ActiveRoute
      objApp.Visible = True
      objApp.UserControl = True
      'Add route stops and calculate route
      With objRoute.Waypoints
        .Add objMap.FindResults("Seattle, WA").Item(1)
        .Add objMap.FindResults("Redmond, WA").Item(1)
        .Add objMap.FindResults("Tacoma, WA").Item(1)
        .Add objMap.FindResults("Bellevue, WA").Item(1)
      End With
      objRoute.Calculate
      'Optimize the route
      objRoute.Waypoints.Optimize
      End
    SubNote This sample code is specific for use in MapPoint North America; it is for illustration purposes only.

  4. #4
    maddog 2 is offline Junior Member White Belt
    Join Date
    Jan 2004
    Posts
    5

    Re: building an optimal route using VBA

    thanks for that Eric. I'll give it a go

    I actually tried

    oRoute.Waypoints.Optimize = true

    but it spat that out

  5. #5
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: building an optimal route using VBA

    It's a method with no parameters, I don't think you need to say anything, just do

    .Optimize

    not

    .Optimize = Tue

    best,
    Eric

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Building a GIS using mappoint technology
    By vishwadev in forum Bing Maps and MapPoint Web Service
    Replies: 10
    Last Post: 06-06-2007, 02:06 PM
  2. MapPoint Road building tool
    By larrya in forum Wish List
    Replies: 1
    Last Post: 04-03-2005, 10:46 PM
  3. How do you map optimal route for multiple pick up addresses?
    By mprdj in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 11-15-2004, 02:30 PM
  4. Is the Optimal stop Function optimal?????
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 06-13-2004, 11:34 AM
  5. How do we add our building (Shape) data to MapPoi....
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 08-06-2001, 10:32 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


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