I'm using VBA but not on excel.
I know that on VBA for Excel, you could do something like Split("String will be splitted") and obtain an array back.
Is there a way to perform the split without this function? Because it isn't recognize by the version of VBA I'm using.
Thank you.
something like this, returning a collection, for time as didn't want to code all the if's for redimming the o/p array.
Public Sub testing()
Dim c As New Collection
Set c = New_Split("test split function", " ")
End Sub
Public Function New_Split(strInput As String, strDelimiter As String) As Collection
Dim colDelimitPoints As New Collection
Dim intCounter As Integer
Dim intPrevPoint As Integer
For intCounter = 1 To Len(strInput)
If Mid(strInput, intCounter, 1) = strDelimiter Then
colDelimitPoints.Add intCounter, CStr(intCounter)
End If
Next intCounter
intPrevPoint = 1
Set New_Split = New Collection
For Each i In colDelimitPoints
New_Split.Add Mid(strInput, intPrevPoint, (i - intPrevPoint))
intPrevPoint = i + 1
Next i
End Function
You can combine the collection creation and the iteration of it later into one routine, I've left separate to show how it's working.
I am assuming you are using an early version of Excel in which Split doesn't exist, @Meehow and @Nathan_Sav are correct that you are best off writing your own, I using commands like mid, and instr.
There is not an equivalent, just a way to make an equivalent.
See the below equivalent: -
Public Sub Sample()
Dim ArySplit() As String
ArySplit = FnSplit("This|is|my||string", "|")
End Sub
Private Function FnSplit(ByVal StrContent As String, ByVal StrDelimiter As String) As String()
Dim AryTemp() As String
ReDim AryTemp(0)
'Work until we have nothing left to work with
Do Until StrContent = ""
'Only increase the array size if needed
If AryTemp(UBound(AryTemp, 1)) <> "" Then ReDim Preserve AryTemp(UBound(AryTemp, 1) + 1)
'if the delimiter is no longer there then output the remaining content
'and clear out the todo string
If InStr(1, StrContent, StrDelimiter) = 0 Then
AryTemp(UBound(AryTemp, 1)) = StrContent
StrContent = ""
Else
'If there is a delimiter then then add it to the array and take it and the delimiter
'off of the to do string
AryTemp(UBound(AryTemp, 1)) = Left(StrContent, InStr(1, StrContent, StrDelimiter) - 1)
StrContent = Right(StrContent, Len(StrContent) - ((InStr(1, StrContent, StrDelimiter) - 1) + Len(StrDelimiter)))
End If
Loop
'Return our array
FnSplit = AryTemp
End Function
