View Full Version : Distance Calculator Runs Slowly

01-13-2015, 07:38 AM
I have a macro in which I can input a start and end postcode, then let MapPoint find the drive time and distance and return it in my spreadsheet. My issue is that I am currently using this to find 300+ distances and the macro is running very slow, sometimes taking up to 60 seconds to return one result.

Can anyone help me try to speed this up in any way?

I'm using Windows XP, Excel 2003 and MapPoint Europe 2011.

Thanks for your help.

Private Sub CommandButton1_Click()Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objRoute As MapPoint.Route
Dim objLoc1 As MapPoint.Location
Dim objLoc2 As MapPoint.Location

Set objApp = CreateObject("MapPoint.Application")
objApp.Visible = False

Set objMap = objApp.NewMap
Set objRoute = objMap.ActiveRoute
Sheets("Sheet1").Cells(1, 3).Value = "Drive Distance (Miles)"
Sheets("Sheet1").Cells(1, 4).Value = "Drive Time (Mins)"
Sheets("Sheet1").Cells(1, 5).Value = "Straight Line Distance (Miles)"
NReadRow = 2

Do While Sheets("Sheet1").Cells(NReadRow, 2) <> ""

'Locate the 2 points
Set objLoc1 = objMap.FindResults(Sheets("Sheet1").Cells(NReadRow, 1)).Item(1)
Set objLoc2 = objMap.FindResults(Sheets("Sheet1").Cells(NReadRow, 2)).Item(1)

'Calculate the route
objRoute.Waypoints.Add objLoc1
objRoute.Waypoints.Add objLoc2

'Drive Distance in Miles
Sheets("Sheet1").Cells(NReadRow, 3) = objRoute.Distance
'Drive Time in Minutes
Sheets("Sheet1").Cells(NReadRow, 4) = objRoute.DrivingTime
'Straight Line Distance in Miles (as a check)
Sheets("Sheet1").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)

NReadRow = NReadRow + 1

objMap.Saved = True


End Sub

01-13-2015, 08:01 AM
I don't see anything obvious, so it could be a combination of your PC being old (you're running XP and Excel 2003 which suggests an older and possibly overloaded PC); the locations, and their positions.

Try separating the geocoding out - this could be adding to the time.
Or use an existing off-the-shelf solution (eg. our MPMileage product at MPMileage: Batch calculate mileages with Microsoft MapPoint (http://www.mpmileage.com) ). Yes it costs but you save the time developing on a discontinued product.