Re: Determining array length with split function

Hi Ellen

The following should explain how to get information about the array returned
by Split():

Sub DoingTheSplits()

Dim sMyText As String
Dim asMyText() As String
Dim lngLowerBound As Long
Dim lngUpperBound As Long
Dim lngNumElements As Long


sMyText = "This text has four words"
asMyText = Split(sMyText)

lngLowerBound = LBound(asMyText)
lngUpperBound = UBound(asMyText)
lngNumElements = lngUpperBound - lngLowerBound

MsgBox "The text '" & sMyText & "' has " & CStr(lngNumElements) & "
elements"

If lngNumElements >= 3 Then
'Split() always returns a 0-based array.
'So the 3rd word is asMyText(2).
MsgBox "The third word of my text is '" & asMyText(2) & "'"
End If

'Or, you could do
If UBound(asMyText) >= 2 Then
'Split() always returns a 0-based array.
'So the 3rd word is asMyText(2).
MsgBox "The third word of my text is '" & asMyText(2) & "'"
End If

End Sub


Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word


"EllenM" <EllenM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C0B26F2-555E-4076-8182-4E628861A5F0@xxxxxxxxxxxxxxxx
Hello, I have incorporated the split function to isolate an array of
substrings within a string. It works fine, however, I need a way of
determining the number of substrings in any given array to avoid
referencing
an index that is outside the array bounds. For instance, sometimes I
could
have 3 substrings or other times 8 substrings. As such, something like the
following would fail for a string with only 3 substrings.


Split(MyData.GetText, vbCr)(5)


Anyone familiar with the split function will know that there is no element
5
in the above example. You get an "array out of bounds" error message or
some
such thing. Is there a way of determining in advance the number of
substrings
to avoid referencing an item that does not exist? I found Microsoft's help
section confusing when studying the split() function.


Thanks,
Ellen




.

Relevant Pages

  • RE: Follow up question. Get value of closed file.
    ... You havve to access each member of the array ... Dim ReturnData as Variant ... SourceSheet As String, _ ... Dim rsData As Object ...
    (microsoft.public.excel.programming)
  • Re: Need help with SeriesCollection Object Please !
    ... Why did you use 4 Elements in your Array and then the use of the Exit ... Function GetSourceSheet(sFmla As String, sWSname, sFile As String) As ... Dim i As Long ... ran my code on certain other charts sometimes it would return ...
    (microsoft.public.excel.programming)
  • Re: Searching for best matches in string query
    ... looped through my recordset as an array counting the matches. ... 'Receives an array to be searched and the string value containing all the ... Dim arrData() ' As String ... Dim iLeftcur, iRightCur, iPivot, iTemp As Long ...
    (microsoft.public.access.queries)
  • RE: ReDim Preserve code almost working
    ... specifying beginning and ending elements. ... SegList will, however, accept a zero-based array (because Option Base 1 does ... Optional iEnd As Long = -1) As String() ... Dim tmp() As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Sorting a variant array
    ... Dim tempList As Variant ... Dim testerList() As String ... Function sortTesters(rangeName As String) ... column range--but even that ends up as a x-rows by 1 column array. ...
    (microsoft.public.excel.programming)