Split() and a few other questions

[jayy]

I would like to find an easy way to *split* a string by a delimiter.
For example, if I had this string:

"Rush,Enchant,Symphony X"

I'd like to be able to turn it into an array like this:

("Rush", "Enchant", "Symphony X")

I've seen some people use 'Split()' on this forum. For example, in
mikeburg's post:

-------
AEmployeeLastName = Split(Cell.Offset(0, -4), "
")(UBound(Split(Cell.Offset(0, -4), " ")))
-------

But this doesn't work on my Mac. I'm guessing it only works on a PC?
(By the way, is there a web sight that tells the differences between
VBA on a Mac versus a PC?)

I next tried coming up with a simple subroutine to split this string:

-------
Sub test3()
Dim bandList As String, theArray(0 To 2), theText As String

bandList = "Rush,Enchant,Symphony X"
n = 0
theText = ""

For x = 1 To Len(bandList)
If Mid(bandList, x, 1) = "," Then
theArray(n) = theText
n = n + 1
theText = ""
Else
theText = theText & Mid(bandList, x, 1)
End If
Next x

theArray(n) = theText

MsgBox theArray(0) & vbLf & theArray(1) & vbLf & theArray(2)
End Sub
-------

I'm thinking surely there's an easier way to do this? Is there
something else on the Mac similar to Split()?

While doing this, I stumbled on another question. I know that I can
reDim the array, but what if I don't know how many elements will be
placed in it? For example, let's say I have a cell that contains this
text:

Rush,Enchant,Symphony X,Journey,Iron Maiden

With my above subroutine (test3) I don't know how many items will be
placed in the array. Yes, I will end up with 5 items in there, but
I'll only know that once the code is done looping through each letter
in the string. So I really can't reDim the array unless I want to
reDim it every time through the loop. I could of course set the array
to (0 To 100) which would probably be safe, but is there a more
accurate way to do this? It would be nice not having to set the array
and then just using whatever amount of it I needed.

Thanks for looking at my questions.

Jay

 

[Jim Thomlinson]

I am not a mac user but here is how the split function works on a PC. It also
demonstrates how to iterate through an array...

Sub LoadArray()
Dim aryBands() As String
Dim BandList As String
Dim intCounter As Integer

BandList = "Rush,Enchant,Symphony X"

aryBands = Split(BandList, ",")

For intCounter = LBound(aryBands) To UBound(aryBands)
MsgBox aryBands(intCounter)
Next intCounter
End Sub

 

[Dave Peterson]

Split was added in xl2k (VBA 6). Mac's still use VBA 5.

Tom Ogilvy uses this for lots of code in xl97:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
These come from MS:

Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit <> -1 And nC >= nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead <> ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function

The readuntil and split97 functions were stolen from the MSKB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

 

[Jim Thomlinson]

Sorry I also shold have mentioned that you can create dynamic arrays (the
sized of the array is defined at run time). Look Up the key word Redim.

 

[jayy]

Thanks Dave and Jim very much for the help. That's a very cool function
Tom came up with!

Thanks again!

Jay