1

Background: I am currently using VBA-JSON to parse json strings to dictionary objects in VBA (Access). This is quite slow, a sample process takes 18 seconds.

In VB.NET, the JavaScriptSerializer Deserialize method takes .5 seconds for the same data.

I want the performance of the VB.NET method available to my VBA code via COM Interop. But COM cannot pass generic objects and although I have read that the solution involves marshaling, I am having trouble understanding that option.

I can successfully pass a scripting.dictionary type from my VB.NET COM class when I manually generate it and can use it in VBA.

    Public Function GetData2() As Scripting.Dictionary

        Dim dict As New Scripting.Dictionary

        dict.Add("a", "Athens")
        dict.Add("b", "Belgrade")
        Return dict

    End Function

But the JavaScriptSerializer Deserialize method returns a type IDictionary, not a scripting.dictionary.

So I must either find a way to deserialize json to a scripting.dictionary or convert the IDictionary to a scripting.dictionary.

How can I do this?
Given my overall goal, are there any suggestions about alternate methods?

Edit.

The project uses a proprietary REST API for an accounting system. I want to create generic tools to simplify and speed up a variety of different tasks, from environments including Access, Excel, vbscript etc. Everything the API designers intended but from tools not normally friendly to REST API programming.

The uses could range from reading and writing data to and from the system, or loading data into another database, create custom reports in excel, import orders etc.

Here is some JSON for a sales order.

{
  "id": 7,
  "orderNo": "0000102692",
  "division": "000",
  "location": "",
  "profitCenter": "",
  "invoiceNo": "",
  "customer": {
    "id": 1996,
    "code": "ER118",
    "customerNo": "ER118",
    "name": "E R Partridge Inc"
  },
  "currency": null,
  "status": "O",
  "type": "O",
  "hold": false,
  "orderDate": "2015-02-13",
  "invoiceDate": null,
  "requiredDate": "2015-02-13",
  "address": {
    "id": 2045,
    "type": "B",
    "linkTable": "SORD",
    "linkNo": "0000102692",
    "shipId": "",
    "name": "E R Partridge Inc",
    "line1": "1531 St Jean Baptiste St",
    "line2": "",
    "line3": "",
    "line4": "",
    "city": "St Ulric",
    "postalCode": "G0J 3H0",
    "provState": "QC",
    "country": "CAN",
    "phone": {
      "number": "4187370284",
      "format": 1
    },
    "fax": {
      "number": "",
      "format": 1
    },
    "email": "[email protected]",
    "website": "",
    "shipCode": "",
    "shipDescription": "",
    "salesperson": {
      "code": "",
      "name": ""
    },
    "territory": {
      "code": "",
      "description": ""
    },
    "sellLevel": 1,
    "glAccount": "41100",
    "defaultWarehouse": "VA",
    "created": "2014-08-26T11:44:57.930000",
    "modified": "2015-02-16T09:30:08",
    "contacts": [
      {
        "name": "Van Coon",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      },
      {
        "name": "",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      },
      {
        "name": "",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      }
    ],
    "salesTaxes": [
      {
        "code": 1,
        "exempt": ""
      },
      {
        "code": 2,
        "exempt": ""
      },
      {
        "code": 0,
        "exempt": ""
      },
      {
        "code": 0,
        "exempt": ""
      }
    ]
  },
  "shippingAddress": {
    "id": 2044,
    "type": "S",
    "linkTable": "SORD",
    "linkNo": "SORD0000102692          S",
    "shipId": "",
    "name": "E R Partridge Inc",
    "line1": "1531 St Jean Baptiste St",
    "line2": "",
    "line3": "",
    "line4": "",
    "city": "St Ulric",
    "postalCode": "G0J 3H0",
    "provState": "QC",
    "country": "CAN",
    "phone": {
      "number": "4187370284",
      "format": 1
    },
    "fax": {
      "number": "",
      "format": 1
    },
    "email": "",
    "website": "",
    "shipCode": "",
    "shipDescription": "",
    "salesperson": {
      "code": "",
      "name": ""
    },
    "territory": {
      "code": "",
      "description": ""
    },
    "sellLevel": 1,
    "glAccount": "41100",
    "defaultWarehouse": "VA",
    "created": "2014-08-26T11:44:57.930000",
    "modified": "2014-08-26T11:44:57.930000",
    "contacts": [
      {
        "name": "Van Coon",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      },
      {
        "name": "",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      },
      {
        "name": "",
        "email": "",
        "phone": {
          "number": "",
          "format": 1
        },
        "fax": {
          "number": "",
          "format": 1
        }
      }
    ],
    "salesTaxes": [
      {
        "code": 1,
        "exempt": ""
      },
      {
        "code": 2,
        "exempt": ""
      },
      {
        "code": 0,
        "exempt": ""
      },
      {
        "code": 0,
        "exempt": ""
      }
    ]
  },
  "contact": {
    "name": "",
    "email": "",
    "phone": {
      "number": "",
      "format": 0
    },
    "fax": {
      "number": "",
      "format": 0
    }
  },
  "customerPO": "",
  "batchNo": 0,
  "fob": "Your dock",
  "referenceNo": "",
  "shippingCarrier": "",
  "shipDate": null,
  "trackingNo": "",
  "termsCode": "",
  "termsText": "",
  "freight": "41.95",
  "taxes": [
    {
      "code": 1,
      "name": "G.S.T.",
      "shortName": "G.S.T.",
      "rate": "5",
      "exemptNo": "",
      "total": "44.05"
    },
    {
      "code": 2,
      "name": "P.S.T.",
      "shortName": "BC P.S.T.",
      "rate": "7",
      "exemptNo": "",
      "total": "61.67"
    },
    {
      "code": 0,
      "name": "",
      "shortName": "",
      "rate": "0",
      "exemptNo": "",
      "total": 0
    },
    {
      "code": 0,
      "name": "",
      "shortName": "",
      "rate": "0",
      "exemptNo": "",
      "total": 0
    }
  ],
  "subtotal": "839",
  "subtotalOrdered": "839",
  "discount": "0",
  "totalDiscount": "0",
  "total": "986.67",
  "totalOrdered": "986.67",
  "grossProfit": "346.26",
  "items": [
    {
      "id": 8,
      "orderNo": "0000102692",
      "sequence": 1,
      "inventory": {
        "id": 40,
        "whse": "VA",
        "partNo": "INSDB30",
        "description": "InSpire Dumbbell 30"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "INSDB30",
      "description": "InSpire Dumbbell 30",
      "comment": "",
      "orderQty": "4",
      "committedQty": "4",
      "backorderQty": "0",
      "retailPrice": "70",
      "unitPrice": "70",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "INSPIRE",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "280",
      "extendedPriceCommitted": "280",
      "suppress": false
    },
    {
      "id": 9,
      "orderNo": "0000102692",
      "sequence": 2,
      "inventory": {
        "id": 27,
        "whse": "VA",
        "partNo": "NATACCBAL",
        "description": "National Accupressure Balls"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "NATACCBAL",
      "description": "National Accupressure Balls",
      "comment": "",
      "orderQty": "5",
      "committedQty": "5",
      "backorderQty": "0",
      "retailPrice": "22",
      "unitPrice": "22",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "NATPRO",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "110",
      "extendedPriceCommitted": "110",
      "suppress": false
    },
    {
      "id": 10,
      "orderNo": "0000102692",
      "sequence": 3,
      "inventory": {
        "id": 33,
        "whse": "VA",
        "partNo": "SPAB",
        "description": "Springfield Ab Toner"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "SPAB",
      "description": "Springfield Ab Toner",
      "comment": "",
      "orderQty": "1",
      "committedQty": "1",
      "backorderQty": "0",
      "retailPrice": "45",
      "unitPrice": "45",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "SPRFIT",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "45",
      "extendedPriceCommitted": "45",
      "suppress": false
    },
    {
      "id": 11,
      "orderNo": "0000102692",
      "sequence": 4,
      "inventory": {
        "id": 46,
        "whse": "VA",
        "partNo": "INSDB50",
        "description": "InSpire Dumbbell 50"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "INSDB50",
      "description": "InSpire Dumbbell 50",
      "comment": "",
      "orderQty": "2",
      "committedQty": "2",
      "backorderQty": "0",
      "retailPrice": "118",
      "unitPrice": "118",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "INSPIRE",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "236",
      "extendedPriceCommitted": "236",
      "suppress": false
    },
    {
      "id": 12,
      "orderNo": "0000102692",
      "sequence": 5,
      "inventory": {
        "id": 42,
        "whse": "VA",
        "partNo": "INSDB15",
        "description": "InSpire Dumbbell 15"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "INSDB15",
      "description": "InSpire Dumbbell 15",
      "comment": "",
      "orderQty": "3",
      "committedQty": "3",
      "backorderQty": "0",
      "retailPrice": "34",
      "unitPrice": "34",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "INSPIRE",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "102",
      "extendedPriceCommitted": "102",
      "suppress": false
    },
    {
      "id": 13,
      "orderNo": "0000102692",
      "sequence": 6,
      "inventory": {
        "id": 9,
        "whse": "VA",
        "partNo": "INSWP50",
        "description": "InSpire Weight Plate 50"
      },
      "serials": null,
      "whse": "VA",
      "partNo": "INSWP50",
      "description": "InSpire Weight Plate 50",
      "comment": "",
      "orderQty": "1",
      "committedQty": "1",
      "backorderQty": "0",
      "retailPrice": "66",
      "unitPrice": "66",
      "discountable": true,
      "discountPct": "0",
      "discountAmt": "0",
      "taxFlags": [
        true,
        true,
        false,
        false
      ],
      "sellMeasure": "EA",
      "vendor": "INSPIRE",
      "levyCode": "",
      "requiredDate": "2015-08-26",
      "extendedPriceOrdered": "66",
      "extendedPriceCommitted": "66",
      "suppress": false
    }
  ],
  "payments": [
    
  ],
  "createdBy": "SS",
  "modifiedBy": "SS",
  "created": "2014-08-26T11:44:57.930000",
  "modified": "2015-02-20T08:09:55",
  "links": {
    "notes": "https://localhost:10880/api/v2/companies/INSPIRE/sales/orders/7/notes/"
  }
}

EDIT 2

Answer by Erik A showed me how parsing can be done on-demand or 'streaming'. The parsing is only done when you ask for the element.

I think I have misunderstood the nature of how VB.NET deserializes JSON. It must be doing the same thing. So when I saw 500 ms instead of 18 seconds I didn't know what I was looking at. I suspect that if I walked through the de-serialized json in VB.NET and inspected every element, it would take much longer. My sample data for performance testing was actually a collection of 124 of the JSON sample, some with a lot more ITEMS, hence the 18 seconds. Is this correct?

Albert's answer showed me something I wanted to do originally, but couldn't get it to work. Great complete answer I will be digging into next.

Improve this question
11
  • You can convert the .NET's IDictionary into a Scripting.Dictionary in the .NET COM object, since it has access to both types (you'll have to do this recursively of course). Commented Mar 17, 2019 at 16:29
  • Can you share the operations you want to do, and the sample JSON data? VBA-JSON has it's limitations, I've been working on an alternative that's not fully tested yet but might suit your needs. Commented Mar 17, 2019 at 16:35
  • But does it matter? You can use type object and pass that back. But why not pass a DAO recordset to your .net routine, and have the .net code write out the data to the table? It not clear where/when you use the json data, but I would assume you have a json string, and want to un-wind that string into a Access table, right? edit: You can also pass back from .net a collection as a type class you defined in VBA. Or you can pass back a type of iList which is a decent data type to work with in VBA. Commented Mar 17, 2019 at 16:48
  • Why not let vb.net create the class for you? Create a class in vb.net (but then delete all the text. Now cut the Jason from a text file, and choose edit->paste special, choose json. VB.net will generate the class based on that Jason for you. Now, you can create 4 line class in vb.net that takes the string and returns the class to VBA. The beauty of this is you actually get intel-sense in the VBA with all of the columns of the json data. Edit your post and provide a sample json string. I'll post working vb code that can be consumed in Access. Commented Mar 17, 2019 at 17:51
  • My first attempts Albert were to create a full model of the json structure as a class in VB.NET. The json is a deep structure and I found (from another post in this site) that I can't implement sub-classes in the way I wanted. So could not for example reference "company.invoice(1).ShipAddress.AddressLine(2)" from VBA with intellisense all the way along. I going to try your solution to the passing the Dictionary object. Commented Mar 18, 2019 at 15:16

2 Answers 2

Reset to default
1

I've personally been working on a fast, flexible JSON interpreter for VBA that might suit your needs.

It's built up with two different objects: clsStringBuilder (a very basic string builder) and JSONInterpreter (the main object that allows you to work with JSON).

You can find the project here. Note that I've quickly uploaded it for this question, it lacks a whole lot at this moment (documentation, testing, etc).

Sample code:

Dim jsi As New JSONInterpreter
jsi.JSON = SomeString
Debug.Print jsi.item("company", "invoice", 1, "ShipAddress", "AddressLine", 2).VBAVariant

If you're working a lot with a specific array or object within a larger object, I recommend retrieving the VBAVariant property and working further with that, or else creating a subobject.

Example:

Dim jsi As New JSONInterpreter
jsi.JSON = SomeString
Dim shipAddressJSI as JSONInterpreter
Set shipAddressJSI = jsi.item("company", "invoice", 1, "ShipAddress")
Debug.Print shipAddressJSI.item("AddressLine", 1).VBAVariant
Debug.Print shipAddressJSI.item("AddressLine", 2).VBAVariant

Note that if you consider using this, I strongly recommend doing some testing first. The WalkJSON sub found in the sample implementation can help with that.

A quick performance test for the JSON document you've shared shows that moving the entire document (when stored in a worksheet cell in Excel) to a dictionary takes between 0.08 and 0.11 seconds on my system (which is low-end at best). There certainly can be a performance gain when moving only part of the document to a dictionary, especially if you can use position (instead of key name) to specify which part you want.

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

9 Comments

Thanks for posting your code. I have checked it out and am learning lots from it. I didn't understand at first what 'streaming' nature of your code means. What I think it means is that no parsing is done until you call for an element. If that's the case it's a very cool option to have.
Thanks for posting your code. I have checked it out and am learning lots from it. I didn't understand at first what 'streaming' nature of your code means. What I think it means is that no parsing is done until you call for an element. I took your walkthough code and removed the debugs and ran it on the JSON. Your code 0.152 seconds. VBA-JSON 0.066 seconds. VBA-JSON seems faster if i want all the data. Your code is faster if I want just certain elements. I can see a need for both. Thanks for taking the time and sharing your project. I have more to say but ran out of characters.
The streaming nature is mainly that it doesn't parse the entire file. It starts at the start, then reads what it needs, increasing speed when you don't need to read the entire file (since the end isn't parsed). The WalkJSON code is very, very inefficient, it's mainly a way to verify the entire file can be read correctly. If you need the entire file as a dictionary, just use .VBAVariant after loading the file. In my benchmarks, properly using my code is way faster than VBA-JSON in any workload, but if you share your actual workload I can verify.
Note that my approach is noncaching. If you need to read multiple elements from a large file, it gets more efficient to just move the entire thing to a dictionary very fast.
Sample workload json: workload
|
0

Why not just use the .net dict? You likely only ever need the count, and to set, or pull by the key.

So, this code should do just fine:

Imports System.Runtime.InteropServices
Imports Newtonsoft.Json

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class MyJSON

   Private m_DICT As New Dictionary(Of String, String)

   Public Function ToJson() As String
       Dim s As String = ""
       s = JsonConvert.SerializeObject(m_DICT)
       Return s
   End Function

   Public Sub JsonToDict(s As String)
       m_DICT = JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(s)
   End Sub

   Public Sub Add(sKey As String, sValue As String)
       m_DICT.Add(sKey, sValue)
   End Sub

   Public Function ix(s As String) As String
       Return m_DICT(s)
   End Function

   Public Function Count() As Integer
       Return m_DICT.Count
   End Function

End Class

Just make sure you set the above project to x86. Check the box to register for com interop, and you off to the races. I used NewtonSOFT json, and it not clear what serializer library you are using.

So, now your VBA code becomes:

Sub TEstMyCom()

  Dim MyJSON  As New TestCom2.MyJSON
   
  MyJSON.Add "a", "aaaaa"
  MyJSON.Add "b", "bbbbb"
  MyJSON.Add "c", "ccccc"
      
  Dim ss As String
  ss = MyJSON.toJSON
  
  Debug.Print MyJSON.toJSON
  
  ' convert the string to array (dict)
  
  Dim MyJSON2 As New TestCom2.MyJSON
 
  MyJSON2.JsonToDict ss
 
 Debug.Print MyJSON2.ix("c")
 
End Sub

Output:

{"a":"aaaaa","b":"bbbbb","c":"ccccc"}
ccccc

Note that even inteli-sense in the VBA editor will work with above. So just expose a few extra methods to work with the .net dict, and you not even have to bother with the scripting library in VBA - and that library is a bit of a pain to use anyway. So you can get rid of one library reference by simply using the .net dict object as I did above.

Edit

Now that the user has provided sample json?

To work with the given data, the steps are.

Create a new blank class in .net ctrl-a, del key. With this empty class, we have the sample json in a text document. ctrl-a, ctrl-c. Now in Visual Studio (VS), edit->paste special, PASTE AS JSON Class.

At this point, your classes are auto generated for you. Due to a limitation with Neutonsoft parser, it does not support arrays(). (so sad I might add).

So, do a search for (), and you find the arrays, and replace them.

So

Public Property taxes() As Tax

Becomes

Public Property taxes As IList(Of Tax)

We use iList in place of List because we want read/write ability here. And as I stated, we are ONLY doing this work because NeutonSoft simple does not like arrays.

Ok, there is about 4 of the above. This takes less then 1 minute of your time to modify.

However, one needs to re-expose the above if one wants REALLY nice intel-sense in VBA. So, lets add some classes for these ilists.

Again, there are only about 4 of these. So we have this:

Public Property salesTaxes As IList(Of Salestax)

Public Property salesTaxesN(ix As Integer) As Salestax
    Get
        Return salesTaxes(ix)
    End Get
    Set(s As Salestax)
        salesTaxes(ix) = s
    End Set

End Property

The above will give us nice intel-sense. We did not have to do above, but for the extra 2 minutes, we can now walk the data in intel-sense in VBA.

And we need a count (while iList appears to VBA, it for some reason does not expose count). I am open to other suggestions, but lets just add this right after above:

Public Function salesTaxesNCout() As Integer

    Return salesTaxes.Count

End Function

Having done the above changes? We are still well under 5 minutes of time. If you used the paste as json feature in VS, then the more times you do the above, the better you get at making these changes. You find as noted, well under 5 minutes of time.

Again due to wanting intel-sense in VBA, paste in this before each class

<ClassInterface(ClassInterfaceType.AutoDual)>

Now this is a fast paste over and over. So here is what the code will look like (sample snip from our class).

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class Salesperson
    Public Property code As String
    Public Property name As String
End Class
<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class Territory
    Public Property code As String
    Public Property description As String
End Class

The above is just a short snip. Again, this goes real fast.

Ok, we are done!

Our main class now looks like this:

imports System.Runtime.InteropServices
imports Newtonsoft.Json

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class MyJSON

   Public MyCust As New Jcust

   Public Function ToJson() As String

       Dim s As String = ""
       s = JsonConvert.SerializeObject(MyCust)
       Return s

   End Function

   Public Sub JsonToCust(s As String)

       MyCust = JsonConvert.DeserializeObject(Of Jcust)(s)

   End Sub

End Class

That is it! You now have a great working setup.

Our VBA code to play with above is now this:

Sub custTest()

  Dim strJSON    As String
  Dim intF       As Integer
  Dim strF       As String
  strF = "c:\test2\cust.txt"
  ' read in that file
  intF = FreeFile()
  Open strF For Input As #intF
  strJSON = input(LOF(intF), intF)
  Close intF
  
  Dim cCust As New MyJSON
      
  cCust.JsonToCust strJSON
  
  Debug.Print cCust.MyCust.Address.salesTaxesN(1).Code
  
  Debug.Print cCust.MyCust.Address.City
  
  
End Sub

running the above? output:

 2 
St Ulric

I should point out that intel-sense works all the way down.

Now I suppose the real question is if anyone has a idea how to make Newtonsoft work with Arrays, then we would have this done in 2 minutes flat, in place of the 5 minutes that this took me.

In fact I been meaning for about 1 year now to ask that question, and I will later today. And in above I did rename rootobject as MyCust.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.