Moving VBA6 string functions into VBA5

[Captain Nemo]

Gang -

My apologies for the double post. No one answered over on the Mac side.

Microsoft provides this website

http://support.microsoft.com/default.aspx?scid=kb;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

 

[Jim Cone]

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.aspx?scid=kb;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

 

[Rick Rothstein]

While this site says VB6 to VB5 (it means the compiled versions), they
should work fine with VBA as well. The site features several code methods
for each function, with timed tests, so you should find code that will work
for your setup...

http://www.xbeat.net/vbspeed/

 

[Captain Nemo]

While this site says VB6 to VB5 (it means the compiled versions), they
should work fine with VBA as well. The site features several code methods
for each function, with timed tests, so you should find code that will work
for your setup...

http://www.xbeat.net/vbspeed/

Rick - Thank you. Great site.

....best, Capt. N.

 

[Captain Nemo]

Jim -

Thanks. I need to split strings of about 3200 characters.

....Best, Capt. N