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.

FunThomas · Accepted Answer · 2020-10-05 12:53:38Z

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

answered Oct 5, 2020 at 12:53

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Output array to excel with VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related