search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.79k stars 573 forks source link

indexing into array throws "Object doesn't support this property or method" error #196

Closed GunnerP closed 3 years ago

GunnerP commented 3 years ago

Anyone know why this throws an error (in Excel VBA) Dim json As Object Set json = jsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

json("c")("d") = 123

works ok but 

json("b")(2) = 99

gives a Run-time error 438

GunnerP commented 3 years ago

ok, solved this. You can amend items in a collection; you have to remove them and re-add them

ablevinsalix commented 11 months ago

@GunnerP what did you mean by "You can amend items in a collection; you have to remove them and re-add them". I am having the same issue.

houghtonap commented 11 months ago

json("b")(2) = 99

Should give you a runtime error. You need to read the Microsoft VBA object documentation for the VBA.Collection and Scripting.Dictionary objects. These objects have different interfaces and you cannot script/index between them as you would with javascript objects because of their different interfaces.

You can search for comments authored by me that have fuller descriptions and code samples on how to use these objects.

Nick-vanGemeren commented 11 months ago

VBA Collection items are read-only (although you can modify properties of an object referenced by an item). The simplest workaround is to remove and add the item. But note that you need to specify the before position if the item order is important.

    ...
    ModCollItem json("b"), 2, 99
    ...

Sub ModCollItem(coll As Collection, idx As Long, newval As Variant)
    coll.Remove idx
    coll.Add newval, , idx
End Sub