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)
    ReDim Preserve aTemp(0 To lCount)
    aTemp(lCount) = sExprTemp
    Split97 = aTemp
End Function

Posted in Uncategorized

9 thoughts on “Split97

  1. 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.

  2. That wasn’t very neighborly of me. I’ve added a short comment at the top. Thanks for pointing this out.

  3. 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!

  4. 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. ;)

  5. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.