Custom Function to get Drive Time between two points

[randolphoralph]

I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.

http://guj800.wordpress.com/2011/04/27/drive-time-calculations-in-excel/

Here are the steps I have taken so far.

1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per http://guj800.wordpress.com/2008/06/30/getting-information-from-the-web-using-excel-vba/
2. Registered the msinet.ocx
3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.
4. Copied the custom function for DriveTime into a module.
5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)
6. In C2 I entered the formula =DriveTime(A2,B2)
7. After entering the formula I get a error (#VALUE)

I am not sure where I have gone wrong and was hoping someone maybe able to point me in the right direction.

Any help would be greatly appreciated.

 

[randolphoralph]

If anyone gets this to work please let me know. I am not even sure that it does.

 

[randolphoralph]

Correction to previous post....

7. After entering the formula I get an error (#NAME?)

It appears that the DriveTime Custom Function is not being recognized. I tried to evaluate the formula and it shows DriveTime as #NAME?

Here is the code for the custom function

Function DriveTime(PointA As String, PointB As String)
  Dim myURL As String
  myURL = "[URL]http://maps.google.com/maps?q=from[/URL]: " & PointA & " to: " & PointB
  Dim inet1 As Inet
  Dim mypage As String
  Set inet1 = New Inet
  With inet1
    .Protocol = icHTTP
    .URL = myURL
    mypage = .OpenURL(.URL, icString)
  End With
  Set inet1 = Nothing
  Dim intStart As Double, intEnd As Double
  intStart = InStr(mypage, "<div class=""altroute-rcol altroute-info"">") + 41
  intEnd = InStr(intStart, mypage, "</div>") - intStart
  DriveTime = Mid(mypage, intStart, intEnd)
End Function

Any thoughts?

 

[John_w]

The code in the link works for me.

Your steps seem correct, though make sure you have put the code in a standard module and a sheet module etc., as that's what the #NAME? error implies.

 

[randolphoralph]

Thank you John_w for looking at it for me. I have switched the code to a standard module and get the formula returns the #VALUE! error.

The formula I am using is =DriveTime(B2,C2)

I have the addresses in B2 & C2 in the following format

123 Main Street Tampa, FL 32526

Is that how you had yours?

Maybe there is another reference I need to select in addition to Microsoft Internet Transfer Control?

 

[John_w]

The code in the link works for me.

Your steps seem correct, though make sure you have put the code in a standard module and NOT a sheet module etc., as that's what the #NAME? error implies.

Ooops! I missed a crucial word there. The code should go in a standard module.

Try setting a breakpoint (press F9 key) on the myurl = line and stepping through the code by pressing the F8 key. You can check the correctness of the myurl string by pasting its contents into the browser address bar. The Google Maps web page should appear with the directions between PointA and PointB.

 

[randolphoralph]

I tried as you suggested and I set the breakpoint and tried to step through the code. When I try F8 or Step Into nothing happens.

If we are using the same code, and yours works and mine does not then wouldn't it be something more than the code?

 

[randolphoralph]

I have tested the URL and it appears to work...

 

[John_w]

I tried as you suggested and I set the breakpoint and tried to step through the code. When I try F8 or Step Into nothing happens.

You have to make the function execute by entering the UDF in a cell or press F9 on a cell containing the formula to make it recalculate.

If we are using the same code, and yours works and mine does not then wouldn't it be something more than the code?

Maybe, it's difficult to tell. My msinet.ocx is dated 25/08/2005 with a file size of 115,920 bytes.

You could try testing it like this:

Sub test()

    MsgBox DriveTime(Range("A3").Value, Range("B3").Value)
    
End Sub

where A3 and B3 contain addresses.

 

[randolphoralph]

Thanks John_w after trying your suggestion I was able to determine where the problem is

I am now getting an error “Error 429 – cannot create Activex object” when I issue the command:

Set inet1 = New Inet

Not sure what this means.