I have an Excel spreadsheet which creates reports based on contents of a JSON file. In the root of the object structure of the JSON is an object called “Buildings”. It has two properties, “Count” and an Array of objects called “Items”. One of the fields of those objects is a field called “Names”. Ex:

"Buildings": {
  "Count": 2,
  "Items": [
    {
       "Name": "Building 1",
       .
       .
       .
    }
  ]
.
.
.
}

I am trying to build an Excel VBA User Defined Function which loads the JSON contents into a Dictionary object and walks the nodes of the JSON file by accepting a parameter array containing the key values and returning a variant containing the value of the terminating node. I have written two functions below, GetJSONValue1 and GetJSONValue2. GetJSONValue1 one works correctly but is not a very good solution for more general cases. GetJSONValue2 performs a similar function, but parses the dictionary one key at a time and is a more general approach.

Public Function GetJSONValue(ParamArray Keys() As Variant) As Variant

Dim N%

N = UBound(Keys)

Select Case N
Case 0
  GetJSONValue = JSONDictionary(Keys(0))
Case 1
  GetJSONValue = JSONDictionary(Keys(0))(Keys(1))
Case 2
  GetJSONValue = JSONDictionary(Keys(0))(Keys(1))(Keys(2))
Case 3
  GetJSONValue = JSONDictionary(Keys(0))(Keys(1))(Keys(2))(Keys(3))
Case 4
  GetJSONValue = JSONDictionary(Keys(0))(Keys(1))(Keys(2))(Keys(3))(Keys(4))
End Select

End Function

Public Function GetJSONValue2(ParamArray Keys() As Variant) As Variant

Dim I%, N%
Dim ThisDictionary As Dictionary

Set ThisDictionary = JSONDictionary
N = UBound(Keys)

For I = 0 To N - 1
  Set ThisDictionary = ThisDictionary(Keys(I))
Next I

GetJSONValue2 = ThisDictionary(Keys(N))

End Function

In the spreadsheet, the formula GetJSONValue1(“Buildings”,”Items”,1,”Name”) returns “Building 1” as expected. GetJSONValue2(“Buildings”,”Count”) yields a correct result as well but GetJSONValue2(“Buildings”,”Items”,1,”Name”) returns a #VALUE error. No exception is raised.

What am I doing wrong? TIA.