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 10 of 10

Rote calculation works fine using Excel VBA but doesn't optimize the route?

This is a discussion on Rote calculation works fine using Excel VBA but doesn't optimize the route? within the Development forums, part of the MapPoint Desktop Discussion category; Hi folks, I'm using Excel and Mappoint to calculate routes using the code below. Whilst it does calculate the driving ...

  1. #1
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Question Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Hi folks, I'm using Excel and Mappoint to calculate routes using the code below. Whilst it does calculate the driving distance it creates the route using the stops in the order they are presented to the calculation.

    According to this page:

    Optimize method

    There is a method to optimize the route - eg:
    objRoute.Waypoints.Optimize

    Can anyone suggest how to add the optimization of the route to the code below so that the mproutedist function returns a driving distance for an optimized route?

    Thanks! Scott.
    Current VBA code:

    Code:
    ===================================
    Function MPRouteDist(iMPType As Integer, ParamArray WPoints())
     
      Dim objApp As New MapPoint.Application
      Set objMap = objApp.ActiveMap
     
      With objMap.ActiveRoute
        For Each wpoint In WPoints
            .Waypoints.Add objMap.FindResults(wpoint).Item(1)
        Next
        .Waypoints.Item(1).SegmentPreferences = iMPType
        .Calculate
        MPRouteDist = Application.Round(CStr(.Distance), 5)
      End With
      
      
        objMap.Saved = True
    End Function
    ====================================
    Last edited by Eric Frost; 10-14-2010 at 10:35 AM.

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

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Right after the .Calculate put in a
    Code:
                 .Optimize
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Exclamation Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Quote Originally Posted by Eric Frost View Post
    Right after the .Calculate put in a
    Code:
                 .Optimize
    Thanks, that was how I tried to do it first of all but it doesn't seem to work in that context, it gives a VALUE error in Excel and asks to save the map which it doesn't usually becuase the code tells it to save it.

    I think the problem is that the code needs to compile the waypoint list, calculate the route, optimize it then calculate it again. No idea how to do that in VBA though!

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

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Oh I'm sorry. Yeah, right before .Calculate, do the .Optimize. The Optimize needs to be before the Calculate.

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  5. #5
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Yep I've tried that too, gives me the same behaviour. Unfortunatly I'm not skilled enough at VBA to fully understand the object and method information on the page that I linked to. I'll keep at it...

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

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Are you sure the ParamArray WPoints() is being passed in correctly?

    Maybe if you can post a little Excel file with an example I can troubleshoot.

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  7. #7
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Here you go, it's working with the code in my original post but I can't get it to 'Optimize'.

    Note that we're using Mappoint 2010 Europe (I'm in England) and I havn't got access to the American version so I can't set this up to work with USA in advance of sending it to you...

    Many thanks in advance!

    Scott.
    Attached Files Attached Files

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

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    I played with it a bit. One mistake I was telling you is that the .Optimize is actually a property of the Waypoints collection.

    I changed the code a little and added a separate function for the Optimized route. See below.

    Let me know if you have any questions.

    One comment I would make is if you are doing a lot of these, it would be a lot faster not to instantiate MapPoint each time, but have objApp as a module level variable, and then you'd have to add some code to try to attach to an existing running instance of MapPoint or if it doesn't find one, then it would instantiate a new one.

    Code:
    Function MPRouteDist(iMPType As Integer, ParamArray WPoints())
     
    On Error GoTo TheEnd:
     
      Dim objApp As New MapPoint.Application
      Set objMap = objApp.ActiveMap
     
      With objMap.ActiveRoute
        For Each wpoint In WPoints
            .Waypoints.Add objMap.FindResults(wpoint).Item(1)
        Next
        .Waypoints.Item(1).SegmentPreferences = iMPType
        '.Waypoints.Optimize
        .Calculate
        MPRouteDist = Application.Round(CStr(.Distance), 5)
      End With
      
    TheEnd:
      objMap.Saved = True
      Set objApp = Nothing
      Set objMap = Nothing
        
    End Function
    
    
    Function MPRouteDistOpt(iMPType As Integer, ParamArray WPoints())
     
    On Error GoTo TheEnd:
     
      Dim objApp As New MapPoint.Application
      Set objMap = objApp.ActiveMap
     
      With objMap.ActiveRoute
        For Each wpoint In WPoints
            .Waypoints.Add objMap.FindResults(wpoint).Item(1)
        Next
        .Waypoints.Item(1).SegmentPreferences = iMPType
        .Waypoints.Optimize
        .Calculate
        MPRouteDistOpt = Application.Round(CStr(.Distance), 5)
      End With
      
    TheEnd:
      objMap.Saved = True
      Set objApp = Nothing
      Set objMap = Nothing
        
    End Function

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  9. #9
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    Eric you're a genius! Thanks so much for your help.

    I'm going to try and modify the code to use a single MapPoint instance, and also get it to save the map for each route using variables as the file name. Will post the new code on here when it's done.

    Cheers, Scott.

  10. #10
    Scotbot is offline Junior Member White Belt
    Join Date
    Oct 2010
    Posts
    6

    Lightbulb Re: Rote calculation works fine using Excel VBA but doesn't optimize the route?

    I've made a small change to the code so that it will ignore blank cells that are specified as waypoints. For example if you have several journeys each on a row and they have different numbers of postcodes and therefore some rows have blank cells in some columns.

    This means you can specify the same columns for the waypoints in the formula on every row and will still work becuase it skips the additon of the cell as a waypoint if the cell is blank.

    ==============

    Code:
    Function MPRouteDistOptBlank(iMPType As Integer, ParamArray WPoints())
     
    On Error GoTo TheEnd:
     
      Dim objApp As New MapPoint.Application
      Set objMap = objApp.ActiveMap
     
      With objMap.ActiveRoute
        For Each wpoint In WPoints
            If IsEmpty(wpoint) = False Then .Waypoints.Add objMap.FindResults(wpoint).Item(1)
            
        Next
        .Waypoints.Item(1).SegmentPreferences = iMPType
        .Waypoints.Optimize
        .Calculate
        MPRouteDistOptBlank = Application.Round(CStr(.Distance), 5)
      End With
      
    TheEnd:
      objMap.Saved = True
      Set objApp = Nothing
      Set objMap = Nothing
        
    End Function
    Last edited by Eric Frost; 11-04-2010 at 12:30 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Route calculation problem
    By app_2_win in forum Development
    Replies: 2
    Last Post: 03-19-2009, 11:40 PM
  2. Mappoint add-in doesn't works fine
    By soso78 in forum Development
    Replies: 5
    Last Post: 02-06-2009, 05:50 AM
  3. Optimizing Route Calculation
    By jper043 in forum Development
    Replies: 4
    Last Post: 04-01-2008, 11:14 AM
  4. How to handle route calculation error
    By rberick in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 12-17-2003, 08:12 PM
  5. Export to Excel works from 'closed shapes'. Is i....
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 03-04-2002, 10:54 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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127