CODEWITHSUNDEEP
excelvba
Bogdan
Bogdan

Reputation: 673

How to initialize an Array in VBA with the value from an excel cell that contains a square brackets array?

I want to take an array from a cell that is filled with courses that an user has selected:

["Java","Visual Basic for Applications"]

I want to copy the elements from the array inside a VBA array:

Dim IndividualCourses(1 To 9) As String

The array from excel cell can hold min 1 to 10 values an is coming via an Wix form.

Upvotes: 0

Views: 172

Answers (2)

Axuary
Axuary

Reputation: 1507

This should work.

Sub test()
    Dim data() As Variant
    Dim arrStr As String
    Dim dataItems As Long
    Dim i As Long
    Dim IndividualCourses(1 To 9) As String

    arrStr = Range("B2")
    arrStr = Replace(Replace(arrStr, "[", "{"), "]", "}")
    data = Application.Evaluate(arrStr)
    dataItems = Application.CountA(data)
    For i = 1 To dataItems
        IndividualCourses(i) = data(i)
    Next i
End Sub

Upvotes: 1

Edward
Edward

Reputation: 8596

Assuming that your "array" is in a single Excel cell and is enclosed in "[" and "]", and each item is enclosed in double-quotes, you can do this:

Dim CourseList As String
CourseList = "[""Java"",""Visual Basic for Applications""]"
' Strip off "["" and ""]" at each end
CourseList = Mid(CourseList, 3, Len(CourseList) - 4)

' Split at quoted comma delimiter
Dim IndividualCourses() As String
IndividualCourses = Split(CourseList, """,""")

It might be better to use a JSON parser.

As this link says,

You could write your own VBA code to parse the JSON data.

This approach is only recommended if you have acute masochistic tendencies.

Upvotes: 1

Related Questions