Alternative to Split function for Excel97?
 

I am using Excel XP at home but have transfered a spreadsheet to a PC which
has Excel 97 on it. I can't update this PC. I have used the Split function in
a macro, this isn't recognised in 97. Are there any alternatives?

Alternative to Split function for Excel97?
 

Hi Norm,

Try Tom Ogilvy's Split97 function:

Function Split97(sStr As String, sDelim As String) As Variant
Split97 = Evaluate("{""" & Application.Substitute _
(sStr, sDelim, """,""") & """}")
End Function

---
Regards,
Norman



"Norm" wrote in message
...
I am using Excel XP at home but have transfered a spreadsheet to a PC which
has Excel 97 on it. I can't update this PC. I have used the Split function
in
a macro, this isn't recognised in 97. Are there any alternatives?

Alternative to Split function for Excel97?
 

Here is the routine I use, adapted from one found at VBSPEED
(http://www.xbeat.net/vbspeed/c_Split.htm
Its fast enough not to bother with VBA6 split.


Public Function Split5(Expression As String, _
Delimiter As String) As Variant
' by Donald, , 20000916
'
' modified for VBA5 crw 8/10/2004
'
'
Const BUFFERDIM As Long = 1024
Dim cntSplit As Long
Dim posStart As Long
Dim posFound As Long
Dim lenDelimiter As Long
Dim sArray() As String
Dim ubArray As Long
Dim Count As Long

Count = -1

lenDelimiter = Len(Delimiter)
If lenDelimiter = 0 Then
' return expression in single-element Variant array
Split5 = Array(Expression)
Else
posStart = 1
ubArray = -1
Do
If cntSplit ubArray Then
ubArray = ubArray + BUFFERDIM
ReDim Preserve sArray(ubArray)
End If
posFound = InStr(posStart, Expression, Delimiter, vbBinaryCompare)
If cntSplit + 1 = Count Then
sArray(cntSplit) = Mid$(Expression, posStart)
Exit Do
Else
If posFound Then
sArray(cntSplit) = Mid$(Expression, posStart, posFound -
posStart)
posStart = posFound + lenDelimiter
cntSplit = cntSplit + 1
Else
sArray(cntSplit) = Mid$(Expression, posStart)
End If
End If
Loop While posFound
' shrink to actual size
ReDim Preserve sArray(cntSplit)
' return string array as Variant array
Split5 = sArray
End If

End Function


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Alternative to Split function for Excel97?
 

Charles,
I give up...

Count = -1
If cntSplit + 1 = Count Then

How could cntSplit ever = -2?
Is this some "left over" code?

Regards,
Jim Cone
San Francisco, CA

"Charles Williams" wrote in message ...
Here is the routine I use, adapted from one found at VBSPEED
(http://www.xbeat.net/vbspeed/c_Split.htm
Its fast enough not to bother with VBA6 split.


Public Function Split5(Expression As String, _
Delimiter As String) As Variant
' by Donald, , 20000916
'
' modified for VBA5 crw 8/10/2004
'
'
Const BUFFERDIM As Long = 1024
Dim cntSplit As Long
Dim posStart As Long
Dim posFound As Long
Dim lenDelimiter As Long
Dim sArray() As String
Dim ubArray As Long
Dim Count As Long

Count = -1

lenDelimiter = Len(Delimiter)
If lenDelimiter = 0 Then
' return expression in single-element Variant array
Split5 = Array(Expression)
Else
posStart = 1
ubArray = -1
Do
If cntSplit ubArray Then
ubArray = ubArray + BUFFERDIM
ReDim Preserve sArray(ubArray)
End If
posFound = InStr(posStart, Expression, Delimiter, vbBinaryCompare)
If cntSplit + 1 = Count Then
sArray(cntSplit) = Mid$(Expression, posStart)
Exit Do
Else
If posFound Then
sArray(cntSplit) = Mid$(Expression, posStart, posFound -
posStart)
posStart = posFound + lenDelimiter
cntSplit = cntSplit + 1
Else
sArray(cntSplit) = Mid$(Expression, posStart)
End If
End If
Loop While posFound
' shrink to actual size
ReDim Preserve sArray(cntSplit)
' return string array as Variant array
Split5 = sArray
End If

End Function


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Alternative to Split function for Excel97?
 

And MS has some code at:

http://support.microsoft.com/default...b;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

Norm wrote:

I am using Excel XP at home but have transfered a spreadsheet to a PC which
has Excel 97 on it. I can't update this PC. I have used the Split function in
a macro, this isn't recognised in 97. Are there any alternatives?

--

Dave Peterson

Alternative to Split function for Excel97?
 

Jim,

the original VBSPEED routine has an optional Count argument (VB6 Split has
this optional argument) which I removed when converting to VBA5, but I added
Count=-1 instead of the default value for the optional argument ...

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Jim Cone" wrote in message
...
Charles,
I give up...

Count = -1
If cntSplit + 1 = Count Then

How could cntSplit ever = -2?
Is this some "left over" code?

Regards,
Jim Cone
San Francisco, CA

"Charles Williams" wrote in message
...
Here is the routine I use, adapted from one found at VBSPEED
(http://www.xbeat.net/vbspeed/c_Split.htm
Its fast enough not to bother with VBA6 split.


Public Function Split5(Expression As String, _
Delimiter As String) As Variant
' by Donald, , 20000916
'
' modified for VBA5 crw 8/10/2004
'
'
Const BUFFERDIM As Long = 1024
Dim cntSplit As Long
Dim posStart As Long
Dim posFound As Long
Dim lenDelimiter As Long
Dim sArray() As String
Dim ubArray As Long
Dim Count As Long

Count = -1

lenDelimiter = Len(Delimiter)
If lenDelimiter = 0 Then
' return expression in single-element Variant array
Split5 = Array(Expression)
Else
posStart = 1
ubArray = -1
Do
If cntSplit ubArray Then
ubArray = ubArray + BUFFERDIM
ReDim Preserve sArray(ubArray)
End If
posFound = InStr(posStart, Expression, Delimiter, vbBinaryCompare)
If cntSplit + 1 = Count Then
sArray(cntSplit) = Mid$(Expression, posStart)
Exit Do
Else
If posFound Then
sArray(cntSplit) = Mid$(Expression, posStart, posFound -
posStart)
posStart = posFound + lenDelimiter
cntSplit = cntSplit + 1
Else
sArray(cntSplit) = Mid$(Expression, posStart)
End If
End If
Loop While posFound
' shrink to actual size
ReDim Preserve sArray(cntSplit)
' return string array as Variant array
Split5 = sArray
End If

End Function


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com