Douglas J. Steele said:Public Function MySplit( _
ByVal Expression As String, _
Optional Delimiter = " ", _
Optional limit As Long = -1, _
Optional Compare As Integer = vbBinaryCompare _
) As Variant
'*******************************************
'Name: MySplit (Function)
'Purpose: Emulates the A2k/A2k2 MySplit function
'Author: Terry Kreft
'Date: December 13, 2001, 02:47:07
'Called by: Any
'Calls: None
'Inputs:
' Expression - The string to MySplit
' Delimiter - The delimiter to MySplit on
' limit - How many terms to return
' (Default -1 return all terms)
' Compare - How to make the string comparison
' for the delimiter
' This should be
' vbBinaryCompare = 0 (Default)
' vbTextCompare = 1
' vbDatabaseCompare = 2
'Output:
'*******************************************
On Error GoTo Err_MySplit
Dim varValues As Variant
Dim lngCount As Long
Dim intInstr As Integer
Dim intLenDelim As Integer
Const ARRAY_LOW_BOUND = 0
varValues = Array()
If limit <> 0 Then
lngCount = 0
intLenDelim = Len(Delimiter)
intInstr = InStr(1, Expression, Delimiter, Compare)
Do While intInstr <> 0 And lngCount - limit + 1 <> 0
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Left(Expression, intInstr - 1)
Expression = Mid(Expression, intInstr + intLenDelim)
intInstr = InStr(1, Expression, Delimiter, Compare)
lngCount = lngCount + 1
Loop
If Len(Expression) <> 0 Then
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Expression
End If
End If
End_MySplit:
MySplit = varValues
Exit Function
Err_MySplit:
Err.Raise Err.Number, "MySplit", Err.Description
Resume End_MySplit
End Function