Returning more than one value from a function

        15 votes: *****     19,846 views      No comments
by Allen Browne, 20 April 2005    (for ALL VERSIONS of Access)

Microsoft Access Tips for Serious Users

Provided by Allen Browne,

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:


(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.

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Returning more than one value from a function'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).