How to create a GeoJSON editor with VBA

[waimea]

Hi,

I am trying to create an userform that outputs a file in GeoJSON.

GeoJSON is a https://en.wikipedia.org/wiki/GeoJSON fileformat for geospatial information.

I know how to create the userform and I would like to add 16 properties where some of them can be blank.

{
    "type": "FeatureCollection",
    "features": [
      {
        "type": "Feature",
        "properties": {
          "Propertie1": "Value1",
          "Propertie2": "Value2",
          "Propertie3": "Value3",
          "Propertie4": "Value4",
          "Propertie5": "Value5",
          "Propertie6": "Value6",
          "Propertie7": "Value7",
          "Propertie8": "Value8",
          "Propertie9": "Value9",
          "Propertie10": "Value10",
          "Propertie11": "Value11",
          "Propertie12": "Value12",
          "Propertie13": "Value13",
          "Propertie14": "Value14",
          "Propertie15": "Value15",
          "Propertie16": "Value16",
        },
        "geometry": {
          "type": "Point",
          "coordinates": [
            15.1561231232
            60.54545678424146
          ]
        }
      },
      {
         "type": "Feature",
        "properties": {
          "Propertie1": "Value1",
          "Propertie2": "Value2",
          "Propertie3": "Value3",
          "Propertie4": "Value4",
          "Propertie5": "Value5",
          "Propertie6": "Value6",
          "Propertie7": "Value7",
          "Propertie8": "Value8",
          "Propertie9": "Value9",
          "Propertie10": "Value10",
          "Propertie11": "Value11",
          "Propertie12": "Value12",
          "Propertie13": "Value13",
          "Propertie14": "Value14",
          "Propertie15": "Value15",
          "Propertie16": "Value16",
        },
        "geometry": {
          "type": "Point",
          "coordinates": [
            20.91595458984375,
            60.47159370768037
          ]
        }
      },

{
    "type": "FeatureCollection",
    "features": [
      {
        "type": "Feature",

The first entry has a different structure than the other entries, see the feature collection. The last entry looks like this at the bottom.

         ]
        }
      }
    ]
  }

I have an userform with 18 labels and 18 textboxes. 16 textboxes for the properties and 2 textboxes for latitude and longitude.

I might change one or more of the textboxes to combo boxes.

How do I create the GeoJSON structure with VBA? Since the beginning and the end are different I don't know where to start?

[waimea]

I have googled a lot and I found this page http://excelerator.solutions/2017/08...son-using-vba/ with the following code:

Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim i As Long
Dim cell As Variant

Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
jsonDictionary("id") = Cells(i, 1)
jsonDictionary("name") = Cells(i, 2)
jsonDictionary("username") = Cells(i, 3)
jsonDictionary("email") = Cells(i, 4)
jsonDictionary("street") = Cells(i, 5)
jsonDictionary("suite") = Cells(i, 6)
jsonDictionary("city") = Cells(i, 7)
jsonDictionary("zipcode") = Cells(i, 8)
jsonDictionary("phone") = Cells(i, 9)
jsonDictionary("website") = Cells(i, 10)
jsonDictionary("company") = Cells(i, 11)

jsonItems.Add jsonDictionary
Set jsonDictionary = Nothing
Next i

MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)

This code creates a JSON file, I want to create a GeoJSON file.

How can I adapt this so that the first entry and the last entries are different?

[waimea]

Any suggestions on where to start? I need a hint?

[snb]

I have an userform with 18 labels and 18 textboxes. 16 textboxes for the properties and 2 textboxes for latitude and longitude.

So why don't you post it here ?