Pulling data in a for loop where data sometimes does not exist

Question

Let's say I have four data values and one of them exists sometimes.
My For loop crashes because the path doesn't exist.
I would like to pass a "" in the cell instead of crashing.

myJSON.data[i].bank[0].money <- this part is my problem, because the bank[0].money sometimes doesn't exist.
I would like to keep the cell empty.
I tried an If but I didn't get it formatted properly, same goes for error handling.

Sub DATA()
    Set RDict = CreateObject("Scripting.Dictionary")
    Set dlist = CreateObject("Scripting.Dictionary")
    JSON_String = Form.fromURL("exampleurl")
    With CreateObject("htmlfile")
        With .parentWindow
            .execScript "var myJSON = " & JSON_String & ", csvstring = '';for (i = 0; i < myJSON.data.length; i++) {csvstring += myJSON.data[i].name + ',' + myJSON.data[i].bank[0].money + ',' + myJSON.data[i].location + ',' + myJSON.data[i].planneddate + ';';};"
            RData = Split(.csvstring, ";")
        End With
    End With
    For i = 0 To UBound(RData) - 1
        DaData = Split(RData(i), ",")
        If DaData(0) <> "null" Then RDict(DaData(0)) = DaData
    Next i
    Dim RSheet() As Variant
    If RDict.Count > 0 Then
        ReDim RSheet(2 To RDict.Count + 2, 1 To 7)
        i = 0
        For Each D In RDict
            datalist(RDict(Da)(2)) = True
            For j = 0 To 6
                RSheet(i + 2, j + 1) = RDict(Da)(j)
            Next j
            i = i + 1
        Next Da
        RSData.Cells(2, 1).Resize(i, 6) = RSheet
    End If

End Sub

No it seems like this "myJSON.data[i].bank[0].money" is the problem, because the path does not exsist allways — user18609963, Jun 13 '22 at 15:53
Good rule of thumb on SO - It's always helpful if you show your code you tried — dbmitch, Jun 13 '22 at 16:13
Oh i didnt know that, i will do it in the further thank you! — user18609963, Jun 13 '22 at 17:15
It would be easier I think to use the VBA-JSON library to parse the JSON. https://github.com/VBA-tools/VBA-JSON Even if you're comfortable in jscript, this hybrid approach feels a bit clunky. — Tim Williams, Jun 13 '22 at 17:52

dbmitch · Answer 1 · 2022-06-13T17:47:54.103

0

You can handle null by using optional chaining with default nullish coalescing (#3 in example).

Something like this should work

Change myJSON.data[i]?.bank[0]?.money

To myJSON.data[i]?.bank[0]?.money ?? 'Unknown'

You can do the same with your other variables (myJSON.data[i].location and myJSON.data[i].planneddate) if they have the potential to be undefined or null as well

EDIT - Use Optional IF when optional chaining is not available

If that feature is not available in HTMLDocument's javascript maybe you can use basic conditional if?

This should work for undefined object, because undefined is == null

(myJSON.data[i].bank[0].money != null ? myJSON.data[i].bank[0].money : '-')

edited Jun 13 '22 at 17:47

answered Jun 13 '22 at 16:16

dbmitch

5,361
4
24
38

I changed it but now i get this error "-2147352319 (80020101)" – user18609963 Jun 13 '22 at 17:12
Do you have a javascript console you can do some troubleshooting in? `exampleurl` doesn't give us anything to work with – dbmitch Jun 13 '22 at 17:37
Perhaps that feature is not available in Microsoft HTMLDocument javascript? Try the basic conditional if in my edited answer – dbmitch Jun 13 '22 at 17:48
1

ECMAScript version support in `htmlfile` - https://stackoverflow.com/questions/65207848/using-htmlfile-com-object-to-access-the-getownpropertydescriptor-method-of-an – Tim Williams Jun 13 '22 at 17:49
https://caniuse.com/mdn-javascript_operators_logical_nullish_assignment – dbmitch Jun 13 '22 at 18:08
Okay the If condition worked, thank you very much this wars bothering me for a couple of days :) – user18609963 Jun 14 '22 at 12:26
Can you mark it as the answer then (with the checkmark) or at least upvote as helpful? – dbmitch Jun 14 '22 at 15:47

Pulling data in a for loop where data sometimes does not exist

1 Answers1