Fancy a little quiz about parsing JSON texts in VBA?

Here are five questions related to parsing JSON texts in VBA. If you can answer all questions correctly at once, we sincerily congratulate you! Parsing JSON texts in VBA has little secrets left for you. If you do not know the answer to all questions, please don't worry. It is absolutely normal. This is why we have built this website! We hope that the website will help you to develop your knowledge about parsing JSON texts in VBA. Enjoy!

Question 1

  1. Is the following JSON text valid?
  2. If not, why not? If yes, can it be parsed into VBA?
{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":{"JSON":"VBA"}}}}}}}}}}
Answer to Question 1

Answer to Question 1

  1. Yes, the JSON text is valid. Nesting, to whatever depth should not be a problem. Most JSON texts will use nesting, giving depth to the JSON text. For example, in the JSON text above, the depth is 10. Nesting is a key strength of the JSON format and often facilitates the data exchange. In practice however, we have never come across a JSON text that has a depth of more than, say 20. It is hard to imagine that JSON texts with depths of 1000, 10000 or 100000 would be the most efficient way to exchange data. Nevertheless, the JSON format allows it.
  2. The JSON text can be parsed into VBA. All VBA JSON parsers should be able to do this. The exchange of data between VBA an the JSON text should be perfect. Here is the VBA code, using our JSON parser.

    Just for fun, we have tried to parse a JSON text with depth 1000 in VBA.

    {“JSON”:{“JSON”:{“JSON”: …1000 times in total… {“JSON”:”VBA”} …1000 times in total… }}}

    It works fine. And VBA parsing is still super fast!

Question 2

  1. Is the following JSON text valid?
  2. If not, why not? If yes, can it be parsed into VBA?
{
"JSON":"VBA1",
"JSON":"VBA2"
}
Answer to Question 2

Answer to question 2

  1. Yes, the JSON text is valid. Even though the name “JSON” occurs twice at the same depth in the JSON text. In the definition of the format The JSON Data Interchange Format, Standard ECMA-404, 1st Edition / October 2013, the possibility of equal names (in a name/value pair) at the same depth of the JSON text, has not been forbidden. To us, this was slightly suprising. In practice, it probably doesn’t make a big difference. Until now, we have never come across a real-life JSON text that had two equal names at the same depth. As the main purpose of the JSON format is to exchange data, common sense implies that every data item in the JSON text should have a unique path in the JSON text, the path being the sequence of names that leads to the data item. If there are multiple data item with the same path, there is a data extraction problem. In the JSON text above, suppose you want to get the data item associated to the name “JSON”? Which one is it? Both “VBA 1” or “VBA 2” are correct.
  2. The question whether of not this JSON text can be parsed into VBA cannot be answered in general. It depends on the JSON parser that you are using. The JSON parser on this website maps the data that is in the JSON text into a VBA dictionary. As duplicate keys are not allowed inside a VBA dictionary, our JSON parser is not able to parse this JSON text. Our JSON parser will generate an error. It is not unthinkable that there exists JSON parsers in VBA that can do it (although we haven’t seen one that can do this).

Question 3

  1. Is the following JSON text valid?
  2. If not, why not?If yes, can it be parsed into VBA?
{
"JSON1":"VBA",
"JSON2":"VBA"
}
Answer to Question 3

Answer to question 3

  1. Yes, the JSON text is valid. JSON values at the same depth can be the same. Of course. Also the following JSON text is perfectly valid.
    [
    “VBA”,
    “VBA”,
    “VBA”,
    “VBA”,
    “VBA”
    ]

    This might be less exotic than you think. Imagine you need to represent the following table of data in a JSON text.

    JanFebMarAprMayJunJulAugSepOctNovDec
    Player 1024130011221
    Player 2001000000020

    One possible JSON text that can exchange this data is as follows. It has many JSON values, at the same depth, that are the same.

    {
    “Player 1”:[0,2,4,1,3,0,0,1,1,2,2,1],
    “Player 2”:[0,0,1,0,0,0,0,0,0,0,2,0]
    }
  2. The JSON text can be parsed into VBA. All VBQ JSON parsers should be able to do this. The exchange of data between VBA an the JSON text should be perfect. Here is the VBA code, using our JSON parser.

Question 4

Is the following JSON text valid? How would you check?

{
"Can":"This
",[
"JSON","Text
","Be","P
A
R
S
ED","into",
"VBA",
"?"]
}
Answer to Question 4

Answer to question 4

This is a valid JSON text. One way of checking is to try to parse the JSON text using our VBA parser. Here is the VBA code.

The code does not return an error message, which means that the JSON is valid. Data can be extracted from the VBA dictionary, as normal. For example, when extracting the data item for the path “JSON”,”2″ the VBA code above returns this.

Image showing the result of parsing the JSON text in VBA

Another way to check is to copy the JSON text into a JSON validator. A popular and good JSON validator is JSONLint. However, even good JSON parsers can go wrong (ours not excluded). According to JSONLint this valid JSON text is invalid. The JSON validator has trouble to deal with the (strangely placed) end-of-line characters. Apart from that, it remains an excellent JSON validator. We use it a lot.

Question 5

  1. Is the following JSON text valid?
  2. If not, why not? If yes, can it be parsed into VBA?
{"Number":12345678901234567890.12345678901234567890}
Answer to Question 5

Answer to question 5

  1. Yes, the JSON text is valid. The format of the number matches the JSON format.
  2. It is possible to parse the JSON text into VBA, however, the data exchange with VBA cannot be perfect (unless you use custom number types in VBA). This is due to the fact that VBA has no numeric type that can hold the number 12345678901234567890.12345678901234567890 without loss of precision. Our JSON parser does parse the JSON text, and, the result seems fine. There is no error message. However, our JSON parser has cheated a little bit because it has put the number 12345678901234567890.12345678901234567890 into a variable of the type String. The default number conversion strategy of our JSON parser is to try VBA conversions until it works: first to Long, then to Double and if also that fails to String. Hence, parsing will always work, however, the downside is that the data exchange with VBA need not be perfect. Obviously, to be perfect, the data exchange should not change the type of the data during conversion (which is happening here). The value 12345678901234567890.12345678901234567890 will be available in VBA, however – unless you use custom number types – it cannot be used in VBA as a number. Just FYI, our JSON parser offers other number conversion strategies that will generate an error if the conversion to VBA is not perfect.