Function SplitValue(ByVal strInput As String, Optional ByVal strSeparateur As String = "|", Optional Index As Long = -1, Optional IndexExclude As String = "", _
Optional SecondaryDelimeter As String = "") As Variant()
Dim FirstSplit() As String, SplitExclude() As String, TempArray(), C As Long, j As Long, z As Long, good As Boolean
FirstSplit = Split(strInput, strSeparateur)
If IndexExclude <> "" Then SplitExclude = Split(IndexExclude, strSeparateur)
If IndexExclude = "" And Index < 0 Then
ReDim TempArray(1 To UBound(FirstSplit) + 1)
End If
z = 0
For C = 1 To UBound(FirstSplit) + 1
If IndexExclude <> "" Then
'retourne l'ensemble imputé de certains index
good = True
For j = 0 To UBound(SplitExclude)
If SplitExclude(j) = C Then
good = False
End If
Next
If good = True Then
ReDim Preserve TempArray(z)
If IsNumeric(FirstSplit(C - 1)) Then
TempArray(z) = CDbl(FirstSplit(C - 1))
Else
TempArray(z) = FirstSplit(C - 1)
End If
z = z + 1
good = False
End If
If Index = 0 And C = UBound(FirstSplit) + 1 Then
'retourne le total imputé de certains elements
j = UBound(TempArray) + 1
ReDim TempArray(0)
TempArray(0) = j
End If
Else
Select Case Index
Case Is < 0
'retourne l'ensemble complet
If IsNumeric(FirstSplit(C - 1)) Then
TempArray(C) = CDbl(FirstSplit(C - 1))
Else
TempArray(C) = FirstSplit(C - 1)
End If
Case 0
'retourne le total
j = UBound(FirstSplit) + 1
ReDim TempArray(0)
TempArray(0) = j
Exit For
Case Is > 0
'retourne un et un seul index; dans ce cas, voir si y'a un second "split"
If SecondaryDelimeter = "" Then
'retourne une valeur seule
ReDim TempArray(0)
If UBound(FirstSplit) + 1 <= Index Then
TempArray(0) = "#ERROR INDEX TO HIGHT#"
Else
TempArray(0) = FirstSplit(Index - 1)
End If
Else
'sous découpe encore en un nouveau tableau. traite les résultat en nombre
If UBound(FirstSplit) + 1 <= Index Then
ReDim TempArray(0)
TempArray(0) = "#ERROR INDEX TO HIGHT#"
Else
FirstSplit = Split(FirstSplit(Index - 1), SecondaryDelimeter)
ReDim TempArray(1 To UBound(FirstSplit) + 1)
For j = 1 To UBound(FirstSplit) + 1
If IsNumeric(FirstSplit(j - 1)) Then
TempArray(j) = CDbl(FirstSplit(j - 1))
Else
TempArray(j) = FirstSplit(j - 1)
End If
Next
End If
End If
Exit For
End Select
End If
Next C
'Scindé = Ts Non, ça pourrait apporter un petit + de l'écrire comme ça :
If Application.Caller.Rows.Count > 1 Then
SplitValue = WorksheetFunction.Transpose(TempArray)
Else
SplitValue = TempArray
End If
End Function