PDA

View Full Version : Mappoint macro getting error on calculating distance



shahdelsol
08-19-2009, 02:48 PM
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:


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

Eric Frost
08-19-2009, 05:16 PM
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

shahdelsol
08-19-2009, 05:59 PM
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.

Winwaed
08-19-2009, 10:52 PM
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

Wilfried
08-20-2009, 03:48 AM
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):



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.

Winwaed
08-20-2009, 08:02 AM
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.

shahdelsol
08-20-2009, 10:30 AM
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):



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.

Eric Frost
08-20-2009, 10:54 AM
Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

Eric

shahdelsol
08-20-2009, 11:06 AM
Can you post in a sample of your spreadsheet? Just a few lines of data would be sufficient.

Eric
Zip: Mile:
98001980023.318122980033.1689939800424.48203980052 6.060319800624.363969800724.034649800826.091389800 924.395039801020.026799801135.318749801239.4819398 01326.2849801442.787629801523.52511
Is this what you want?

Eric Frost
08-20-2009, 11:07 AM
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.


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

On Error Resume Next
at the top would have worked, too.

hope this helps!
Eric

Eric Frost
08-20-2009, 11:08 AM
Is this what you want?

Nope. I attached a spreadsheet to the later post, that's what I meant. You see you can attach files and screenshots to the posts.

Eric

shahdelsol
08-20-2009, 11:19 AM
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.


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

On Error Resume Next
at the top would have worked, too.

hope this helps!
Eric
Thanks for code this is good as far as skipping the wrong zip but I like to have an option to correct it, basically a pop up messeage telling me what cell has a bad zip and then I decide to skip it or correct it.

Eric Frost
08-20-2009, 11:28 AM
It's easy to go back and look at the end. You can sort or do a filter.

One thing to worry about with zip codes is the leading 0's in the northeast.

When MapPoint searches for "06101" it finds the zip code.

When MapPoint searches for "6101" it finds three possible matches, none of which are the zip code.

The way the code is written above, I think it would just give you the first of the three possible matches and not give any warning or indication that it did not find the right zip code.

Anyway, just something to be aware of and make sure that's not happening (dropping the leading 0) in your application. If it is happening, the solution may be as simple as formatting the Excel cells so they are Text.

Eric

shahdelsol
08-20-2009, 11:39 AM
It's easy to go back and look at the end. You can sort or do a filter.

One thing to worry about with zip codes is the leading 0's in the northeast.

When MapPoint searches for "06101" it finds the zip code.

When MapPoint searches for "6101" it finds three possible matches, none of which are the zip code.

The way the code is written above, I think it would just give you the first of the three possible matches and not give any warning or indication that it did not find the right zip code.

Anyway, just something to be aware of and make sure that's not happening (dropping the leading 0) in your application. If it is happening, the solution may be as simple as formatting the Excel cells so they are Text.

Eric
Thanks for tip I didn't think about those zip codes I live in west coast and here all 5 digits. But my problem is about new zip codes which map may not find it or simply mistyped zipcode and by the way I don't know how to attach my spreadsheets here.

shahdelsol
08-20-2009, 12:22 PM
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):



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.

I think this is the code I need I just don't know how to use it

Eric Frost
08-20-2009, 02:28 PM
....new zip codes which map may not find it or simply mistyped zipcode and by the way I don't know how to attach my spreadsheets here.

It's down here underneath with all the options when you are making a post.

Note that Excel is not one of the allowed file types, so you would have to zip it first.

Eric

Eric Frost
08-20-2009, 02:29 PM
I think this is the code I need I just don't know how to use it

Why the specific need for a pop-up while it is going through? I think it would be a lot easier and make more sense to just let it run, and then clean up any it did not find afterwards.

Eric

shahdelsol
08-20-2009, 03:07 PM
Why the specific need for a pop-up while it is going through? I think it would be a lot easier and make more sense to just let it run, and then clean up any it did not find afterwards.

Eric

I like your code for my sheets with a lots of zip codes but I have another sheet with only 2 zips and some other excel formulas to make some calcalations including distance. I don't want the user think the distance already been calculated since he or she will see no error. In this case cell for distance show 0 or nothing and other cells have some value in and user might overlooked distance cell. So the idea is user would know something wrong with zip and he or she must correct the zip before saving the file. I hope I did explain it well.

Eric Frost
08-20-2009, 03:44 PM
I still recommend you get a programmer. Sounds like more than a hobby. :-) it is like you want us to do every piece of your project for you. :1eye:

I guess I would probably recommend to insert a counter and just have a pop-up at the end that reports the outcome. e.g.


Finished in ?? seconds.

?? zip codes found out of ??.

?? zip codes not found.

Maybe also include some instructions either in the spreadsheet in a separate worksheet called "Instructions" that explains what to do with the missing distances.

Eric

shahdelsol
08-20-2009, 03:59 PM
I still recommend you get a programmer. Sounds like more than a hobby. :-) it is like you want us to do every piece of your project for you. :1eye:

I guess I would probably recommend to insert a counter and just have a pop-up at the end that reports the outcome. e.g.



Maybe also include some instructions either in the spreadsheet in a separate worksheet called "Instructions" that explains what to do with the missing distances.

Eric

I am not sure why you have problem with my post. On one hand you are trying to help which is nice , on other hand you are tyring to make me go. As I said, if you want to reply I appriciate it if not you can just ignore my post and let others respond.

Eric Frost
08-20-2009, 04:24 PM
Well, hopefully when you have learned a few things you will return the favor to others asking questions in this forum. :-)

Eric

shahdelsol
08-20-2009, 04:27 PM
Well, hopefully when you have learned a few things you will return the favor to others asking questions in this forum. :-)

Eric

I will do that if I know I can help

shahdelsol
08-20-2009, 04:33 PM
Well, hopefully when you have learned a few things you will return the favor to others asking questions in this forum. :-)

Eric
By the way I think I know how to do the code now. Your code returns 0 if bad zip entered. So what I do now , I create a pop up code if there is 0 in the cell a pop up comes up and let the user know there is a bad zip.

Eric Frost
08-20-2009, 09:07 PM
Great! So you want to post your new code for anyone following along who has identical or similar challenges?

Use the code tags.

Eric

shahdelsol
08-20-2009, 09:19 PM
Great! So you want to post your new code for anyone following along who has identical or similar challenges?

Use the code tags.

Eric

Sure here is the code:

Private Sub Worksheet_Calculate()
If [A1] <= 0 Then MsgBox "Wrong zip code or city! Check your input and try again!", vbOKOnly + vbInformation, "Wrong Zip"
End Sub


A1 is where the distance result goes to. There is only one small problem with it, because of this code MapPoint still stay open even though "objMap.Saved = True"
I guess you can manually close the mappoint but I hope there is code for closling the map automatically.

Eric Frost
08-20-2009, 09:34 PM
Can you post the complete code so other people could see how it all fits together?

Use the code tags.

Eric

shahdelsol
08-20-2009, 09:46 PM
Can you post the complete code so other people could see how it all fits together?

Use the code tags.

Eric

Here is the complete code:
B12 is where the distance result goes to. There is only one small problem with it, but because of second code:
Private Sub Worksheet_Calculate()
MapPoint still stay open even though "objMap.Saved = True"
I guess you can manually close the mappoint but I hope there is code for closing the map automatically.


Private Sub CommandButton1_Click()
Dim oApp As MapPoint.Application
Set oApp = CreateObject("MapPoint.Application.NA.16")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute
On Error Resume Next
szZip1 = Worksheets("Sheet1").Cells(5, 2)
szZip2 = Worksheets("Sheet1").Cells(6, 2)
'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(12, 2) = objRoute.Distance
Dim objApp As MapPoint.Application
ObjRoute.clear
objMap.Saved = True
End Sub

Private Sub Worksheet_Calculate()
If [B12] <= 0 Then MsgBox "Wrong zip code or city! Check your input and try again!", vbOKOnly + vbInformation, "Wrong Zip"
End Sub

Eric Frost
08-20-2009, 10:34 PM
Cool. That was the quote tags though. The code tags should preserve the indenting (which help readability) as in previous posts you saw in this thread.
Eric

Wilfried
08-21-2009, 02:27 AM
Hi,

Saved = true; prevent the dialog box 'save the map' to pop up, and to close mapPoint there is a Quit method.

shahdelsol
08-21-2009, 08:48 AM
Hi,

Saved = true; prevent the dialog box 'save the map' to pop up, and to close mapPoint there is a Quit method.

What would be the code for quitting then?

Wilfried
08-23-2009, 07:02 AM
Hi,


objMap.Saved = True
objMap.Quit()

shahdelsol
08-23-2009, 09:24 AM
Hi,


objMap.Saved = True
objMap.Quit()

I get an error a syntex error.

Eric Frost
08-23-2009, 10:13 AM
What is the error?

Do you have any theories on why that might not work?

Open the Help file and look for the Quit method.

See what object it applies to.

Eric

shahdelsol
08-23-2009, 02:48 PM
What is the error?

Do you have any theories on why that might not work?

Open the Help file and look for the Quit method.

See what object it applies to.

Eric

The error says "Compile error, synthex error" on the object property about quit says : Sub quit () quits the applicaion.

In the meantime I like to make some changes on code. I think this will be a better code but still I have the same problem. I applied the same code to different sheets. It is very interesting on one it works perfect and on the other keeps the map open until you manually close it and I don't know how the same code will produce two different result.

Private Sub CommandButton1_Click()
Dim oApp As MapPoint.Application

Set oApp = CreateObject("MapPoint.Application.NA.16")
oApp.Visible = True
Set objMap = oApp.NewMap
Set ObjRoute = objMap.ActiveRoute
On Error Resume Next
szZip1 = Worksheets("sheet1").Cells(5, 2)
szZip2 = Worksheets("sheet1").Cells(6, 2)
On Error Resume Next
'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(12, 2) = ObjRoute.Distance

Dim objApp As MapPoint.Application

ObjRoute.Clear
objMap.Saved = True

End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("B12")
If Not Intersect(Target, rng) Is Nothing Then
If rng = "0" Then
MsgBox "Cell" & _
rng.Address & " = Wrong Zip Code or City! Check Your Input and Try Again!"
End If
End If
Set rng = Nothing
End Sub

Eric Frost
08-23-2009, 02:57 PM
Did you manage to open the Help file? Do you have the Help file? .Quit is a method for the Application object.

Eric

shahdelsol
08-23-2009, 03:00 PM
Did you manage to open the Help file? Do you have the Help file? .Quit is a method for the Application object.

Eric

This is what I found under help :
Visual Basic for ApplicationsFor Each w In Application.Workbooks w.Save Next w Application.Quit
Is this what you are asking?

Eric Frost
08-23-2009, 04:12 PM
The MapPoint help file. It is (trying to be) your friend. But you have to open it.

There's a wealth of MapPoint automation examples in there with all the methods, properties and stuff.

Eric

shahdelsol
08-23-2009, 05:04 PM
The MapPoint help file. It is (trying to be) your friend. But you have to open it.

There's a wealth of MapPoint automation examples in there with all the methods, properties and stuff.

Eric
I see what you are talking about now that's what I have found:


Applies to

Objects: Application (mk:@MSITStore:C:\Program%20Files\Microsoft%20MapP oint%202009\MapPoint.CHM::/BIZOMOApplication.htm)
Syntax

object.Quit
Parameters

Part
Description
object
Required. An expression that returns an Application object.

Example

Sub QuitTheApplication()Dim
objApp As New MapPoint.Application'Set up application
objApp.Visible = True
objApp.UserControl = True
'Get the name of the appMsgBox "Application name: " + objApp.Name
'Quit the application
objApp.Quit
End Sub

Eric Frost
08-23-2009, 07:16 PM
Congrats in finding the Help file.

Now try finding the "code" tags when you post code to this site :-)

Eric

Wilfried
08-29-2009, 03:29 AM
Hi,

I gived the example with parentesis: Quit(). It should be without it: Quit. Probably that is the syntax error?