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

Hi,

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

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

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.

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

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

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 ?

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