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:

Answer 1

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

Answer 2

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