Alternative to Split function?

[vegasrockstar]

Hi

I am trying to create an array from a comma seperated string. I have been trying to use the Split function but the version of VBA that I am using (VBA332 - unable to change - am running a Macro in a Reflection session) does not include the VBA.Strings.Split functionality.

All help would be appreciated.

 

[GlennUK]

Is this any use:

http://www.tek-tips.com/viewthread.cfm?qid=828671

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vegasrockstar]

That looks as if could well do the trick. I'll give it a try - thanks!

 

[N1GHTEYES]

I haven't tried the code in Glen's reference, but here is an alternative version I wrote some time back for use in Excel. I have no idea whether it is faster or slower than that in Glen's ref.

If anyone can suggest any improvements, feel free.

The separation character is optional. If you supply the separation character when you call the function, it acts like the split function. If you don't it looks for comma, tab, semicolon and space in that order, and if it finds one it will use it. Failing that, it returns Nothing.

Other than that, you can use it like split in code. I also use it as a user function in excel. You can enter it as an array formula and it will do a "live" version of a "text to columns" operation.

One of these days, I'm going to have to take the time to figure out how to do that "code" window thing. Till then...

Public Function AL_Split(ipstr As String, _
Optional sepchar As Variant) _
As Variant
'***********************************************************
' 1. FUNCTIONAL DESCRIPTION of AL_Split
'
' This function mimics the VB6 "Split" function.
' The input string ipstr is split at each occurrence
' of sepchar.
' The seperated strings are returned as a string
' array contained in a variant.
' If no sepchar is specified, comma, tab, semicolon
' and space (in that order) are used if present.
'
' 2. REFERENCES - None
'
' 3. INPUTS
'
' ipstr - String
' Optional sepchar - String
'
' 4. RETURN VALUE
' Variant (array) All of the segments of the input
' string, split at sepchar boundaries
'
' 5. EXTERNAL EFFECTS - None
'***********************************************************
Dim char As String

If IsMissing(sepchar) Then
Dim testchars As Variant
Dim testchar As Variant
testchars = Array(",", vbTab, ";", " ")
For Each testchar In testchars
If InStr(ipstr, testchar) Then
char = testchar
Exit For
End If
Next testchar
Else
If TypeName(sepchar) = "String" Then
char = sepchar
Else
char = ""
End If
End If

If char <> "" Then
Dim totsize As Long, incsize As Long
Dim wordcount As Long, remlen As Long, charpos As Long
incsize = 50
totsize = incsize
ReDim strlist(totsize) As String
Dim remstr As String
remstr = ipstr
charpos = InStr(remstr, char)
wordcount = 0
While charpos > 0
'increase the size of string array to store the
'results if necessary
If wordcount > totsize Then
incsize = incsize * 2
totsize = totsize + incsize
ReDim Preserve strlist(totsize) As String
End If

'if the word length is >0 then
'add it to the string list
If charpos > 1 Then
strlist(wordcount) = Mid(remstr, 1, charpos - 1)
End If

'check if there are any chars left in the remstr
remlen = Len(remstr) - charpos
If remlen > 0 Then
remstr = Mid(remstr, charpos + 1, remlen)
End If

'increment the word count
wordcount = wordcount + 1

'find the next separator
charpos = InStr(remstr, char)
Wend

'add the remainder of the string (if any) into the
'last position of the strlist
If Len(remstr) > 0 Then
strlist(wordcount) = remstr
wordcount = wordcount + 1
End If

'redim the list back to the current size
If wordcount > 0 Then
ReDim Preserve strlist(wordcount - 1) As String
AL_Split = strlist()
Else
Set AL_Split = Nothing
End If
Else
Set AL_Split = Nothing
End If
End Function