Tuesday, November 25, 2008

How to Return Multiple Values from a VBA Function

Return Multiple Values from a Visual Basic Function

A normal VBA function has a return statement that returns a value to the calling function/subroutine

If you want multiple values to be returned, use reference parameters. The reference parameter represents the same storage location as the argument variable and hence changes made in the function is reflected in the calling function too.

Sub ReturnMultipleValue()

Dim L As Double

Dim B As Double

L = 10

B = 6

Debug.Print L & " " & B

ChangeLengthAndBreadth L, B

Debug.Print L & " " & B

End Sub

The above sub passes the arguments to ChangeLengthAndBreadth function by reference. That is, the storage location is passed to function and the changes made inside the ChangeLengthAndBreadth function is reflected in the main method.

Function ChangeLengthAndBreadth(ByRef Length As Double, ByRef Width As Double)

Length = 1.2 * Length

Width = 1.2 * Width

End Function

Multiple Return Parameters in VBA, Multiple Return Parameters in Visual Basic, Return Multiple Values – VBA Function, VBA Function to return more than one value

Return Multiple Values

See also:

Output Parameters in C# (.NET)

How to get the return value from C# program (console application)


  1. Does it *return* multiple values, or simply *change* multiple values?

  2. Anonymous6:36 AM

    Good Call, it simply changes multiple values. The second "Function" is not really a function but a Sub.

  3. Anonymous7:09 AM

    To make it perform as a function, change it as follows:F
    unction ChangeLengthAndBreadth(ByRef Length As Double, ByRef Width As Double) As ""

  4. Set osoap = CreateObject("MSSOAP.SoapClient")
    osoap.ClientProperty("ServerHTTPRequest") = True
    osoap.mssoapinit ("")
    result = osoap.get_customer_debit_balance(CUSTOMERID)'this function return type is float and it is working fine
    Dim result1 As object
    result1 = osoap.get_customer_balance(1010) 'this function return type is CustomerBalance class object
    MsgBox result1

    I am running this code and getting invalid procedure or function call error message

    when I am using PHP with SOAP and this function returning array but in VB i am getting error.

    please help me to fix up this problem and explain me how to get multiple values from a function using VB.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.