Returning more than one value from a function

A function can only have one return value. In Access 2, there were a couple of ways to work around this limitation:

• Use a parameter to define what you want returned. For example:

  Function MultiMode(iMode As Integer) As String
     Select Case iMode
     Case 1
         MultiMode = "Value for first Option"
     Case 2
         MultiMode = "Value for second Option"
     Case Else
         MultiMode = "Error"
     End Select
  End Function

• Another alternative was to pass arguments whose only purpose was so the function could alter them:

  Function MultiArgu(i1, i2, i3)
     i1 = "First Return Value"
     i2 = "Second Return Value"
     i3 = "Third Return Value"
  End Function

VBA (Access 95 onwards) allows you to return an entire structure of values. In database terms, this is analogous to returning an entire record rather than a single field. For example, imagine an accounting database that needs to summarize income by the categories Wages, Dividends, and Other. VBA allows you to declare a user-defined type to handle this structure:

    Public Type Income
       Wages As Currency
       Dividends As Currency
       Other As Currency
       Total As Currency
   End Type

You can now use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:

    Function GetIncome() As Income
       GetIncome.Wages = 950
       GetIncome.Dividends = 570
       GetIncome.Other = 52
       GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
   End Function

To use the function, you could type into the Immediate Window:

    GetIncome().Wages

(Note: the use of "Public" in the Type declaration gives it sufficient scope.)

Programmers with a background in C will instantly recognize the possibilities now that user-defined types can be returned from functions. If you're keen, user-defined types can even be based on other user-defined types.

Collection
Kazi Masudul Alam