VBA - Retourner des valeurs multiples

4 possibilités :

Le type définit par l'utilisateur

En Anglais : User Define Type (UDF)

Exemple :

Type Contact
    Nom As String
    Prenom As String
    Age As Byte
End Type

Function SetContact(N As String, Fn As String, A As Byte) As Contact
    SetContact.Nom = N
    SetContact.Prenom = Fn
    SetContact.Age = A
End Function

Pour l'utiliser :

Sub Test_SetContact()
Dim Cont As Contact

    Cont = SetContact("SMITH", "John", 23)
    Debug.Print Cont.Nom & " " & Cont.Prenom & ", " & Cont.Age & " years old."
End Sub

Argument ByRef

Passer un argument ByRef signifie que cet élément peut être modifié par la fonction.

Exemple :

Function Divide(Dividend As Integer, Divisor As Integer, ByRef Result As Double) As Boolean
    Divide = True
    On Error Resume Next
    Result = Dividend / Divisor
    If Err <> 0 Then
        Divide = False
        On Error GoTo 0
    End If
End Function

Pour l'utiliser :

Sub test_Divide()
Dim R As Double, Ddd As Integer, Dvs As Integer, B As Boolean

    Ddd = 10: Dvs = 3
    B = Divide(Ddd, Dvs, R)
    Debug.Print "Divide retourne : " & B & " Result = " & R
    Ddd = 10: Dvs = 0
    B = Divide(Ddd, Dvs, R)
    Debug.Print "Divide retourne : " & B & " Result = " & R
End Sub

ParramArray

Toujours Variant !!

Exemple :

Function Multiple_Divide(Dividend As Integer, Divisor As Integer, ParamArray numbers() As Variant) As Long
Dim i As Integer

    On Error GoTo ErrorHandler
    numbers(LBound(numbers)) = Dividend / Divisor
    For i = LBound(numbers) + 1 To UBound(numbers)
        numbers(i) = numbers(i - 1) / Divisor
    Next i
    Multiple_Divide = 1: Exit Function
ErrorHandler:
    Multiple_Divide = 0
End Function

Pour l'utiliser :

Sub test_Multiple_Divide()
Dim Arr(3) As Variant, Ddd As Integer, Dvs As Integer, L As Long, i As Integer

    Ddd = 10: Dvs = 3
    L = Multiple_Divide(Ddd, Dvs, Arr(0), Arr(1), Arr(2), Arr(3))
    Debug.Print "La fonction retourne : " & L
    Debug.Print "Les valeurs en retour de l'array : "
    For i = LBound(Arr) To UBound(Arr)
        Debug.Print Arr(i)
    Next i
    Erase Arr
    Debug.Print "--------------------------------------"
    Ddd = 10: Dvs = 0
    L = Multiple_Divide(Ddd, Dvs, Arr(0), Arr(1), Arr(2), Arr(3))
    Debug.Print "La fonction retourne : " & L
    Debug.Print "Les valeurs en retour de l'array : "
    For i = LBound(Arr) To UBound(Arr)
        Debug.Print IIf(Arr(i) = "", "vbNullString", "Null")
    Next i
End Sub

Fonction de type Tableau

La fonction, typée comme une variable tableau, retourne une liste de valeurs.

Exemple :

Function List() As String()
Dim i&, Temp(9) As String

    For i = 0 To 9
        Temp(i) = "Liste " & i + 1
    Next
    List = Temp
End Function

Pour l'utiliser :

Sub test_List()
Dim myArr() As String, i As Integer
'Note : vous n'avez pas besoin de dimensionner votre array 
    myArr = List()
    For i = LBound(myArr) To UBound(myArr)
        Debug.Print myArr(i)
    Next
End Sub