You can use the following basic syntax to return an array from a function in VBA:

Function GenerateRandom() As Integer()

    Dim RandValues(2) As Integer
     
    'generate three random integers and store them in array 
    RandValues(0) = Int(Rnd * 100)
    RandValues(1) = Int(Rnd * 100)
    RandValues(2) = Int(Rnd * 100)
    
    'return array as a result of the function
    GenerateRandom = RandValues

End Function

This particular example generates three random integers, stores them in an array, then returns the array as a result of the function called GenerateRandom().

The following examples show how to return the values from this function in two different ways:

• Return the values in a message box
• Return the values in cells

Let’s jump in!

Example 1: Return Array from Function and Display Values in Message Box

We can use the following syntax to create a function called GenerateRandom() that generates an array of three random integers and then display the integers in a message box by using the MsgBox function:

'define function to generate array of three random integers
Function GenerateRandom() As Integer()

    Dim RandValues(2) As Integer
     
    RandValues(0) = Int(Rnd * 100)
    RandValues(1) = Int(Rnd * 100)
    RandValues(2) = Int(Rnd * 100)
    
    GenerateRandom = RandValues

End Function

'define sub to display values from function in a message box
Sub DisplayRandom()

     Dim WS As Worksheet
     Dim RandomValues() As Integer
     Dim i As Integer

     Set WS = Worksheets("Sheet1")
     RandomValues = GenerateRandom()

     j = "Array values: "
     For i = 0 To 2
          j = (j & RandomValues(i) & " ")
     Next i
     MsgBox j

End Sub

When we run this macro, we receive the following output:

We can see that the function returns the random integers of 96, 87 and 5 in a message box.

Example 2: Return Array from Function and Display Values in Cells

We can use the following syntax to create a function called GenerateRandom() that generates an array of three random integers and then display the integers starting in cell A1 in Excel:

'define function to generate array of three random integers
Function GenerateRandom() As Integer()

    Dim RandValues(2) As Integer
     
    RandValues(0) = Int(Rnd * 100)
    RandValues(1) = Int(Rnd * 100)
    RandValues(2) = Int(Rnd * 100)
    
    GenerateRandom = RandValues

End Function

'define sub to display values from function starting in cell A1
Sub DisplayRandom()

     Dim WS As Worksheet
     Dim RandomValues() As Integer
     Dim i As Integer

     Set WS = Worksheets("Sheet1")
     RandomValues = GenerateRandom()

     For i = 0 To 2
          WS.Range("A1").Offset(i, 0).Value = RandomValues(i)
     Next i

End Sub

When we run this macro, we receive the following output:

Note: To return the array values starting in a different cell, simply change A1 in the code to a different cell reference.