Output array to excel with VBA

Question

Im am trying to output an array coming from JsonConverter to excel but I get an error 1004:

Sub test() Dim parsed As Object Dim myArray As Variant Set parsed = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}") Set myArray = parsed("b") Set TxtRng = ThisWorkbook.Sheets("Project").Range("A44:D44") TxtRng.Value = Application.Transpose(myArray) End Sub 

The error is at TxtRng.Value = Application.Transpose(myArray).

Can someone help solve this issue? Thank you.

Answer 1

First: You shouldn't assume that it is commonly known what JsonConverter because it is not a build-in object. I assume you are talking about https://github.com/VBA-tools/VBA-JSON ?

In your example, parsed will return a Dictionary and parsed("b") will return a Collection. Application.Transpose expects a (2-dimensional) array as parameter, but will not work with a collection.

Probably the easiest way to solve this is simply to loop over the collection. Or use a helper function like similar to Converting VBA Collection to Array to create an intermediate array first