VBA Print Array to Sheet

Question

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?

i = i + 1 inside the Loop... I suppose that Parsed("responses").Count > 1. — FaneDuru
– FaneDuru, Commented Jan 14, 2020 at 14:29
You have initialized a horizontal array (per definition). You'll have to transpose your array to fill your vertical range > Application.Transpose(Responses). If you don't transpose your array, it will fill all cells with the first element from your array. — JvdV
– JvdV, Commented Jan 14, 2020 at 14:38
I had tried i = i + 1 and Application.Transpose before but not both together. It now works using both. — Matheus Leão
– Matheus Leão, Commented Jan 14, 2020 at 15:10

Peyter · Accepted Answer · 2020-01-14 14:48:29Z

1

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

answered Jan 14, 2020 at 14:48

Peyter

4843 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JvdV Over a year ago

Why would you build your own Application.Transpose function? Out of curiosity =)

Scott Craner Over a year ago

@JvdV Application.Transpose has a limit ~62,000. This allows the transposition of any size array.

Peyter Over a year ago

Hi JvdV. There's been cases over the years where I needed to pull specific things out of the array during the transpose. One the problems involved unicode place holder characters which needed to be removed from the array before printing the page. Id transpose the array into 2 different temp arrays, then run the unicode removal on the one before merging back. It was to wrap my single-threaded brain around the multi-threaded problem XD...and yes for ADODB transpose... like scott said ^ limit.

Collectives™ on Stack Overflow

VBA Print Array to Sheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related