Excel VBA带ParamArray的自定义函数访问Dictionary项出错求助

核心问题拆解 #

你的GetJSONValue2函数出错的核心原因是未区分JSON解析后的对象类型，以及索引规则不匹配：

1. 主流VBA JSON解析器（比如常见的GitHub开源实现）会把JSON数组（比如你的Items字段）解析为Collection对象，而非Dictionary。你的函数没有判断当前节点的类型，直接统一用Dictionary的键访问方式处理，遇到Collection时自然触发类型不匹配错误。
2. 调试时访问JSONDictionary("Items")直接终止，是因为VBA在UDF（用户自定义函数）上下文里遇到未捕获的类型错误时，会直接返回#VALUE!且隐性终止调试流程，不会抛出明确异常。
3. 即使部分解析器将JSON数组转为VBA原生数组，这类数组通常是0起始索引，但你传入的索引是1，若未做索引转换也会导致访问异常。

修复思路与代码示例 #

要解决问题，必须在函数中加入类型判断逻辑，针对Dictionary、Collection、原生数组分别做处理：

Function GetJSONValue2(ParamArray keys() As Variant) As Variant
    Dim currentObj As Variant
    Dim key As Variant
    Dim i As Integer
    
    Set currentObj = JSONDictionary ' 全局JSON字典对象
    On Error GoTo ErrorHandler ' 捕获错误避免直接终止

    For i = LBound(keys) To UBound(keys)
        key = keys(i)
        Select Case TypeName(currentObj)
            Case "Dictionary"
                ' 处理字典节点，按字符串键访问
                If currentObj.Exists(CStr(key)) Then
                    Set currentObj = currentObj(CStr(key))
                Else
                    GetJSONValue2 = "#KEY_NOT_FOUND"
                    Exit Function
                End If
            Case "Collection"
                ' 处理集合节点（对应JSON数组），按数字索引访问（1起始）
                If IsNumeric(key) Then
                    If key >= 1 And key <= currentObj.Count Then
                        Set currentObj = currentObj(key)
                    Else
                        GetJSONValue2 = "#INDEX_OUT_OF_RANGE"
                        Exit Function
                    End If
                Else
                    GetJSONValue2 = "#INVALID_INDEX"
                    Exit Function
                End If
            Case "Variant()"
                ' 处理VBA原生数组（部分解析器的输出），转0起始索引
                If IsNumeric(key) Then
                    Dim arrIndex As Integer
                    arrIndex = CInt(key) - 1 ' 把传入的1转为数组的0索引
                    If arrIndex >= LBound(currentObj) And arrIndex <= UBound(currentObj) Then
                        currentObj = currentObj(arrIndex)
                    Else
                        GetJSONValue2 = "#INDEX_OUT_OF_RANGE"
                        Exit Function
                    End If
                Else
                    GetJSONValue2 = "#INVALID_INDEX"
                    Exit Function
                End If
            Case Else
                ' 终端节点，直接返回值
                GetJSONValue2 = currentObj
                Exit Function
        End Select
    Next i

    GetJSONValue2 = currentObj
    Exit Function

ErrorHandler:
    GetJSONValue2 = "#VALUE!"
End Function

额外调试建议 #

• 在类型判断的分支处加断点，查看currentObj的实际类型，确认你的解析器对JSON数组的处理方式（是Collection还是原生数组）
• 确保JSONDictionary在UDF调用前已正确加载JSON数据，且全局可访问

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