I am using the following code for calculating distance between the zips. It works great but if there is bad zipcode or new zipcode in one cell that mappoint cannot find, the mappoint and macro stops with this error: " Run-time error- 22.... the requested member of the collection doesnot exist. Use a valid name or index number" and then it gives the option of: End or Debug which is not helpful. I want a macro instead to do this for me: if there is wrong zipcode, an pop up would give me an option to correct zip code in which cell or ignore and continue to calculate the rest of zip codes and this is the code I am using:

Code:

Private Sub CommandButton1_Click()
Set oApp = CreateObject("MapPoint.Application.NA.16")
 oApp.Visible = True
 Set objMap = oApp.NewMap
 Set objRoute = objMap.ActiveRoute
 szZip1 = Worksheets("Sheet1").Cells(1, 3)
 NRow = 3
 Do
     szZip2 = Worksheets("Sheet1").Cells(NRow, 1)
     'Add route stops and calculate the route
     objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
     objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
     objRoute.Calculate
     Worksheets("Sheet1").Cells(NRow, 3) = objRoute.Distance
     objRoute.Clear
     NRow = NRow + 1
 Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""
objMap.Saved = True

End Sub

Coding is not that hard, do you think you might be in the wrong profession? Sorry I just see you asking lots of simple questions. Not that you're not welcome on this site, but maybe your organization should get a programmer.

Eric

Originally Posted by Eric Frost

Coding is not that hard, do you think you might be in the wrong profession? Sorry I just see you asking lots of simple questions. Not that you're not welcome on this site, but maybe your organization should get a programmer.

Eric

I am learning and I am just doing this as hobby... nothing serious. You don't have to reply but this question might be also helpful for others too.

Check if it is empty or cannot be found.

Also trap exceptions. You can use "On Error" to do this, but a decent language (eg. C++ or C#) has much better support for exception handling.


Richard

Hi,

As Richard mentioned: 'on error'. I think it has to be on top of you button click event. Somethign like this (but I don't know VBA so there may be syntax errors here):

Code:

On Error goto ErrHandler;
   // here your code
ErrHandler:
   ShowMessage('Zip code does not exists or is incorrect')

I think this is more or less the same as an exception block as Richard already mentioned.

Yes it is the closest thing in VB6 or VBA. It is also possible to query some of the error information, but I find the whole mechanism a bit clunky for decent programming purposes.

Originally Posted by Wilfried

Hi,

As Richard mentioned: 'on error'. I think it has to be on top of you button click event. Somethign like this (but I don't know VBA so there may be syntax errors here):

Code:

On Error goto ErrHandler;
   // here your code
ErrHandler:
   ShowMessage('Zip code does not exists or is incorrect')

I think this is more or less the same as an exception block as Richard already mentioned.

Thanks for your help I have been trying to use your code but I was not sure where exactly to insert your code. I tried several different ways but none worked perhaps you could tell me how to use it.

Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

Eric

Originally Posted by Eric Frost

Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

Eric

Zip: Mile:
98001980023.318122980033.1689939800424.482039800526.060319800624.363969800724.034649800826.091389800924.395039801020.026799801135.318749801239.481939801326.2849801442.787629801523.52511
Is this what you want?

I worked it out.. attached is a spreadsheet with some zip code data.

In the code below, I use a FindResults object and then there's an If statement that tests whether there are any results before it tries to add the Waypoint and Calculate the route. It just skips over if it couldn't find the zip code.

Code:

Private Sub CommandButton1_Click()
Set oApp = CreateObject("MapPoint.Application.NA.16")
 oApp.Visible = True
 Set objMap = oApp.NewMap
 Set objRoute = objMap.ActiveRoute
 szZip1 = Worksheets("Sheet1").Cells(1, 3)
 NRow = 3
 
 Dim objFindResults As MapPoint.Findresults

 Do
     szZip2 = Worksheets("Sheet1").Cells(NRow, 1)
     'Add route stops and calculate the route
     objRoute.Waypoints.Add objMap.Findresults(szZip1).Item(1)
     Set objFindResults = objMap.Findresults(szZip2)
     If objFindResults.Count > 0 Then
       objRoute.Waypoints.Add objMap.Findresults(szZip2).Item(1)
       objRoute.Calculate
       Worksheets("Sheet1").Cells(NRow, 3) = objRoute.Distance
       objRoute.Clear
     End If
     NRow = NRow + 1
 Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""
objMap.Saved = True

End Sub

I think simply inserting a line

Code:

On Error Resume Next

at the top would have worked, too.

hope this helps!
Eric