Excel VBA运行时错误9（Subscript out of range）求助：代码执行反复触发下标越界错误

Let's walk through the common causes of this error and how to fix each one—since the debug highlight points to an array operation, we'll focus on where your arrays might be going wrong.

1. Empty or Failed Yahoo Finance Request #

The most likely culprit is that your resultFromYahoo variable is empty or invalid. This happens if:

• Your cookie or crumb values are expired (Yahoo's auth tokens change frequently)
• The Symbols, startDate, endDate, or period parameters are malformed
• The HTTP request fails entirely (e.g., network issue, Yahoo blocks the request)

Fix: Add Request Validation #

Add checks to confirm the request succeeded before processing the response:

With objRequest
    .Open "GET", tickerURL, False
    .setRequestHeader "Cookie", cookie
    .send
    .waitForResponse
    
    ' Check if the request returned a success status code
    If .Status <> 200 Then
        MsgBox "Request failed: " & .Status & " - " & .StatusText
        Exit Sub
    End If
    
    resultFromYahoo = .ResponseText
    
    ' Ensure we actually got data back
    If Trim(resultFromYahoo) = "" Then
        MsgBox "No data received. Verify your cookie/crumb, symbol, and date parameters."
        Exit Sub
    End If
End With

2. Filtered csv_rows Array Becomes Empty #

Your line csv_rows = Filter(csv_rows, csv_rows(0), False) is meant to remove the header row, but if csv_rows(0) is an empty string (e.g., the response starts with a blank line), this filter will delete every row in the array. An empty array will throw a subscript error when you try to access UBound(csv_rows).

Fix: Clean Rows and Remove Header Safely #

Replace your existing array filtering code with this more robust version:

csv_rows() = Split(resultFromYahoo, Chr(10))

' First, remove all blank rows
csv_rows = Filter(csv_rows, "", False)

' Check if we have any rows left after cleaning
If UBound(csv_rows) = -1 Then
    MsgBox "No valid data rows found in the response."
    Exit Sub
End If

' Remove the header row (take all elements starting from index 1)
csv_rows = Mid(csv_rows, 1)

3. Invalid resultArray Initialization #

If csv_rows is empty when you run ReDim resultArray(0 To UBound(csv_rows), 0 To nColumns), UBound(csv_rows) will return -1, creating an array with an invalid row range (0 To -1). Any attempt to write to this array will trigger the error.

Fix: Validate csv_rows Before Initializing resultArray #

We already added a check for empty csv_rows in the previous step, but make sure you only initialize resultArray after confirming there are rows to process:

' After cleaning and trimming csv_rows...
nColumns = 6
ReDim resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant

4. Mismatched Column Counts Mid-Processing #

While you do use ReDim Preserve to adjust resultArray's column count, there's an edge case where a row has fewer columns than nColumns. This shouldn't cause an error, but adding an extra check ensures you never try to write to an out-of-bounds column:

For iCols = LBound(CSV_Fields) To UBound(CSV_Fields)
    ' Only assign if the column index is within the resultArray's bounds
    If iCols <= UBound(resultArray, 2) Then
        If IsNumeric(CSV_Fields(iCols)) Then
            resultArray(iRows, iCols) = Val(CSV_Fields(iCols))
        ElseIf IsDate(CSV_Fields(iCols)) Then
            resultArray(iRows, iCols) = CDate(CSV_Fields(iCols))
        Else
            resultArray(iRows, iCols) = CStr(CSV_Fields(iCols))
        End If
    End If
Next

内容的提问来源于stack exchange，提问作者Zeeshan Hafeez