John,
There is a simpler way, but this is what I use. Call it like this:
strMyWord = GetAllWordsAsCollection("This is a test")(1)
...or...
strMyWord = GetAllWordsAsCollection("This is a test").Item(1)
Public Function GetAllWordsAsCollection(ByVal strPhrase As String, Optional
strDelimiter As String = " ") As VBA.Collection
Dim col As VBA.Collection
Dim strTemp As String
Dim intPos As Integer
Set col = New VBA.Collection
strPhrase = Trim(strPhrase)
If Right(strPhrase, 1) = strDelimiter Then strPhrase = Left(strPhrase,
Len(strPhrase) - 1)
strPhrase = Trim(strPhrase)
Do While Len(strPhrase) > 0
intPos = InStr(1, strPhrase, strDelimiter)
If intPos = 0 Then
If Len(strPhrase) > 0 Then
col.Add strPhrase
strPhrase = ""
Else
Set col = Nothing
GoTo Proc_Exit
End If
Else 'intPos > 0
strTemp = Trim(Left(strPhrase, intPos - 1))
col.Add strTemp
strPhrase = Trim(Mid(strPhrase, Len(strTemp) + 1))
End If
If Left(strPhrase, 1) = "," Then strPhrase = Mid(strPhrase, 2)
strPhrase = Trim(strPhrase)
Loop
Proc_Exit:
If col.Count > 0 Then Set GetAllWordsAsCollection = col
End Function
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
