Returning Multiple Values from a Custom Function

Question

So, I've got a ValidateForm function that loops through a form to validate each control. I have a collection set up called ValData to capture different bits of info to be passed out of the function. This is working great.

However, I don't know how to access each item in ValData after the function returns. I can get one at a time like: ValidateForm().IsValid, but in order to get each item, I have to run the function again. I want to avoid this.

Is there a way to run the function once, but access the values of each item returned?

XIVSolutions · Accepted Answer · 2011-11-14 04:16:09Z

Depending upon your requirements (which are NOT clear in your question! ;-) ), you might consider using a Collection as the return from your function:

Private Function MyResultsFunction() As Collection
    Dim output As Collection
    Set output = New Collection

    'Hydrate your collection with data by whatever means necessary:
    With output

        'Stupid example code:
        .Add "Item1"
        .Add "Item2"
        .Add "Item3"
        .Add "Item4"
    End With

    'Return a reference to the collection as the function output:
    Set MyResultsFunction = output

End Function

As a simple, retarded test of the above:

Private Sub Form_Load()

    'A variable to receive the results from your function:
    Dim Results As Collection

    'An iterator to loop through the results contained in the collection:
    Dim CurrentItem As Variant

    'For this example, a string to toss the results into to display in the
    'MsgBox:
    Dim output As String

    'Use the local Collection defined above to access the reference returned by
    'your function:
    Set Results = MyResultsFunction

    'Iterate through the collection and do something with the results
    'per your project requirements:
    For Each CurrentItem In Results

        'This is stupid example code:
        output = output & CurrentItem & vbCrLf
    Next

    'I am just displayng the results to show that it worked:
    MsgBox output

    'Clean up:
    Set Results = Nothing

End Sub

Hope that heps!

Gaijinhunter · Accepted Answer · 2011-11-12 05:20:24Z

Without seeing your code it's hard to say what exactly you are tyring to do, but here is one of several ways you can store results to query later.

Declare a public variable at the top of your module to represent the items from your ValData function. After you populate the array, you can access the items through a normal function.

You obviously could do more sophisticated things, especially if you use a collection object. You could even store a counter and create a GetNext() function. I hope this gives you a heads start.

Public Results(1 To 2) As String

Sub CreateTestArray()
    Results(1) = "Foo"
    Results(2) = "Bar"
End Sub

Function GetResult(ByVal i As Long) As String
    If i <= UBound(Results) Then
        GetResult = Results(i)
    End If
End Function

Sub ProofOfConcept()
    MsgBox GetResult(2) ' will show "Bar"
End Sub

Collectives™ on Stack Overflow

Returning Multiple Values from a Custom Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related