CODEWITHSUNDEEP
excelvba
Matheus Leão
Matheus Leão

Reputation: 437

VBA Print Array to Sheet

I understand there are many similar questions but none of the ones I looked at solved my issue. I'm completely new to VBA and I don't understand how its data structures work.

I'm getting a JSON string from a web API and I'm trying to print a specific field into the spreadsheet. The JSON has the following format:

{
   "responses":[
      {
         "responseId":"someId",
         "values":{
            ...,
            "QID2":1
         }
      },
      {
         "responseId":"someOtherId",
         "values":{
            ...,
            "QID2":2
         }
      },
      ...
   ]
}

The fields I'm interested in are the "QID2"s, which always have a value in range [1,3]. Here's my code to parse and print this JSON:

 'Load response into sheet
    Dim Parsed As Dictionary: Set Parsed = JsonConverter.ParseJson(oHttp.responseText)
    Dim Responses As Variant
    ReDim Responses(Parsed("responses").Count)

    Dim Response As Dictionary
    Dim i As Long
    i = 0
    For Each Response In Parsed("responses")
        Responses(i) = Response("values")("QID2")
    Next Response

    Sheets("Sheet1").Range(Cells(1, 1), Cells(Parsed("responses").Count, 1)) = Responses

I've checked using Debug.Print that Responses contains varied values (a combination of 1's, 2's and 3's). But the output of this code only prints a column of 1's on the sheet. How can I properly print the array Responses to a sheet?

EDIT: On closer inspection, my loop seems weird in that it doesn't increment i (I copied this syntax from somwhere). Is this correct?

Upvotes: 0

Views: 250

Answers (1)

Peyter
Peyter

Reputation: 484

You can try this code to see how to transpose an array manually into a temp array, and output as a function.

    Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 0-based array (v)

    Dim x As Long, y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

    ReDim tempArray(Xupper, Yupper)
    For x = 0 To Xupper
        For y = 0 To Yupper
            tempArray(x, y) = v(y, x)
        Next y
    Next x

    TransposeDim = tempArray

End Function

Upvotes: 1

Related Questions