To split a string into collection in VBA you can use the following snippet.

Sample VBA

Public Function SplitStringToCollection(sInput As String, Optional sSplitter As String = ";") As Collection
Dim lCounter As Long
Dim sTemp() As String

On Error GoTo errHandler
Set SplitStringToCollection = New Collection
sTemp = Split(sInput, sSplitter)
For lCounter = 0 To UBound(sTemp)
    If (CStr(Replace(sTemp(lCounter), sSplitter, vbNullString)) <> vbNullString) Then
        SplitStringToCollection.Add CStr(Replace(sTemp(lCounter), sSplitter, vbNullString))
    End If
Next lCounter
Exit Function

Set SplitStringToCollection = New Collection
'handle the exception your way
End Function

2 thought on “How to split a string into collection in VBA”

Leave a Reply