- 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