search

VBA-tools / VBA-Web

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web
http://vba-tools.github.io/VBA-Web/
MIT License
2.01k stars 494 forks source link

JSON Parsing error excel 2016 #345

Closed duncanelliot closed 6 years ago

duncanelliot commented 6 years ago

Hello there,

I am experiencing some weird behaviour depending on which version of Excel I run the code on. The purpose of the code is to authenticate and then retrieve three tables of data in JSON format.

The code ran fine for a while but then stopped working on my local machine which is running excel 2016 32bits. It still however ran fine on the 2010 32bit version of Excel which is also installed on my local machine. The code also ran fine on a 32 bit version of Excel 2016 hosted on an Azure Machine. Something happened and the code then stopped working on the VM.

I believe the issue lies in parsing the JSON into object using the JSON_ParseObject method. If i comment out the code that does the heavy lifting after a successful authentication, I am able to run the code in both versions of Excel on my local machine. However the JSON_ParseObject function takes in the order of 3 orders of magnitude longer to parse the response of the authentication on the 2016 version of excel.

I have ran a performance test and you can see the results for yourselves:

Tot Dur 4.43878   EXCEL 2016    
Project Module Proc Count Total Avg Max
Tipps WebHelpers json_ParseObject 2 4.21964 2.10982 2.34082
Tipps CRestCalls getReport1 1 0.21911 0.21911 0.21911
             
Tot Dur 0.57604   EXCEL 2010    
Project Module Proc Count Total Avg Max
Tipps WebHelpers json_ParseObject 2 0.00345 0.00172 0.00288
Tipps CRestCalls getReport1 1 0.57257 0.57257 0.57257

Can anybody help? I have been having sleepless nights over this...

Many thanks in advance.

zgrose commented 6 years ago

See if #341 helps you with a workaround

duncanelliot commented 6 years ago

@zgrose you are a legend mate!!!! I owe you a drink!