Return multiple values from a function, sub or type? Return multiple values from a function, sub or type? vba vba

Return multiple values from a function, sub or type?


You might want want to rethink the structure of you application, if you really, really want one method to return multiple values.

Either break things apart, so distinct methods return distinct values, or figure out a logical grouping and build an object to hold that data that can in turn be returned.

' this is the VB6/VBA equivalent of a struct' data, no methodsPrivate Type settings    root As String    path As String    name_first As String    name_last As String    overwrite_prompt As BooleanEnd TypePublic Sub Main()    Dim mySettings As settings    mySettings = getSettings()End Sub' if you want this to be public, you're better off with a class instead of a User-Defined-Type (UDT)Private Function getSettings() As settings    Dim sets As settings    With sets ' retrieve values here        .root = "foo"        .path = "bar"        .name_first = "Don"        .name_last = "Knuth"        .overwrite_prompt = False    End With    ' return a single struct, vb6/vba-style    getSettings = setsEnd Function


You could try returning a VBA Collection.

As long as you dealing with pair values, like "Version=1.31", you could store the identifier as a key ("Version") and the actual value (1.31) as the item itself.

Dim c As New CollectionDim item as VariantDim key as Stringkey = "Version"item = 1.31c.Add item, key'Then return c

Accessing the values after that it's a breeze:

c.Item("Version") 'Returns 1.31orc("Version") '.Item is the default member

Does it make sense?


Ideas :

  1. Use pass by reference (ByRef)
  2. Build a User Defined Type to hold the stuff you want to return, and return that.
  3. Similar to 2 - build a class to represent the information returned, and return objects of that class...