+ Reply to Thread
Results 1 to 4 of 4

Zip code error, Need help if zip starts with zero

Hybrid View

  1. #1
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Zip code error, Need help if zip starts with zero

    I have been using a "get distance" VBA to calculate the distance between two zip codes (can work with city names as well) that links to a google api.

    I stumbled upon a calculation error yesterday that occurs when a zip code begins with a zero. The VBA is ignoring that 0 and just picks up the rest of the numbers. I think what I need to add is something like

    If length of start does not = 5, then start needs to have however many zeros added in front of the number until length = 5

    (attached file, see row 4, - link should be creatinghttps://maps.googleapis.com/maps/api...j8SzHMSMltzQC4, but instead is creating https://maps.googleapis.com/maps/api...j8SzHMSMltzQC4)

    VBA is
    Public Function GetDistance(start As String, dest As String)
        Dim firstVal As String, secondVal As String, lastVal As String
        firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
        secondVal = "&destinations="
        lastVal = "&mode=car&language=pl&sensor=false&key=" & Range("KEY")
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        Url = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
        objHTTP.Open "GET", Url, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
        Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
        Set matches = regex.Execute(objHTTP.responseText)
        tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
        GetDistance = CDbl(tmpVal)
        Exit Function
    ErrorHandl:
        GetDistance = -1
    End Function
    So, what I am trying to figure out in the VBA is how to make it so that if "start" and/or "dest" begin with a zero, how to make it look at start and/or dest as "0"+start

    Help!
    Attached Files Attached Files
    Last edited by snuffnchess; 01-04-2019 at 12:56 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Zip code error, Need help if zip starts with zero

    I punched in the zips for Boston (00215) and Pasadena (91106 where I am) and got 3229 miles
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: Zip code error, Need help if zip starts with zero

    Sorry for this... I solved the problem... I am leaving the solution up in case anybody needs is. Question that I do have, though, is there a better way to do this???

    Public Function GetDistance(start As String, dest As String)
        start = String(5 - Len(start), "0") & start
        dest = String(5 - Len(dest), "0") & dest
        
        Dim firstVal As String, secondVal As String, lastVal As String
        firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
        secondVal = "&destinations="
        lastVal = "&mode=car&language=pl&sensor=false&key=" & Range("KEY")
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        Url = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
        objHTTP.Open "GET", Url, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
        Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
        Set matches = regex.Execute(objHTTP.responseText)
        tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
        GetDistance = CDbl(tmpVal)
        Exit Function
    ErrorHandl:
        GetDistance = -1
    End Function

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Zip code error, Need help if zip starts with zero

    You can simply use Format()
    ex:
    start = Format(start, "00000")
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code "suddenly" starts giving me error 6 overflow
    By jayherring86 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-20-2016, 07:38 AM
  2. Replies: 1
    Last Post: 06-14-2015, 10:06 PM
  3. [SOLVED] The code starts automatically when opening the workbook
    By Sarangsood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2013, 04:53 AM
  4. Code Starts itself as I open the XLSM file, and it should not
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2013, 08:03 AM
  5. Replies: 1
    Last Post: 05-17-2013, 04:56 AM
  6. Stop my code before it starts
    By techteam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2011, 06:08 AM
  7. how to determinde which button starts the code
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-05-2009, 07:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1