Existem quatro possibilidades de retornar valores múltiplos no Excel. Veremos cada um deles a seguir, com os códigos que deverão ser inseridos por você.
Exemplo:
Type Contact Sobrenome As String Nome As String Age As Byte End Type Function SetContact(N As String, Fn As String, A As Byte) As Contact SetContact.Sobrenome = N SetContact.Nome = Fn SetContact.Age = A End Function
Sub Test_SetContact() Dim Cont As Contact Cont = SetContact("SMITH", "John", 23) Debug.Print Cont.Sobrenome & " " & Cont.Nome & ", " & Cont.Age & " years old." End Sub
Passar um argument ByRef significa que este elemento pode ser alterado pela função.
Exemplo:
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
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 retorna: " & B & " Result = " & R Ddd = 10: Dvs = 0 B = Divide(Ddd, Dvs, R) Debug.Print "Divide retorna: " & B & " Result = " & R End Sub
Exemplo:
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
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 "A função retorna: " & L Debug.Print "Os valores de retorno da 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 "A função retorna: " & L Debug.Print "Os valores de retorno da array:" For i = LBound(Arr) To UBound(Arr) Debug.Print IIf(Arr(i) = "", "vbNullString", "Null") Next i End Sub
Exemplo:
Function List() As String() Dim i&, Temp(9) As String For i = 0 To 9 Temp(i) = "Lista" & i + 1 Next List = Temp End Function
Sub test_List() Dim myArr() As String, i As Integer 'Observação: você não precisa dimensionar sua array myArr = List() For i = LBound(myArr) To UBound(myArr) Debug.Print myArr(i) Next End Sub
Foto: © Excel.