How to return blank or zero when 'On Error Resume Next' leaves previous value in variable?

Question

I have VBA code that gets the stock price in a loop.

There are stock symbols not found in this API source. It will result to error for those stocks.

I'm using On Error Resume Next so the VBA code will continue to the next symbol.

My problem is the symbol in error will return a stock price of the last stock symbol not in error.

I would like to make the result blank or zero for the stock symbols in error.

Current Result - the italicized symbols are those that are in error.

Stock Symbol Price
BDO 158.00
ABS 15.80
GREEN 1.87
ALHI 1.87
LAND 1.87
PLC 0.57
LBC 0.57
EVER 0.57

Desired Result - the italicized symbols those that are in error will result or will give return of 0

Stock Symbol Price
BDO 158.00
ABS 15.80
GREEN 1.87
ALHI 0
LAND 0
PLC 0.57
LBC 0
EVER 0

    Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "http://phisix-api.appspot.com/stocks/" & symbol & ".json"
    myrequest.Send

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(myrequest.ResponseText)

    i = Json("stock")(1)("price")("amount")
    ws.Range(Cells(n, 2), Cells(n, 2)) = i

    n = n + 1

    On Error Resume Next

Next X

ws.Columns("B").AutoFit
MsgBox ("Stock Quotes Refreshed.")

ws.Range("B4:B" & lastrow).HorizontalAlignment = xlGeneral
ws.Range("B4:B" & lastrow).NumberFormat = "#,##0.00"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Can you add the JSON response in the question for valid and invalid stocks ? — stud3nt, Jan 01 '20 at 06:18
[Format data as table](https://www.tablesgenerator.com/markdown_tables) and have a look at [VBA-Web](https://github.com/VBA-tools/VBA-Web) for webrequest. — ComputerVersteher, Jan 01 '20 at 11:26

score 3 · Answer 1 · answered Jan 01 '20 at 07:05

3

Your code as it stands sets On Error Resume Next at the end of the first time through the loop, and from that point on ignores any and all errors. That's bad.

The general method of using OERN should be

Other non error causing code
Optionally initialise variables in preparation for error trap
On Error Resume Next
   Execute the instruction that might cause an error
On Error GoTo 0
If (test if instruction succeeded) Then
    Process the result
Else 
    Optionally handle the error case
End If

answered Jan 01 '20 at 07:05

chris neilsen

52,446
10
84
123

Hi @chris neilsen, appreciate your suggestion but I'm no expert on VBA, so I'm not sure how to apply the suggested method. Thanks. – Stock Tracker Jan 01 '20 at 07:27
@StockTracker read https://riptutorial.com/vba/example/11022/resume-keyword! – ComputerVersteher Jan 01 '20 at 11:28

score 1 · Accepted Answer · edited Jan 01 '20 at 21:19

1

You can always put a conditional check statement based on the error you receive for invalid stocks, if you are getting empty value in myrequest on invalid stock. You can write your logic like below and update price value to 0.

If myrequest is Nothing Then
    'For Invalid Stocks
End

or

If myrequest.ResponseText = "" Then
    'For Invalid Stocks
End

Let me know if it helps. Otherwise share the JSON response for both valid and invalid stocks.

Update: Based on the value of myrequest.ResponseStatus for invalid response, update the <add-condition> condition in If statement according to your requirement.

For Each X In rng

    Dim Json As Object
    Dim i
    symbol = X

    Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "http://phisix-api.appspot.com/stocks/" & symbol & ".json"
    On Error Resume Next
    myrequest.Send
    On Error GoTo 0

    Debug.Print myrequest.ResponseStatus

    If <add-condition> Then
        Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
        i = Json("stock")(1)("price")("amount")
    Else
        i = 0
    End If

    ws.Range(Cells(n, 2), Cells(n, 2)) = i
    n = n + 1

Next X

edited Jan 01 '20 at 21:19

chris neilsen

52,446
10
84
123

answered Jan 01 '20 at 06:31

stud3nt

2,056
1
12
21

Hi @stud3nt. Normally the code will stop if I remove the onerror resume next. – Stock Tracker Jan 01 '20 at 07:19
Maybe you are getting error due to some other statement. Please remove the on resume statement and tell us what error are you getting and where ? Because `On Error Resume Next` statement will only work for errors occurring after it. – stud3nt Jan 01 '20 at 07:21
if I remove the OERN, this is the error I'm getting Run-time error '10001': Error Parsing JSON: Expecting '{' or '[' – Stock Tracker Jan 01 '20 at 07:33
Normally, this would be the JSON result, I'm only getting the amount. But if the symbol is invalid the page will result to page can't be found HTTP error 404. {"stock":[{"name":"Banco de Oro","price":{"currency":"PHP","amount":158.00},"percent_change":0.38,"volume":2245820,"symbol":"BDO"}],"as_of":"2019-12-27T15:20:00+08:00"} – Stock Tracker Jan 01 '20 at 07:36
This is the webpage result if the symbol is not found. This phisix-api3.appspot.com page can’t be foundNo webpage was found for the web address: http://phisix-api3.appspot.com/stocks/ALHI.json HTTP ERROR 404 – Stock Tracker Jan 01 '20 at 07:37
Please check I have updated the answer. Also, you have to debug the code to find out the value of response on invalid request and add that as your condition for updating price in your excel. – stud3nt Jan 01 '20 at 08:01
The JSON response would be this. The invalid symbols will just be blank. How I should put this in the IF statement. Example here, there are two blank lines between GREEN and PLC symbol (valid symbols). In between are the invalid symbols ALHI and LAND. {"stock":[{"name":"GREEN","price":{"currency":"PHP","amount":1.87},"percent_change":-0.53,"volume":9898000,"symbol":"GREEN"}],"as_of":"2019-12-27T15:20:00+08:00"} {"stock":[{"name":"PREMIUM LEISURE","price":{"currency":"PHP","amount":0.57},"percent_change":0.00,"volume":14396000,"symbol":"PLC"}],"as_of":"2019-12-27T15:20:00+08:00"} – Stock Tracker Jan 01 '20 at 08:24
Valid is fine. Try to capture the invalid one condition. For example: `If ResponseCode <> "404"` perform json parse otherwise set to 0. It is difficult for me to write code without access to the api. Hope this will help – stud3nt Jan 01 '20 at 08:29
Hi @stud3nt. I made some update on the your suggested answer. And now it works. – Stock Tracker Jan 01 '20 at 08:29
Thanks anyway. I will upvote and mark it as answered. – Stock Tracker Jan 01 '20 at 08:33
You can read this guide on [comments](https://stackoverflow.com/help/privileges/comment) and [formatting](https://stackoverflow.com/editing-help#code). Also, remove the comments which have code. – stud3nt Jan 01 '20 at 08:51
@stud3nt I edited to add the necessary error reset. Hope you don't mind – chris neilsen Jan 01 '20 at 21:21

How to return blank or zero when 'On Error Resume Next' leaves previous value in variable?

2 Answers2

Linked