3

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:

Example of array structure

Improve this question
1
  • arr(i) is of type string. You have to use another variable of type variant. Commented Sep 17, 2018 at 9:49

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

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.
ad 1) I do not know of and I am quite sure there is no way ad 2) Possibly with join
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?
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
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 :)
|
2

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.