VBA: Can an array-item be split within the array itself? Attempting gives "Type Mismatch" error

Question

I'm attempting in Excel to split an array-item within the array itself, but without any luck at all. I'm wondering whether I will have to redim the array-item, but at the same time I'm not sure if that makes any sense or if it's even possible. One thing i've noticed is that the array-items are of string type in the array, which I'm also suspecting can be the source of the Type Mismatch error - this despite the array is Dim'ed as variant.

An example can be seen here:

Sub testarr()


    Dim arr As Variant, str As String, i As Integer

    str = "{test:[{test this here||Can it be split inside the array?}]}"

    arr = Split(str, "[{")

    For i = LBound(arr) To UBound(arr)
        arr(i) = Split(arr(i), "||")
    Next i
End Sub

Example of the wished structure of the array:

arr(i) is of type string. You have to use another variable of type variant. — Storax, Sep 17 '18 at 09:49

Storax · Accepted Answer · 2018-09-17T11:13:02.563

4

Though arr is dim'ed as variant the single elements become of type string as soon as you assign to them values like you did. You could do something like that

Sub testarr()


Dim arr As Variant, str As String, i As Integer
Dim v As Variant

    str = "{test:[{test this here||Can it be split inside the array?}]}"

    arr = Split(str, "[{")

    For i = LBound(arr) To UBound(arr)
        Debug.Print TypeName(arr(i))
        v = Split(arr(i), "||")
    Next i
End Sub

Update Based on the comments from the OP one could try that in order to replace the original array with the new splitted one

Sub testarr()

Dim arr As Variant, str As String, i As Integer
Dim v As Variant, s As String

    str = "{test:[{test this here||Can it be split inside the array?}]}"

    arr = Split(str, "[{")

    For i = LBound(arr) To UBound(arr)
        'Debug.Print TypeName(arr(i))
        v = Split(arr(i), "||")
        s = s & "||" & Join(v, "||")
    Next i

    arr = Split(s, "||")

End Sub

Update 2 Again, based on the OP's comment code which will return a structure described in the OP's post.

Sub testarr()

Dim arr As Variant, str As String, i As Integer
Dim v As Variant, s As String
Dim arr1 As Variant
Dim dic As New Scripting.Dictionary

    str = "{test:[{test this here||Can it be split inside the array?}]}"

    arr = Split(str, "[{")


    For i = LBound(arr) To UBound(arr)
       v = Split(arr(i), "||")
       If UBound(v) > 0 Then
           dic.Add i, v
       Else
           dic.Add i, arr(i)
       End If
    Next i



    ReDim arr1(dic.Count - 1)
    For i = 0 To dic.Count - 1
        arr1(i) = dic.Item(i)
    Next i

End Sub

edited Sep 17 '18 at 11:13

answered Sep 17 '18 at 09:51

Storax

11,158
3
16
33

1) is there a way to force the array items to be of type `variant` - 2) using this method, how would i return the new variable to the initial array and replace the array item of concern? I tried creating a string array instead, but that throws the same error when attempting to split an item. – Chri.s Sep 17 '18 at 09:56
ad 1) I do not know of and I am quite sure there is no way ad 2) Possibly with join – Storax Sep 17 '18 at 09:58
regarding 2), what I mean is that I want the new variable `v` to replace `arr(i)` in the initial array `arr()`. Is that possible? – Chri.s Sep 17 '18 at 10:01
Thank you for the suggestion, but what I want is to split the `arr(i)` into a new array within itself. I.e. giving the array another dimension. I might not have made that clear enough in the original post so I've updated it now with a manual example of the structure. – Chri.s Sep 17 '18 at 10:09
Hmm, what purpose does that serve?Is that a XY-problem? – Storax Sep 17 '18 at 10:15
The purpose is, in all essense, to convert a JSON response to an array in vba to easily handle the response and output it. However, sometimes the JSON response might be of varying "depth" (so to say) and will require multiple array dimensions for it to make sense (at least for me). – Chri.s Sep 17 '18 at 10:17
2

I am not familiar enough with JSON in order to really help you but this sounds like an XY problem. Did you search for "vba convert JSON array" and did you find this [post](https://stackoverflow.com/questions/36415776/parsing-json-array-in-excel-vba) – Storax Sep 17 '18 at 10:24
1

I'm well aware that there exists various JSON-handling scripts but I'm merely interesting in finding out whether what I'm asking in the original question is possible :) – Chri.s Sep 17 '18 at 10:31
Despite that I can't use a dictionary I've marked this as the correct answer as it's a viable solution and pointed me towards the solution I've found for my own case which I've posted as an answer. – Chri.s Sep 17 '18 at 11:20
Can't you use a dictionary because you use a Mac? – Storax Sep 17 '18 at 11:22
exactly - or, as a matter of fact i'm using windows but the application must be available for both Windows and Mac. – Chri.s Sep 17 '18 at 11:23

Chri.s · Answer 2 · 2018-09-17T11:34:53.843

Answering my own question here.

The answer of @Storax pointed me in the right direction in my specific case and therefore I've selected that answer as the correct one. As pointed out by Storax it was indeed due to incompatability between the variantand string array-types. Therefore I decided to convert the array from string to variant and I found the answer for that in this post - simply transforming it by using application.index().

The basics is simply to utilize a new array ("newarr") which continously has it's items converted to variant. This enables one to split the string inside the array-item, creating a new array within it.

Please note that @Storax' answer using a dictionary is fully viable when not using a Mac - which unfortunately I need to support.

The full procedure can be seen here:

Sub testarr()


    Dim arr As Variant, str As String, i As Integer, newarr As Variant, j As Integer

    str = "{test:[{{test this here||Can it be split inside the array?},{ let's add another layer || why not?}}]}"

    arr = Split(str, "[{")

    newarr = Application.Index(arr, 1, 0)

    For i = LBound(arr) To UBound(arr)
        newarr(i + 1) = Split(arr(i), "},")
        newarr(i + 1) = Application.Index(newarr(i + 1), 1, 0)
        For j = LBound(newarr(i + 1)) To UBound(newarr(i + 1))
            newarr(i + 1)(j) = Split(newarr(i + 1)(j), "||")
        Next j
    Next i

End Sub

VBA: Can an array-item be split within the array itself? Attempting gives "Type Mismatch" error

2 Answers2