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

On Error Help - VBA

This is a discussion on On Error Help - VBA within the Development forums, part of the MapPoint Desktop Discussion category; ok, so I'm writing VBA code for an excel spreadsheet and Mappoint 2004, that returns the road distance between two ...

  1. #1
    andriy is offline Junior Member Yellow Belt
    Join Date
    Mar 2007
    Posts
    16

    On Error Help - VBA

    ok, so I'm writing VBA code for an excel spreadsheet and Mappoint 2004, that returns the road distance between two locations, using their Lat and Long...

    The trouble is when I get to an isolated Postal Code (or Zip Code) that doesn't have road access connecting the mainland I get some error (can't even identify it) and Mappoint shuts down, the excel macro suts down, leaving me "up the creek"...

    I've attached the code, and appreciate any help or hints... (My next step will be to identify manually which Zip Codes are isolated, and then have a seperate "straightline distance calculator" return a distance for the isolated location...

    Here's the code:
    >>>>>>>>>>>>>>>>>>>

    Public Function RoadDistance(Lat1, Long1, Lat2, Long2)

    Dim SysApp As New MapPoint.Application
    Dim SysMap As MapPoint.Map
    Dim SysRoute As MapPoint.Route
    Dim SysLoc1 As MapPoint.Location
    Dim SysLoc2 As MapPoint.Location


    Application.ActivateMicrosoftApp xlMicrosoftMappointNorthAmerica

    Application.DisplayAlerts = False

    'SysApp.OpenMap (AKPath & "\LHIN.ptm")
    Set SysMap = SysApp.ActiveMap
    Set SysRoute = SysMap.ActiveRoute

    SysApp.Units = 1

    Set SysLoc1 = SysMap.GetLocation(Lat1, Long1)
    Set SysLoc2 = SysMap.GetLocation(Lat2, Long2)

    SysRoute.Waypoints.Add SysLoc1
    SysRoute.Waypoints.Add SysLoc2

    SysRoute.Calculate
    On Error GoTo IsolatedPC

    'Sheets("Maps").Select
    RoadDistance = SysRoute.Distance
    SysRoute.Clear
    SysMap.Saved = True
    GoTo Ending

    IsolatedPC:
    SysRoute.Clear
    SysMap.Saved = True
    RoadDistance = Posdist(Lat1, Long1, Lat2, Long2)
    Ending:
    End Function

    >>>>>>>>>>>

    any thoughts?
    cheers,
    andriy
    Last edited by andriy; 10-04-2007 at 12:25 PM. Reason: remove unneccesary personal comments

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

    Re: On Error Help - VBA

    Hi,

    the line of code where you open the map is put in comment.

  3. #3
    andriy is offline Junior Member Yellow Belt
    Join Date
    Mar 2007
    Posts
    16

    Re: On Error Help - VBA

    That's because I don't want to open a map, just the application, so that it calculates a distance for me... I should have removed that...

    (Please, correct me if I'm wrong)

    It runs fine, when you have road accessible zip codes, but trying to measure the road distance from Hawai to LA, returns an error... an obvious one, at that, but how to put it into VBA Mappoint language?
    Are there error numbers in mappoint, just like in excel?

    thanks,
    andriy

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

    Re: On Error Help - VBA

    Hi,

    If you uncomment the line then same problem ?

    To debug you can step with the debugger trough the code to find the offending line. Also you can put the whole thing in an exception block, then probably more error information.

    How to do both I don't know, I don't know VBA, but sure others can jump in here.

  5. #5
    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: On Error Help - VBA

    Yep MapPoint throws an exception if it cannot find a route - eg. locations on different islands, poor farm roads, etc.

    You need to trap the exception and "do something appropriate" (eg. ignore it, write a " " for the distance, or whatever is appropriate for you)

    Use "ON ERROR" to trap exceptions in VB6.


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

  6. #6
    andriy is offline Junior Member Yellow Belt
    Join Date
    Mar 2007
    Posts
    16

    Re: On Error Help - VBA

    For those that want to test this out on your computers:

    open up excel, pop in the following into the Excel's VB Editor...

    >>>>>>>>>>>>>>>>>>>

    Public Function RoadDistance(Lat1, Long1, Lat2, Long2)

    Dim SysApp As New MapPoint.Application
    Dim SysMap As MapPoint.Map
    Dim SysRoute As MapPoint.Route
    Dim SysLoc1 As MapPoint.Location
    Dim SysLoc2 As MapPoint.Location

    Application.ActivateMicrosoftApp xlMicrosoftMappointNorthAmerica

    Application.DisplayAlerts = False

    Set SysMap = SysApp.ActiveMap
    Set SysRoute = SysMap.ActiveRoute
    SysApp.Units = 1

    Set SysLoc1 = SysMap.GetLocation(Lat1, Long1)
    Set SysLoc2 = SysMap.GetLocation(Lat2, Long2)

    SysRoute.Waypoints.Add SysLoc1
    SysRoute.Waypoints.Add SysLoc2

    SysRoute.Calculate
    On Error GoTo IsolatedPC

    'Sheets("Maps").Select
    RoadDistance = SysRoute.Distance
    SysRoute.Clear
    SysMap.Saved = True
    GoTo Ending

    IsolatedPC:
    SysRoute.Clear
    SysMap.Saved = True
    RoadDistance = Posdist(Lat1, Long1, Lat2, Long2)
    Ending:
    End Function

    >>>>>>>>>>>>>>>>>>>

    make sure to check your references in the VB Editor, and click on Mappoint Library...

    now on a sheet in A1, enter 53.62
    A2 = -88.68
    B1 = 46.53
    B2 = -84.36

    C1 = RoadDistance(A1, A2, B1, B2)
    and see what happens...

    hope this helps...

    andriy

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

    Re: On Error Help - VBA

    Hi,

    Can you also tell what happens ? (not everyone has Excel or know how to use the VB thing)

    Did you try the exception block as Richard proposed ?

  8. #8
    andriy is offline Junior Member Yellow Belt
    Join Date
    Mar 2007
    Posts
    16

    Re: On Error Help - VBA

    so I made this minor correction, and have the On error statement before the questionable piece of code... yay the program runs...

    so for those following the code on your own computers, it should read:

    ...
    SysRoute.Waypoints.Add SysLoc2

    On Error GoTo IsolatedPC
    SysRoute.Calculate

    RoadDistance = SysRoute.Distance
    SysRoute.Clear
    ...

    thanks for your help

    I still think Mappoint shoud do this automatically, or have a flight option built in, where you take an alternative means of transport, for as short of time as possible... then travel by roads...

    regards,
    andriy

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run-time error 2147467259 80004005 automation error
    By ThomasB in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 02-27-2007, 12:41 PM
  2. Error using MP 2006
    By PC7395 in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 08-28-2006, 05:43 PM
  3. About ERROR 4-40028-1. Error or Bug ?
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 12
    Last Post: 01-11-2005, 10:32 AM
  4. I get this following error
    By ananthdeena in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 01-01-2005, 03:46 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