VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.74k stars 566 forks source link

Parse Variant to simple list #233

Closed jbecker069 closed 2 years ago

jbecker069 commented 2 years ago

Hey guys! I'm trying to do the following:

  1. Collect data from a column of a sheet: Dim some_list As Variant some_list = ThisWorkbook.Sheets("Sheet").Range("A1:A10")
  2. Construct a body for a POST request using this "some_list" variable: Dim body as New Dictionary body.Add "some_list", some_list ... body.Add ...
  3. Convert this VBA Dict to JSON using ConvertToJson Dim json as String json = JsonConverter.ConvertToJson(body)

What I get in return is this: {"some_list":[[value1], [value"], [value_3], ...]}

My question/problem is that the dtype Variant is parsed in a list of lists. My aim is that it is just parsed into a list like: {"some_list":[value1, value", value_3, ...]}

Help is much appreciated! Thanks.

houghtonap commented 2 years ago

The gist I get from the given problem is that some_list, even though it is declared as a variant, is really Excel.Range. Which means the variant contains an object reference to Excel.Range. This can be verified with VBA.TypeName(some_list) which will return Range. As a sidebar, it probably would be better to declare some_list as Dim some_list as Excel.Range making it clearer as to what it is and also allowing intellisense in the VBA code editor.

What it sounds like you want is a JSON Object with the property "some_list" that has as its value a JSON Array with the values from the Excel Range A1:A10. This would be represented in VBA-JSON as a Scripting.Dictionary that has a key "some_list" and its value is a VBA.Collection that contains the values from the Excel Range A1:A10.

Public Sub test()

  Dim list As Excel.Range
  Dim dict As New Scripting.Dictionary
  Dim arry As New VBA.Collection
  Dim item As Variant
  Dim json As String

  Set list = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")

  For Each item In list
    arry.Add item
  Next item

  dict.Add "list", arry

  json = ConvertToJson(dict)

  Debug.Print json

  Exit Sub
End Sub

which results in the following when the Excel Range A1:A10 contains numbers starting at 1:

{"list":[1,2,3,4,5,6,7,8,9,10]}

Hope that helps.