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

vba calculating postcode distances

This is a discussion on vba calculating postcode distances within the MapPoint Desktop Discussion forums, part of the Map Forums category; hey guys, sorry if this has been asked many times, i've searched the forums but it seems that i'm stupid ...

  1. #1
    pwaddy is offline Junior Member White Belt
    Join Date
    Nov 2010
    Posts
    1

    vba calculating postcode distances

    hey guys,

    sorry if this has been asked many times, i've searched the forums but it seems that i'm stupid in vba.

    I've got a long list of postcodes that I want to find the distances for. So starting in column A (row3) I have a list of starting postcodes, and column C (row3) the ending postcodes. So far the distances are calculated and put into column D.

    My problem is this:
    1. when it reaches a blank cell, it's suppose to stop calculating, but it's just keeps on calculating (i think this is due to my 'On Error Resume Next')
    2. I dont need to see mappoint, but i'm not sure how to hide it...it's telling me if i want to save when i stop the macro.

    Here is the code, any help on optimising it would be grateful!:

    Sub find_distances()
    Dim nCurrentRow As Integer
    nCurrentRow = 3

    szPostCode = Cells(nCurrentRow, 3)

    Set oApp = CreateObject("MapPoint.Application.eu.11")
    oApp.Visible = False
    Set objMap = oApp.NewMap
    Set objRoute = objMap.ActiveRoute

    Do While (szPostCode <> " ") 'stop when it reaches a blank line.

    On Error Resume Next

    szZip1 = Worksheets("Sheet1").Cells(nCurrentRow, 1)
    szZip2 = Worksheets("Sheet1").Cells(nCurrentRow, 3)

    '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(nCurrentRow, 4) = objRoute.Distance

    nCurrentRow = nCurrentRow + 1
    objRoute.Clear


    Loop

    objMap.Saved = False
    objMap.Quit
    End Sub

  2. #2
    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: vba calculating postcode distances

    For #1: Yes.
    On Error Resume Next is considered very bad practice. Use an On Error Goto for now. Then print out any error messages you get with a Message Box and the Error object's message property.
    Check for an empty cell by calling Trim() on the two szZip variables (to remove any whitespace), and then check to see if they are empty (Len() = 0).


    For #2: The application object has a visibility property: As you are using it.
    The saved question is because you have Saved=false at the end. Change this to objMap.Saved = True This makes MapPoint think the map has been saved (it hasn't) so it doesn't think there are any changes that need to be saved.
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Postcode Distances
    By PaulB82 in forum MapPoint Desktop Discussion
    Replies: 16
    Last Post: 04-12-2012, 06:08 AM
  2. How to disable ferries in calculating driving distances
    By taquito2007 in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 12-08-2008, 11:19 AM
  3. Calculating distances from a route
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 07-18-2003, 11:23 AM
  4. Calculating Distances For Dummies
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 12-20-2002, 05:26 PM
  5. Trouble calculating distances
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 10-03-2002, 11:15 AM

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