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:

We can see that the function returns the random integers of **96**, **87** and **5** starting in cell **A1** of our worksheet.

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

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

How to Return a Value from VBA Function

How to Get Cell Value from Another Sheet in VBA

How to Use IsNumeric to Check if Cell is a Number in VBA