Return multiple values from a function, sub or type?

function vba return-value ms-word
Kenny Bones picture Kenny Bones · Mar 17, 2011 · Viewed 185.1k times · Source

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

Michael Paulukonis picture Michael Paulukonis · Mar 17, 2011

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

Related questions

How to return a result from a VBA function

How do I return a result from a function? For example: Public Function test() As Integer return 1 End Function This gives a compile error. How do I make this function return an integer?

Read More
What is the equivalent of "!=" in Excel VBA?

The problem is that != does not work as a function in excel vba. I want to be able to use If strTest != "" Then instead of If strTest = "" Then Is there another approach to do this besides !=? My function to mimic != …

Read More
How to pass an array to a function in VBA?

I am trying to write a function that accepts an array as an argument. The array can have any number of elements. Function processArr(Arr() As Variant) As String Dim N As Variant dim finalStr as string For N = LBound(…

Read More