MapPoint mit VBA nutzen

Um die Routenplanung für ca. 170 Personenadressen zu ca. 120 Ortsadressen automatisiert zu nutzen, habe ich Microsoft MapPoint 2004 Europe mit dem Objektmodell genutzt, um für Excel 2003 ein Makro mittels VBA (Visual Basic for Application) zu schreiben, welches die Berechnung vornimmt.
Die vollständige Berechnung aller Routenentfernungen dauert derzeit ca. 36h; ich bin also für jegliche Vorschläge zur Optimierung dankbar!
Das Ziel: für alle Personenadressen muss jeweils die Entfernung zu jeder Ortsadresse für die schnellste Route gemessen werden.
Zur technischen Realisierung: die Grundlage ist die Datenbasis aller notwendigen Adressen in Excel. Dabei habe ich für jede Adresse Straße,PLZ, Ort und Land in separaten Spalten. Da nicht immer alle Adresse gefunden werden, habe ich also zuerst die Personen- und Ortsadressen alle geprüft. Die nicht gefundenen Adressen lasse ich eine Fehlerliste eintragen, um danach schnell und einfach Zugriff darauf zu haben und die nicht gefundenene Adressen zu korrigieren bzw. eine "passende" Adresse einzutragen. Bei den Ortsangaben hatte ich Glück, bei wenigen Ausnahmen konnte durch Weglassen von Hausnummernteilen (z.B. "8" statt "8c") oder Hinzufügen einer fiktiven Hausnummer ("1") die Adresse immer gefunden werden. Die Adressen ohne Hausnummern sind meist Gebäude, die als einzige in der Straße stehen und daher wohl über keine Hausnummer verfügen oder die Hausnummer nicht bekannt oder interessant ist. Bei den nicht gefundenen Personenadressen habe ich Rücksprache mit den jeweiligen Personen gehalten, um nicht einfach irgendeine Adresse zu nehmen, sondern eine Adresse, die so nah an der echten Wohnadresse ist, dass die Abweichung bei der Entfernungsberechnug vernachlässigbar klein wird. Ganz wichtig: bei den eher ostlastigen Postleitzahlen darauf achten, dass die Zelle als Text formatiert ist, damit eine führende Null auch wirklich angezeigt und in der nachfolgenden Stringbehandlung übernommen wird. Ein Formatieren der Zellen auf 5-stellige Zahlen mit aufzufüllenden Nullen ohne echte "0" am Anfang hilft da nicht weiter!
Zunächste definiere ich die globalen Variablen (nur Auszug dargestellt) und erstelle die notwendige Anwendung .

'Initialisieren aller Variablen für MapPoint
Dim oMpApp As MapPoint.Application
Dim oMap As MapPoint.Map
Dim oRoute As MapPoint.Route
Dim oLocReferee As MapPoint.Location
Dim oLocGym As MapPoint.Location

'Objekte für die MapPoint-Anwendung
Set oMpApp = GetObject(, "MapPoint.Application")
Set oMap = oMpApp.ActiveMap
Set oRoute = oMap.ActiveRoute

Im Anschluss daran gehe ich mittels Schleifen über den Adressbereich. Da die Personenadressen vollständig in einem anderen Tabellenblatt stehen, habe ich auch gleich mal ein paar Funktionen (hier SVERWEIS) genutzt, um anhand eines Primärschlüssels die benötigten Adressen zu nutzen.

'oLocReferee/oLocGym enthält die jeweiligen Adressen, zusammengesetzt aus Zellwerten, die mittels SVERWEIS (=VLOOKUP) abgefragt wurden
RefereeStreet = WorksheetFunction.VLookup(Cells(1, currentColumn), srRange, RefereeStreetColumn, False)

'genauso für RefereeCity, RefereeZip und RefereeState
RefereeAddress = RefereeStreet & ", " & RefereeCity & ", " & RefereeZip & ", " & RefereeState

'und ebenso für die Ortsadressen
GymAddress = GymStreet & ", " & GymCity & ", " & GymZip & ", " & GymState
Die Routenplanung selbst ist recht simpel. Man nehme zwei Adressen, lasse diese finden, füge sie einer Route hinzu und lasse die Route berechnen. Der Teufel liegt wie so oft im Detail …

'Routenpunkte zur Route hinzufügen
Set oLocReferee = oMap.Find(RefereeAddress)
oRoute.Waypoints.Add oLocReferee
Set oLocGym = oMap.Find(GymAddress)
oRoute.Waypoints.Add oLocGym

'Routenplanung für die schnellste Strecke berechnen und als gerundete, doppelte Strecke eintragen
With oRoute.Waypoints
oRoute.Waypoints.Item.SegmentPreferences = geoSegmentQuickest
oRoute.Calculate
Cells(currentRow, currentColumn) = 2 * Round(oRoute.Distance, 0)
oRoute.Clear
End With

Nun noch einige Details auf die zu achten ist:

• Wer statt der Routenberechnung über die Route mit Waypoints versucht ein einfaches DistanceTo auf zwei Adressen aufzurufen, bekommt noch leichter und vor allem weniger rechneintensiver Werte. Diese geben allerdings die reine Luftlinienentfernung an und sind für Routenplanungen wenig hilfreich.
  'DistanceTo berechnet reine Luftlinienentfernung ...
  Cells(currentRow, currentColumn) = 2 * Round(oLocReferee.DistanceTo(oLocGym), 0)
  
• die Entfernungsangaben in km darstellen lassen war noch relativ leicht
  'Entfernungsberechnung in Kilometern, ist eh Standard-Einstellung für MapPoint Europe, aber sicher ist sicher :-)
  oMpApp.Units = geoKm
  
• die Berechnung der Route für die schnellste Strecke verbirgt sich in den Segment-Einstellungen
  oRoute.Waypoints.Item.SegmentPreferences = geoSegmentQuickest
  

Anschließend habe ich noch zahlreiche Formatierungen vorgenommen, um die Tabellenblätter etwas zu "hübschen" :-) Auf die Darstellung davon verzichte ich hier mal. Bei Bedarf kann das nachgefragt werden.
Tipp: viele der VBA-Funktionen lassen sich leichter (als mit der Hilfe) entdecken, wenn man einfach ein Makro aufzeichnet, welches die gewünschte Funktionalität einmal macht und dann den Code anschaut. Da läss sich meist leicht und schnell erkennen, was getan wird und wie man as auf seine eigenen Zwecke anpasst.