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
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 40

Mappoint macro getting error on calculating distance

This is a discussion on Mappoint macro getting error on calculating distance within the MapPoint Desktop Discussion forums, part of the Map Forums category; I am using the following code for calculating distance between the zips. It works great but if there is bad ...

  1. #1
    shahdelsol is offline Member Yellow Belt
    Join Date
    Sep 2008
    Posts
    44

    Mappoint macro getting error on calculating distance

    I am using the following code for calculating distance between the zips. It works great but if there is bad zipcode or new zipcode in one cell that mappoint cannot find, the mappoint and macro stops with this error: " Run-time error- 22.... the requested member of the collection doesnot exist. Use a valid name or index number" and then it gives the option of: End or Debug which is not helpful. I want a macro instead to do this for me: if there is wrong zipcode, an pop up would give me an option to correct zip code in which cell or ignore and continue to calculate the rest of zip codes and this is the code I am using:

    Code:
    Private Sub CommandButton1_Click()
    Set oApp = CreateObject("MapPoint.Application.NA.16")
     oApp.Visible = True
     Set objMap = oApp.NewMap
     Set objRoute = objMap.ActiveRoute
     szZip1 = Worksheets("Sheet1").Cells(1, 3)
     NRow = 3
     Do
         szZip2 = Worksheets("Sheet1").Cells(NRow, 1)
         'Add route stops and calculate the route
         objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
         objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
         objRoute.Calculate
         Worksheets("Sheet1").Cells(NRow, 3) = objRoute.Distance
         objRoute.Clear
         NRow = NRow + 1
     Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""
    objMap.Saved = True
    
    End Sub
    Last edited by Eric Frost; 08-20-2009 at 09:55 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: Mappoint macro getting error on calculating distance

    Coding is not that hard, do you think you might be in the wrong profession? Sorry I just see you asking lots of simple questions. Not that you're not welcome on this site, but maybe your organization should get a programmer.

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

  3. #3
    shahdelsol is offline Member Yellow Belt
    Join Date
    Sep 2008
    Posts
    44

    Re: Mappoint macro getting error on calculating distance

    Quote Originally Posted by Eric Frost View Post
    Coding is not that hard, do you think you might be in the wrong profession? Sorry I just see you asking lots of simple questions. Not that you're not welcome on this site, but maybe your organization should get a programmer.

    Eric
    I am learning and I am just doing this as hobby... nothing serious. You don't have to reply but this question might be also helpful for others too.

  4. #4
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Mappoint macro getting error on calculating distance

    Check if it is empty or cannot be found.

    Also trap exceptions. You can use "On Error" to do this, but a decent language (eg. C++ or C#) has much better support for exception handling.


    Richard
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  5. #5
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: Mappoint macro getting error on calculating distance

    Hi,

    As Richard mentioned: 'on error'. I think it has to be on top of you button click event. Somethign like this (but I don't know VBA so there may be syntax errors here):

    Code:
    On Error goto ErrHandler;
       // here your code
    ErrHandler:
       ShowMessage('Zip code does not exists or is incorrect')
    I think this is more or less the same as an exception block as Richard already mentioned.

  6. #6
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Mappoint macro getting error on calculating distance

    Yes it is the closest thing in VB6 or VBA. It is also possible to query some of the error information, but I find the whole mechanism a bit clunky for decent programming purposes.
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  7. #7
    shahdelsol is offline Member Yellow Belt
    Join Date
    Sep 2008
    Posts
    44

    Re: Mappoint macro getting error on calculating distance

    Quote Originally Posted by Wilfried View Post
    Hi,

    As Richard mentioned: 'on error'. I think it has to be on top of you button click event. Somethign like this (but I don't know VBA so there may be syntax errors here):

    Code:
    On Error goto ErrHandler;
       // here your code
    ErrHandler:
       ShowMessage('Zip code does not exists or is incorrect')
    I think this is more or less the same as an exception block as Richard already mentioned.
    Thanks for your help I have been trying to use your code but I was not sure where exactly to insert your code. I tried several different ways but none worked perhaps you could tell me how to use it.
    Last edited by shahdelsol; 08-20-2009 at 09:46 AM.

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

    Re: Mappoint macro getting error on calculating distance

    Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

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

  9. #9
    shahdelsol is offline Member Yellow Belt
    Join Date
    Sep 2008
    Posts
    44

    Re: Mappoint macro getting error on calculating distance

    Quote Originally Posted by Eric Frost View Post
    Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

    Eric
    Zip: Mile:
    98001980023.318122980033.1689939800424.482039800526.060319800624.363969800724.034649800826.091389800924.395039801020.026799801135.318749801239.481939801326.2849801442.787629801523.52511
    Is this what you want?

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

    Re: Mappoint macro getting error on calculating distance

    I worked it out.. attached is a spreadsheet with some zip code data.

    In the code below, I use a FindResults object and then there's an If statement that tests whether there are any results before it tries to add the Waypoint and Calculate the route. It just skips over if it couldn't find the zip code.

    Code:
    Private Sub CommandButton1_Click()
    Set oApp = CreateObject("MapPoint.Application.NA.16")
     oApp.Visible = True
     Set objMap = oApp.NewMap
     Set objRoute = objMap.ActiveRoute
     szZip1 = Worksheets("Sheet1").Cells(1, 3)
     NRow = 3
     
     Dim objFindResults As MapPoint.Findresults
    
     Do
         szZip2 = Worksheets("Sheet1").Cells(NRow, 1)
         'Add route stops and calculate the route
         objRoute.Waypoints.Add objMap.Findresults(szZip1).Item(1)
         Set objFindResults = objMap.Findresults(szZip2)
         If objFindResults.Count > 0 Then
           objRoute.Waypoints.Add objMap.Findresults(szZip2).Item(1)
           objRoute.Calculate
           Worksheets("Sheet1").Cells(NRow, 3) = objRoute.Distance
           objRoute.Clear
         End If
         NRow = NRow + 1
     Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""
    objMap.Saved = True
    
    End Sub
    I think simply inserting a line
    Code:
    On Error Resume Next
    at the top would have worked, too.

    hope this helps!
    Eric
    Attached Images Attached Images
    Attached Files Attached Files
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Page 1 of 4 123 ... LastLast

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. macro for calculating from one zip code to multiple zipcodes
    By shahdelsol in forum Introduce Yourself
    Replies: 11
    Last Post: 07-27-2010, 01:15 AM
  2. Calculating Distance between two sets of data
    By daphtci in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 09-10-2008, 01:00 AM
  3. Excel to Mappoint Macro
    By Paddy1979 in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 06-12-2008, 09:53 AM
  4. Newbie needs help calculating distance
    By Tom S. in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 11-17-2005, 07:26 AM
  5. Calculating distance without direction-list
    By rbarthels in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 05-13-2003, 09:57 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