Qfyilyi

Function exits abruptly.

I am trying to parse this json response https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=AAPL&apikey=GKXCJ44J3TESZGZK&outputsize=full If i try with a subroutine I get the parsed values but when I try to parse and update the values in ranges using function it exits right after this line .Range("B" & fO) = Item

Function GetFullData(tickeR)



Dim urL As String, i As Long, j As Long, fO As Long

Dim http As Object, JSON As Object, Item, dateItems

Dim apiKey



Application.Calculation = xlCalculationManual



apiKey = "GKXCJ44J3TESZGZK"

urL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & tickeR & "&apikey=" & apiKey & "&outputsize=full"



Set http = CreateObject("MSXML2.XMLHTTP")



With MAIN



    http.Open "GET", urL, False

    http.Send



    fO = 4



    If http.Status = 200 Then



        Set JSON = ParseJson(http.responseText)



        For Each Item In JSON("Time Series (Daily)")

            .Range("B" & fO) = Item

            .Range("C" & fO) = JSON("Time Series (Daily)")(Item)("1. open")

            .Range("D" & fO) = JSON("Time Series (Daily)")(Item)("2. high")

            .Range("E" & fO) = JSON("Time Series (Daily)")(Item)("3. low")

            .Range("F" & fO) = JSON("Time Series (Daily)")(Item)("4. close")

            .Range("G" & fO) = JSON("Time Series (Daily)")(Item)("5. volume")

            fO = fO + 1

        Next Item



    Else

        MsgBox "Invalid ticker!": Exit Function

    End If



End With



GetFullData = "Process Completed"



Set http = Nothing

Application.Calculation = xlCalculationAutomatic



End Function

User defined functions are not to operate on other cells. They are only to return something to the calling cell. Make it a sub and attach that sub to a form control command button. An example is shown below where you attach to a button and have a named range in that sheet e.g. A1 called tickerCell. That holds the ticker value to use.

You could even tie the macro to a Worksheet_Change event with the target as tickerCell such that when its value changes a new call is made to get associated data.

API calls using UDFs: If number of API calls is not an issue you can go down an inefficient route of adding an argument to your function which determines which part of the JSON is returned to the calling cell. Inside the UDF implement a Select Case argument .... Case "A" return "B", Case "C" return "D" ...etc.
UDFs making API calls is generally not a good idea as UDFs can recalculate for a number of reasons and end up making for expensive API calls.

Option Explicit



Public Sub GetAllData()

    Dim apiKey As String, ticker As String, url As String, json As Object, fO As Long

    Dim ws As Worksheet, item As Variant



    Set ws = ThisWorkbook.Worksheets("Main")

    ticker = ws.Range("tickerCell").Value

    apiKey = "yourKey"

    url = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & ticker & "&apikey=" & apiKey & "&outputsize=full"



    With CreateObject("MSXML2.XMLHTTP")



        .Open "GET", url, False

        .send

        If .Status = 200 Then



            Set json = JsonConverter.ParseJson(.responseText)

            fO = 4

            With ws

                For Each item In json("Time Series (Daily)")

                    .Range("B" & fO) = item

                    .Range("C" & fO) = json("Time Series (Daily)")(item)("1. open")

                    .Range("D" & fO) = json("Time Series (Daily)")(item)("2. high")

                    .Range("E" & fO) = json("Time Series (Daily)")(item)("3. low")

                    .Range("F" & fO) = json("Time Series (Daily)")(item)("4. close")

                    .Range("G" & fO) = json("Time Series (Daily)")(item)("5. volume")

                    fO = fO + 1

                Next item

            End With

        Else

            MsgBox "Response <> 200"

        End If



    End With

End Sub