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
