Nested Dictionary conversion causes crash at exit sub

[PlayfulPiano]

I'm currently using the VBA JSON converter for a project I'm working on, but due to the size of the JSON string (the actual saved file is ~43kb), vba crashes after closing the subroutine which contains the converted string.

Note that it does properly print the converted string with no issues, it just crashes after leaving the sub which had the string stored. And what is being converted is a dictionary object whose values also are dictionaries, which could be nested a couple layers deep.

My assumption is that it's because the string length is too big, causing the memory to not properly remove it and leading to the crash. I tried to figure out how to make the converter either print after a specific string length or try to only have it convert each key at a time, but it either caused errors or formatting issues.

Does anybody know either how to adjust the JSON conversion module to setup batch printing after either a character length limit or having it print throughout the nesting process, so it isn't stored all at once?

[houghtonap]

VBS-JSON probably is not a good match for your project due to the size of your Json and the fact that VBA-JSON builds an in-memory representation of the Json.

For large Json processing investigate using PowerQuery (Excel 2016+), PowerQuery Desktop, SQLite with Json UDF, MySQL, Postgres or other SQL databases with Json processing capabilities.

[PlayfulPiano]

VBS-JSON probably is not a good match for your project due to the size of your Json and the fact that VBA-JSON builds an in-memory representation of the Json.

For large Json processing investigate using PowerQuery (Excel 2016+), PowerQuery Desktop, SQLite with Json UDF, MySQL, Postgres or other SQL databases with Json processing capabilities.

Would you possibly also have any suggestions regarding the creation and storage of nested dictionaries? Basically my situation is that I have a table with a series of recipes, and I want to both store that recipe data into a json file for faster data grabbing & the ability to preserve that data outside of excel / not require the data to forcefully regenerate every time I need to reference that data, and then afterwards create nested recipes from the same data which considers cases when an ingredient is an intermediate and has its own ingredients.

Currently I have it set up where I create a dictionary / .json for the table data as standalone recipes, then I create a dictionary / .json for nested recipes using the prior dictionary as a data source. Eventually I want to be able to set it up where it can just read the .json file for grabbing the data over making a dictionary, and then create a flowchart inside excel which allows the user to swap between different recipes.

[DecimalTurn]

@PlayfulPiano when you say that it crashes, do you mean the host application crashes (ie. Excel) or are you getting an error message and/or state loss?

Also, do you have a reproducible example?

[PlayfulPiano]

@PlayfulPiano when you say that it crashes, do you mean the host application crashes (ie. Excel) or are you getting an error message and/or state loss?

Also, do you have a reproducible example?

Excel and VBA stop responding and I have to force close it. It's reproducible in that my code can generate the large json string, store the string, and print/ save the string to a file no problem at manual stepping, but the moment it steps out of the subroutine it stops responding. The data I use is otherwise sensitive.

[PlayfulPiano]

I should be able to upload the module code though tomorrow

[DecimalTurn]

Excel and VBA stop responding and I have to force close it. It's reproducible in that my code can generate the large json string, store the string, and print/ save the string to a file no problem at manual stepping, but the moment it steps out of the subroutine it stops responding. The data I use is otherwise sensitive.

Hum, based on this, I would assume that there is a problem during the garbage collection phase. Maybe setting some of the dictionaries manually to nothing before the Exit Sub could help.

[PlayfulPiano]

Here is the gist for my module & a custom class I created called ItemData. I am otherwise using the VBA-JSON module & the FastDictionary class. https://gist.github.com/PlayfulPiano/2d1d137b349ea30800e01582d459467d (note: ExBOM is the nested dictionary creation, and I have that as well as the save to json for it disabled currently)

[DecimalTurn]

Oh, you are using VBA-FastDictionary? Does the crash occur when you use a regular Scripting.Dictionary or VBA-Dictionary?

[PlayfulPiano]

Oh, you are using VBA-FastDictionary? Does the crash occur when you use a regular Scripting.Dictionary or VBA-Dictionary?

I didn't use the regular version / vba dictionary iirc because it was having trouble recognizing a part of my code, albeit I can't remember what it was exactly.

For right now though, I'm instead just not going to store the tree dictionary at all as a .json and instead store the flowchart itself, which should be a lot less complex.

[DecimalTurn]

Looking at the gist, there isn't anything that seems crash worthy in the code. Having sample data would make it easier to reproduce.