Dictionary to JSON question

[kchen12]

Hi Tim,

Thank you so much for the converter! This has been such a great help! I have a few question about the converter and can't seem to figure it out, and I'm just wondering if you could help me out.

1.)
I have a dictionary data structure that contains about 70k objects(these objects only contain 2 strings). Regarding the ConvertToJson function, when I use the dictionary you provided (mac-support), it took about 8 seconds to convert, but when I use the windows scripting dictionary it took 60 seconds. I was wondering if you could shed some light on this.

2.)
The reason I asked the first question is because in order to create the 70k dictionary structure, I extracted the data from a spreadsheet, and when I created the dictionary structure using the mac-support dictionary , the .add function took significantly longer. When creating the dictionary structure, the mac-support dictionary took around 30 seconds, whereas the windows scripting dictionary was almost instant.

I'm at a dilemma here because I'm between 2 options of choosing a 40 seconds convert and a 60 seconds convert, of which both are not ideal. I am in the process of finding a way to maybe use the scripting dictionary for the first half of the program and convert it to the mac-support dictionary for the ConvertToJson part. However, I do want to avoid this because there will be mac users using the macro I'm writing. Any help will be much appreciated.

Thank you,

Kevin

Edit: From what I can see, the mac support dictionary is basically the same as the scripting dictionary with preprocessed platform check. From what I understand the performance should be about the same, so I'm pretty stumped here.

[timhall]

Hi @kchen12 you're right that for Windows, VBA-Dictionary just calls Scripting.Dictionary underneath, but there is a definite (but generally minor) performance penalty (there are some performance tests in VBA-Dictionary specs). I haven't seen a case where VBA-Dictionary is faster than Scripting.Dictionary, so if you have a specific test case that would be interesting to see.

I looked through VBA-JSON and New Dictionary only occurs in one place so it would be pretty easy for you to move the platform check to that location and avoid using VBA-Dictionary on Windows (to skip the dispatch penalty):

#If Mac Then
  Set json_ParseObject = New Dictionary
#Else
  Set json_ParseObject = CreateObject("Scripting.Dictionary")
#End If

(I have no plans to add this, but you could do it in your performance-critical code and see if it helps)