Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very complex IF function if not an alternative function of crossselection | Excel Worksheet Functions | |||
Freeze at the top & split the side in Excel97? | New Users to Excel | |||
non-VBA Alternative to SUBTITUTE function | Excel Worksheet Functions | |||
Alternative for IF function | Excel Discussion (Misc queries) | |||
Alternative to Indirect Function | Excel Programming |