Determining array length with split function

[EllenM]

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

 

[Shauna Kelly]

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

 

[Helmut Weber]

Hi Ellen,

split returns a zero based array,
"option base" is ignored.

MsgBox UBound(Split("x,x,x,x,x,x,x,x,x,x", ",")) ' 9

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[EllenM]

Thanks, Shauna, the examples work. Unfortunately, in my situation, I am using
the carriage return as the delimiter. I’ve designed a form so that when I
copy data from a form, the carriage returns are carried over and stored in
between this continuous string of text. Unfortunately, the GetText method
removes the carriage return characters from the string. As such, I would
always return a value of 1 since only one carriage return is left remaining.
See below.



Private Sub Paste_Click()

Dim MyData As DataObject

Dim Num

Dim myStr As String



Set MyData = New DataObject



MyData.GetFromClipboard ‘’ THIS ASSUMES I HAVE STORED TEXT FROM THE
CLIPBOARD



'THE FOLLOWING LINE REMOVES THE CARRIAGE RETURNS FROM THE STRING... NOT
COUNTED AS

'PART OF THE STRING ! ! !



myStr = MyData.GetText

MsgBox UBound(Split(myStr, "vbCr")) + 1





End Sub





How would I avoid the stripping? Is there another function that retains
carriage returns in text copied from the Windows clipboard?



Thanks!