EXCEL :: Récupérer le contenu d'une liste déroulante (validation) en VBA

Sub GetValidationList()
    Const LIST_FRUITS = "LIST_FRUITS"
    Const EGAL = "="
    Const VIRGULE = ","
    Dim rRng As Range
    Dim vArray As Variant
    Dim iDx As Integer
    Dim iRows As Integer
    Dim sString As String
    
    
    Set rRng = Range(LIST_FRUITS)
    On Error Resume Next
    
    iRows = Range(Replace(rRng.Validation.Formula1, EGAL, "")).rows.Count
    
    ReDim vArray(1 To iRows)
    
    For iDx = 1 To iRows
        vArray(iDx) = _
            Range(Replace(rRng.Validation.Formula1, EGAL, "")).Cells(iDx, 1)
    Next iDx
    
    If Err.Number <> 0 Then
        Err.Clear
        vArray = Split(rRng.Validation.Formula1, VIRGULE)
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Impossible de renvoyer la liste - erreur : " & Err.Number & " - " & Err.Description
        Exit Sub
    End If
    
    On Error GoTo 0
    
    For iDx = LBound(vArray) To UBound(vArray)
        rRng.Value = vArray(iDx)
        Application.Calculate
        sString = sString & " ; " & rRng.Value
        Debug.Print rRng.Value
    Next iDx
    MsgBox sString
    
End Sub