Function exits abruptly parsing json response
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
json excel vba
|
show 5 more comments
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
json excel vba
1
Have you debugged/ stepped through your code? What doesJSON
contain? Where does ParseJson come from?
– Andy G
Nov 22 at 16:26
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
PutDebug.Print TypeName(JSON)
before the loop. Are you getting aDictionary
or aCollection
?
– Comintern
Nov 22 at 16:59
@Comintern Dictionary
– newguy
Nov 22 at 17:00
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01
|
show 5 more comments
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
json excel vba
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
json excel vba
json excel vba
edited Nov 22 at 16:40
Mathieu Guindon
41.1k762140
41.1k762140
asked Nov 22 at 16:20
newguy
1,67521644
1,67521644
1
Have you debugged/ stepped through your code? What doesJSON
contain? Where does ParseJson come from?
– Andy G
Nov 22 at 16:26
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
PutDebug.Print TypeName(JSON)
before the loop. Are you getting aDictionary
or aCollection
?
– Comintern
Nov 22 at 16:59
@Comintern Dictionary
– newguy
Nov 22 at 17:00
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01
|
show 5 more comments
1
Have you debugged/ stepped through your code? What doesJSON
contain? Where does ParseJson come from?
– Andy G
Nov 22 at 16:26
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
PutDebug.Print TypeName(JSON)
before the loop. Are you getting aDictionary
or aCollection
?
– Comintern
Nov 22 at 16:59
@Comintern Dictionary
– newguy
Nov 22 at 17:00
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01
1
1
Have you debugged/ stepped through your code? What does
JSON
contain? Where does ParseJson come from?– Andy G
Nov 22 at 16:26
Have you debugged/ stepped through your code? What does
JSON
contain? Where does ParseJson come from?– Andy G
Nov 22 at 16:26
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
Put
Debug.Print TypeName(JSON)
before the loop. Are you getting a Dictionary
or a Collection
?– Comintern
Nov 22 at 16:59
Put
Debug.Print TypeName(JSON)
before the loop. Are you getting a Dictionary
or a Collection
?– Comintern
Nov 22 at 16:59
@Comintern Dictionary
– newguy
Nov 22 at 17:00
@Comintern Dictionary
– newguy
Nov 22 at 17:00
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01
|
show 5 more comments
1 Answer
1
active
oldest
votes
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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434917%2ffunction-exits-abruptly-parsing-json-response%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
add a comment |
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
add a comment |
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
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
edited Nov 22 at 17:34
answered Nov 22 at 17:28
QHarr
29.8k81841
29.8k81841
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434917%2ffunction-exits-abruptly-parsing-json-response%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Have you debugged/ stepped through your code? What does
JSON
contain? Where does ParseJson come from?– Andy G
Nov 22 at 16:26
@AndyG Yes I did stepped through the code. ParseJson is from here: github.com/VBA-tools/VBA-JSON and JSON contains the right response when I checked it in local variables window. Basically it work as expected when I execute it via subroutine but exits if I use function.
– newguy
Nov 22 at 16:48
Put
Debug.Print TypeName(JSON)
before the loop. Are you getting aDictionary
or aCollection
?– Comintern
Nov 22 at 16:59
@Comintern Dictionary
– newguy
Nov 22 at 17:00
You might want to remove your API key from the above.
– QHarr
Nov 22 at 17:01