Simulates the Split function that is not available in Excel 97.
Function Split97(ByVal sExpr As String, _
Optional sDelim As String = ” “, _
Optional lLimit As Long = -1, _
Optional cComp As VbCompareMethod = vbBinaryCompare) As Variant
Dim aTemp() As String
Dim lCount As Long
Dim lLimitCnt As Long
Dim sExprTemp As String
Dim lDelimFnd As Long
lCount = 0
lLimitCnt = lLimit
sExprTemp = sExpr
lDelimFnd = InStr(1, sExprTemp, sDelim, cComp)
Do While CBool(lLimitCnt – 1) And lDelimFnd > 0
ReDim Preserve aTemp(0 To lCount)
aTemp(lCount) = Left(sExprTemp, lDelimFnd – 1)
sExprTemp = Right(sExprTemp, Len(sExprTemp) – (Len(aTemp(lCount)) + 1) _
– (Len(sDelim) – 1))
lCount = lCount + 1
lLimitCnt = lLimitCnt – 1
lDelimFnd = InStr(1, sExprTemp, sDelim, cComp)
Loop
ReDim Preserve aTemp(0 To lCount)
aTemp(lCount) = sExprTemp
Split97 = aTemp
End Function
What’s your point?
The point is that Excel 2000+ has some built-in VBA functions that do not exist in Excel 97. Therefore, users of Excel 97 can use functions such as these to simulate functions that are built in to later versions.
Thanks, good point.
That wasn’t very neighborly of me. I’ve added a short comment at the top. Thanks for pointing this out.
Thanks for this function! This will help lot of programmers (like me) tired enough to do impotant functions (like the one you did).
Thanks again and more power!
Thanks a million
GREAT WORK on this and the Replace function.
Two quick things: First, the VbCompareMethod needs to be changed to Long (like with Replace97). Second, the double quotes need to be just straight double quotes, not angled ones. After I made these quick changes it complied and worked perfectly in Excel 97.
Thanks again, these two functions have been REALLY HELPFUL. ;)
GOOD JOB man
your code has done me well..
Nice job with this. One note that might help others, The function call returned an error with me: “Variable uses Automation type not supported in VB”
To resolve this, take out the VbBinaryCompare. The function call then would read:
Function Split97(ByVal sExpr As String, _
Optional sDelim As String = ” “, _
Optional lLimit As Long = -1) As Variant
Which then operates like the split function in later versions. Thanks again for sharing this.