Split & Join

Séparer des données délimitées dans un tableau

Alternative à la fonction "Split" apparue avec Access 2000

Function fSplit(ByVal sIn As String, Optional sDelim As String, _
                Optional nLimit As Long = -1, _
                Optional bCompare As Long = vbBinaryCompare) As Variant

    Dim sRead As String
    Dim sOut() As String
    Dim nC As Integer

    If sDelim = "" Then
        fSplit = sIn
    End If

    sRead = fReadUntil(sIn, sDelim, bCompare)

    Do
        ReDim Preserve sOut(nC)
        sOut(nC) = sRead
        nC = nC + 1
        If nLimit <> -1 And nC >= nLimit Then
            Exit Do
        End If
        sRead = fReadUntil(sIn, sDelim)
    Loop While sRead <> ""

    ReDim Preserve sOut(nC)
    sOut(nC) = sIn
    fSplit = sOut

End Function

' Fonction utilisée par fSplit()
 

Function fReadUntil(ByRef sIn As String, sDelim As String, _
                    Optional bCompare As Long = vbBinaryCompare) As String
    
    Dim nPos As String
    nPos = InStr(1, sIn, sDelim, bCompare)

    If nPos > 0 Then
        fReadUntil = Left(sIn, nPos - 1)
        sIn = Mid(sIn, nPos + Len(sDelim))
    End If

End Function

Délimiter et réassembler les données d'un tableau

Alternative à la fonction "Join" apparue avec Access 2000

Public Function fJoin(source() As String, _
                      Optional sDelim As String = " ") As String

    Dim sOut As String
    Dim iC As Integer

    For iC = LBound(source) To UBound(source) - 1
        sOut = sOut & source(iC) & sDelim
    Next iC

    sOut = sOut & source(iC)
    Join = sOut

End Function