在VBA中使用Excel STOCKHISTORY函数时处理#N/A错误

问题背景 #

在VBA子程序中使用STOCKHISTORY函数获取股票历史价格时，只要返回数据中有任意一天出现#N/A错误，Application.WorksheetFunction.StockHistory就会直接触发错误，导致整个数组无法填充。但在Excel单元格中直接使用该函数时，会保留所有有效数据，仅跳过错误项。

原子程序代码片段：

Public Sub GenerateStockPrices(sTicker As String, dteStart As Date, dteEnd As Date)
' ...

    On Error GoTo ErrorReceivingStockData
    mvaStockHistory = Application.WorksheetFunction.StockHistory(sTicker, dteStart, dteEnd, 0, , 0, 3, 4, 1, 2)
    mvaStockHistory = RemoveErrorsFromStockArray(mvaStockHistory)

    ' ...
    
    Exit Sub
    
ErrorReceivingStockData:
    On Error GoTo 0
    mbError = True
End Sub

希望找到比「写入隐藏工作表再读取」或「逐日调用函数」更优的方案，实现保留有效数据、跳过错误项的效果。

核心解决方案 #

将Application.WorksheetFunction.StockHistory替换为Application.StockHistory，直接调用Excel应用对象的方法而非工作表函数对象的方法。

两种调用方式的区别 #

• Application.WorksheetFunction.StockHistory：遇到任何单元格级错误时，会直接触发VBA运行时错误，中断执行且不返回任何数据。
• Application.StockHistory：与单元格中使用函数的行为一致，会返回包含所有行的数组，其中错误日期对应的位置会是Error类型值，有效数据则正常保留。

修改后的代码示例 #

Public Sub GenerateStockPrices(sTicker As String, dteStart As Date, dteEnd As Date)
' ...

    ' 改用Application.StockHistory，避免因部分错误中断整个调用
    mvaStockHistory = Application.StockHistory(sTicker, dteStart, dteEnd, 0, , 0, 3, 4, 1, 2)
    
    ' 处理数组中的错误项（比如过滤掉包含错误的行）
    mvaStockHistory = RemoveErrorsFromStockArray(mvaStockHistory)

    ' ...
    
    ' 无需再依赖错误捕获块处理部分数据错误，除非要处理函数完全调用失败的情况
    Exit Sub
    
ErrorReceivingStockData:
    On Error GoTo 0
    mbError = True
End Sub

错误项处理说明 #

修改调用方式后，mvaStockHistory数组中会包含有效数据和错误值（如#N/A对应的VBA错误类型）。你可以在RemoveErrorsFromStockArray函数中遍历数组，判断每个元素是否为错误值：

• 使用IsError()函数检测单个元素是否为错误
• 过滤掉包含错误的整行数据，或者将错误值替换为默认值（如空值、0等）

为什么这是最优方案 #

• 效率远高于逐日调用：一次性获取所有数据，避免多次API/数据源请求
• 无需依赖隐藏工作表：直接在内存中处理数组，避免工作表操作的额外开销
• 行为与单元格函数完全一致：符合预期的错误处理逻辑

内容的提问来源于stack exchange，提问作者Michael Wycisk