当"On Error Resume Next"将先前值保留在变量中时，如何返回空白或零? [英] How to return blank or zero when 'On Error Resume Next' leaves previous value in variable?

问题描述

我有VBA代码，可以循环获取股票价格.

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

在此API来源中找不到股票代码.这些股票将导致错误.

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

我正在使用 On Error Resume Next ，因此VBA代码将继续到下一个符号.

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.

股票代码 价格
BDO 158.00
ABS 15.80
绿色1.87
ALHI 1.87
LAND 1.87
PLC 0.57
LBC 0.57
EVER 0.57

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

所需结果-出现斜体的符号 会导致错误或返回0的结果

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

股票代码 价格
BDO 158.00
ABS 15.80
绿色1.87
ALHI 0
LAND 0
PLC 0.57
LBC 0
EVER 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

推荐答案

如果您在 myrequest 中获取了空值，则始终可以根据收到的无效股票错误来添加条件检查语句.无效库存.您可以像下面这样编写逻辑并将价格值更新为0.

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

或

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

让我知道是否有帮助.否则，共享有效和无效股票的JSON响应.

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

更新:根据用于无效响应的 myrequest.ResponseStatus 的值，根据您的要求更新 If 语句中的< add-condition> 条件.

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

这篇关于当"On Error Resume Next"将先前值保留在变量中时，如何返回空白或零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！