View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jim Cone[_2_] Jim Cone[_2_] is offline
external usenet poster
 
Posts: 1,549
Default Moving VBA6 string functions into VBA5

I don't get the automation error, but "ReadUntil" is not valid in any version of VBA.
Tom Ogilvy posted the following "Split" function, about six years, ago for use in Excel 97.
It is very fast and returns a Variant containing a 1 based array.
It appears to be limited to strings no longer than ~171 characters.
'--
Function Split97(sStr As String, sDelim As String) As Variant
Split97 = Evaluate("{""" & Application.Substitute(sStr, sDelim, """,""") & """}")
End Function
--
For example...
MsgBox Split97("only the lonely", " ")(2)
Returns "the"
'--
Jim Cone
Portland, Oregon USA




"Captain Nemo"
wrote in message
Gang -
My apologies for the double post. No one answered over on the Mac side.
Microsoft provides this website
http://support.microsoft.com/default...b;en-us;188007
on how to move these VBA functions into VBA5:

Function Description
-------- -----------
Join Used to join arrays elements.

Split Split a string into a variant array.

InStrRev Similar to InStr but searches from end of string.

Replace To find a particular string and replace it.

Reverse To reverse a string.

The Split Function, quoted below gives a

Compile error: Automaton type not supported in Visual Basic

Public Function Split(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
VbCompareMethod = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split = 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
Split = sOut
End Function

What's up with the error, and what can I do?

....Best, Captain Nemo