Hey folks,

So the online knowledge base articles I've come across don't quite fit my scenario... Perhaps someone else has the same dilemma, or knwos how to remedy it.

I've written a function in Excel's VBA.
The purpose is to return the distance between two lat/long points. (If that point isn't near a road, the code refers to a straightline distance calculation that I've adapted named: "Posdist"...

When I fill my excel worksheet with multiple copies of this function, and have inputs pointing to appropriate cells, with latitude and longitude, after a certain time I get error messages... The kicker is it works when I calculate one cell at a time, but when I recalculate the whole sheet I get errors...

They read:
This application is unable to continue running due to an internal error (6-40028--1073741819)
Error signature
AppName: mappoint.exe
Appvers:13.0.15.2800
Modname:ntdll.dll
mod version:5.1.2600.2180
Offset: 00010f29

I tried uninstalling the network HP printer we have in the office, and unplugging the network cable, and then got a slightly different error:
with details:
Modname:mfc71.dll

Here's my snippet of code, for your perusal:
hopefully someone has a clue as to my next appropriate step of actio!

Thnx in advance,
andriy

Code:
--- the PCFLAG input, is binary, and identifies isolated postal codes, so teh algorithm directly calculates distance based on straightline distance)

Public Function RoadDistance(PCFlag, 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

If PCFlag = 1 Then
GoTo Straight
End If

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

On Error GoTo IsolatedPC
SysRoute.Calculate

RoadDistance = SysRoute.DrivingTime
SysRoute.Clear
SysMap.Saved = True
GoTo Ending

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

End Function


any ideas?
anyone?

Doesn't anybody else use mappoint to calculate distances between ZipCodes for an entire dataset of locations?

Do you folks usually do this in C++? Is my problem that I'm doing it in VBA?

thanks for your input...

andriy

<cheeky answer>

I sell my own products that do this.

The one that works from database (or Excel) route listings uses C#.

Richard

__________________
Winwaed Software Technology LLC
http://www.winwaed.com
See http://www.mapping-tools.com for MapPoint Tools
See the Geoweb Guru for online mapping