excel VBA how to return 2 different arrays in a function

Question

I am wondering if VBA can return more than 1 array in a function. I have created a function called 'getStats()' and i would like to return 2 arrays named 'returnVal' and 'a' as shown below. I have tried the methods below but i only get back the value for array 'a' which is 10. It does'nt give me the array for 'returnVal'. Is there any way to do that? Pls help. I tried doing this: "getStats = returnVal & a " but an error occured as type mismatch

    Sub mysub()
        Dim i As Integer
        Dim myArray() As Integer 
        myArray() = getStats() 'calling for function

        MsgBox myArray(0)
        MsgBox myArray(1)
        MsgBox myArray(2)
     End Sub

     Function getStats() As Integer() 
        Dim returnVal(1 To 2) As Integer 
        Dim a(0) As Integer
        returnVal(1) = 7
        returnVal(2) = 8
        a(0) = 5 + 5

        getStats = returnVal 'returning value
        getStats = a 'returning value

    End Function

T.M. · Accepted Answer · 2020-05-02 06:01:52Z

4

A Function procedure can return one single result.

You can make that result a Variant and return an array of arrays or a custom object that encapsulates two arrays, but no matter what you do the function returns one value of the type you specify as its return type in its signature.

Or you can take ByRef parameters - this should work (untested):

Public Sub GimmeTwoArrays(ByRef outArray1 As Variant, ByRef outArray2 As Variant)
    ReDim outArray1(1 To 10)
    ReDim outArray2(1 To 10)
    Dim i As Long
    For i = 1 To 10
        outArray1(i) = i
        outArray2(i) = Chr$(64 + i)
    Next
End Sub

The caller only needs to pass the variant pointers, doesn't matter if you initialize them or not:

Dim values1 As Variant
Dim values2 As Variant
GimmeTwoArrays values1, values2
Debug.Print values1(1), values2(1)

You don't have to declare them As Variant, but wrapping your arrays in a Variant generally makes them easier to pass around and work with.

edited May 2, 2020 at 6:01

T.M.

10k3 gold badges40 silver badges61 bronze badges

answered May 1, 2020 at 4:37

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

T.M. Over a year ago

Friendly hint to (fine) late night answer: think redimming should be coded as ReDim outarray1(1 To 10) etc. :-)

T.M. Over a year ago

FYI posted an approach in addition to your answer returning the function results as ParamArray contents maintaining the individual array names; could be useful for initializing purposes, too.

T.M. · Accepted Answer · 2020-05-01 11:47:42Z

Return function results as ParamArray contents

In addition to Mathieu's valid answer referring to an array of arrays (or jagged array), I demonstrate a way to get the function results as ParamArray contents passed as empty array arguments. The function result assigned to arr has the possible advantage to dispose of several ways to address the individual or entire array contents:

get the whole set (e.g. array name arr) conform to ParamArray structure
get the sub sets directly, even by the original array names (here a and b)

Sub ExampleCall()
'[0] declare empty arrays and assign array to function results via ParamArray argument
Dim arr, a(1 To 10), b(1 To 10)
arr = GetBoth(a, b)                      ' << function GetBoth()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Two options to display results:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'[1] get the whole set conform to ParamArray structure
'    shows: arr(0) ~> 1,2,3,4,5,6,7,8,9,10            arr(1) ~> A,B,C,D,E,F,G,H,I,J
Debug.Print "arr(0) ~> " & Join(arr(0), ","), _
            "arr(1) ~> " & Join(arr(1), ",")

'[2] get the sub sets directly, even by the original array names
'    shows: a      ~> 1|2|3|4|5|6|7|8|9|10            b      ~> A|B|C|D|E|F|G|H|I|J
Debug.Print "a      ~> " & Join(a, "|"), _
            "b      ~> " & Join(b, "|")
'[ad) 1/2] get individual values, e.g. the first item of the second array
'    note that sub sets a and b got a 1-base declaration here,
'    whereas the array of the subset arrays remains 0-based
Debug.Print "arr(1)(2) = " & arr(1)(2)          ' // shows: arr(1)(2) = B
Debug.Print "b(2) = " & b(2)                    ' // shows: b(2)      = B

End Sub

Function GetBoth() via ParamArray arguments

Function GetBoth(ParamArray arr()) As Variant
    Dim i As Long
    For i = 1 To 10
        arr(0)(i) = i
        arr(1)(i) = Chr$(64 + i)
    Next
GetBoth = arr
End Function

Mark E. · Accepted Answer · 2021-06-10 06:23:49Z

Inspired by Mathieu and T.M, this function creates an arbitrary number of variables, and deals out a deck of cards.

Sub PlayHand()

    Dim Huey(0 To 4), Dewey(0 To 4), Louie(0 To 4)
    DealCards Huey, Dewey, Louie
    
    Dim Charlotte(0 To 6), Anne(0 To 6), Elizabeth(0 To 6), Maria(0 To 6)
    Combined = DealCards(Charlotte, Anne, Elizabeth, Maria)

End Sub

Function DealCards(ParamArray Players())

    'Create deck of cards
    Set cardCollection = New Collection
    For Each suit In Array("H", "C", "S", "D")
        For Each rank In Array(2, 3, 4, 5, 6, 7, 8, 9, 10, "J", "Q", "K", "A")
            cardCollection.Add suit & "_" & Rank
        Next
    Next
    
    'Deal out the cards to each player
    For i = 0 To UBound(Players)
        For j = 0 To UBound(Players(i))
            randomCard = Round(Rnd() * cardCollection.Count + 0.5, 0)
            Players(i)(j) = cardCollection(randomCard)
            cardCollection.Remove randomCard
        Next
    Next
    DealHand = Players

End Function

VBasic2008 · Accepted Answer · 2020-05-01 05:32:45Z

Merge Two Long Arrays

Option Explicit

Sub mysub()

    Dim i As Long
    Dim myArray() As Long
    myArray() = getStats() 'calling for function

    For i = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(i)
        ' MsgBox myArray(i)
    Next i

 End Sub

 Function getStats() As Long()

    Dim returnVal(1 To 2) As Long
    Dim a(0) As Long

    returnVal(1) = 7
    returnVal(2) = 8
    a(0) = 5 + 5

    getStats = mergeTwoLongArrays(returnVal, a)

End Function

Function mergeTwoLongArrays(Array1, Array2, Optional Base As Long = 0)

    Dim arr() As Long
    Dim NoE As Long
    Dim countNew As Long

    NoE = UBound(Array1) - LBound(Array1) + UBound(Array2) - UBound(Array2) + 2

    ReDim arr(Base To NoE - Base - 1)

    countNew = LBound(arr)
    For NoE = LBound(Array1) To UBound(Array1)
        arr(countNew) = Array1(NoE)
        countNew = countNew + 1
    Next NoE
    For NoE = LBound(Array2) To UBound(Array2)
        arr(countNew) = Array2(NoE)
        countNew = countNew + 1
    Next NoE

    mergeTwoLongArrays = arr

End Function

Collectives™ on Stack Overflow

excel VBA how to return 2 different arrays in a function

4 Answers 4

2 Comments

Comments

Comments

Merge Two Long Arrays

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Merge Two Long Arrays

Comments

Your Answer

Sign up or log in

Post as a guest

Related