Return multiple values from a function, sub or type?

Question

So I was wondering, how can I return multiple values from a function, sub or type in VBA? I've got this main sub which is supposed to collect data from several functions, but a function can only return one value it seems. So how can I return multiple ones to a sub?

Answer 1

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 methods Private Type settings     root As String     path As String     name_first As String     name_last As String     overwrite_prompt As Boolean End Type   Public 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 = sets  End Function 

Answer 2

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 Collection Dim item as Variant Dim key as String key = "Version" item = 1.31 c.Add item, key 'Then return c 

Accessing the values after that it's a breeze:

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

Does it make sense?