WinHttpRequest as a function (us census API)

[aabrahamson78]

Hi All,

I have made a Sub that I would like to turn into a function, but am having issues getting it to work. here is the Sub I got to work and the function I am trying to get to work

Public Sub Runapi()



    Dim W As Worksheet: Set W = ActiveSheet
    
    Dim URL As String: URL = "http://api.census.gov/data/2014/acs5?get=B19037_001E&for=state:01&key=8f7a0306e78e39c8c50480814428cff2de878daf"
    Dim X As New WinHttpRequest
    X.Open "GET", URL, False
    X.send
    Dim Resp As String: Resp = X.ResponseText
    Dim Lines As Variant: Lines = Split(Resp, ",")
    Dim sLines As String
    Dim Values As Variant
    sLines = Lines(2)
    Dim fLines As Variant
    fLines = Split(sLines, "[")
    rlines = fLines(1)
    
    rlines = fLines(1)
    Dim Runapi As String


Runapi = rlines
Debug.Print Runapi




End Sub

here is the function I am trying to get as I would like to be able to input whatever state and whatever code from selected cells in the sheet.

Public Function Runapi(State As String, code As String)

    Dim W As Worksheet: Set W = ActiveSheet        Dim URL As String: URL = "http://api.census.gov/data/2014/acs5?get=" & code & "&for=state:" & State & "&key=8f7a0306e78e39c8c50480814428cff2de878daf"    Dim X As New WinHttpRequest    X.Open "GET", URL, False    X.send    Dim Resp As String: Resp = X.ResponseText    Dim Lines As Variant: Lines = Split(Resp, ",")    Dim sLines As String    Dim Values As Variant    sLines = Lines(2)    Dim fLines As Variant    fLines = Split(sLines, "[")    rlines = fLines(1)Runapi = rlines
End Function

 

[aabrahamson78]

Public Function Runapi(State As String, code As String)



    Dim W As Worksheet: Set W = ActiveSheet
    
    Dim URL As String: URL = "http://api.census.gov/data/2014/acs5?get=" & code & "&for=state:" & State & "&key=8f7a0306e78e39c8c50480814428cff2de878daf"
    Dim X As New WinHttpRequest
    X.Open "GET", URL, False
    X.send
    Dim Resp As String: Resp = X.ResponseText
    Dim Lines As Variant: Lines = Split(Resp, ",")
    Dim sLines As String
    Dim Values As Variant
    sLines = Lines(2)
    Dim fLines As Variant
    fLines = Split(sLines, "[")
    rlines = fLines(1)
Runapi = rlines


End Function

 

[John_w]

The function seems to work for me.

Put 01 in A1 and B19037_001E in B1.

Put =Runapi(A1,B1) in C1

C1 shows "1842174".

I don't know if that is the expected result, but no errors occur.

 

[aabrahamson78]

Your right it is working I guess I just need a new fresh excel and a second pair of eyes
thank you for checking me I was starting to go mad