I'm trying to make my function return an array of strings and can't
seem to get it to work. I get Error 9, subscript out of range. The
error occurs on the line "strArray(i) = str"
Here is the relevant part of the code:

Sub CompareLabels(str1 as String)
Dim strWords1() As String
strWords1 = MakeArrayOfWords(str1)
End Sub

Private Function MakeArrayOfWords(ByVal str As String) As String()

Dim i As Integer, intSpace As Integer, intLength As Integer,
strArray() As String
If str < "" Then
Do
i = i + 1
intSpace = InStr(1, str, " ", vbTextCompare)
If intSpace 0 Then
strArray(i) = Left(str, intSpace)
intLength = Len(str) - intSpace
str = Right(str, intLength)
Else
strArray(i) = str
End If
Loop Until intSpace = 0
MakeArrayOfWords = strArray
End If

End Function


Can anyone help? Thanks.

P.S. I suck at working with arrays.

Sub CompareLabels(str1 As String)
Dim strWords1() As String
strWords1 = Split(str1)
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dave B" wrote in message
oups.com...
I'm trying to make my function return an array of strings and can't
seem to get it to work. I get Error 9, subscript out of range. The
error occurs on the line "strArray(i) = str"
Here is the relevant part of the code:

Sub CompareLabels(str1 as String)
Dim strWords1() As String
strWords1 = MakeArrayOfWords(str1)
End Sub

Private Function MakeArrayOfWords(ByVal str As String) As String()

Dim i As Integer, intSpace As Integer, intLength As Integer,
strArray() As String
If str < "" Then
Do
i = i + 1
intSpace = InStr(1, str, " ", vbTextCompare)
If intSpace 0 Then
strArray(i) = Left(str, intSpace)
intLength = Len(str) - intSpace
str = Right(str, intLength)
Else
strArray(i) = str
End If
Loop Until intSpace = 0
MakeArrayOfWords = strArray
End If

End Function


Can anyone help? Thanks.

P.S. I suck at working with arrays.

Hehehe. Thanks. I will have to tackle my array weakness another day.

Purely for reference the array you were trying to create was dynamic. This is
to say that you did not specify the size of the array when you created it. In
this case you need to use "ReDim Preserve" each time you add an element to
the array... Split is the way to go in this case... but for your future
reference, look up "Redim Preserve"
--
HTH...

Jim Thomlinson


"Dave B" wrote:

Hehehe. Thanks. I will have to tackle my array weakness another day.