Today I’m going to show you how to return an array from a VBA function. VBA functions can return arrays of any data type. But sometimes we need to return arrays with elements of different data types. Luckily we can do it using the variant data type. I will show how to return an array of type variant in an example. Let’s start with a simple example. Assume we want to create a VBA function which returns 3 random numbers between 1 and 100. The VBA function can return these 3 numbers as an array. We can use the inbuilt function called Rnd to generate a random number. Then we can do some additional calculations and use the VBA Int function to get a number between 1 and 100. Here is how you can do it.

Dim ResultArr(2) As Integer

ResultArr(0) = Int(Rnd * 100) + 1

ResultArr(1) = Int(Rnd * 100) + 1

ResultArr(2) = Int(Rnd * 100) + 1

ThreeRandomNumbers = ResultArr

End Function

Rnd function return values similar to this.

0.86261930.7904852

0.3735362

So I multiplied those numbers by 100.

86.2619379.04852

37.35362

And the VBA Int function returns the integer part of the number. So finally we get random numbers like below.

8679

37

Rnd function returns values equal or greater than 0 and less than 1. Then Int(Rnd * 100) will output numbers from 0 to 99. Therefore we have to add 1 to get a random number between 1 and 100.

Int(Rnd * 100) + 1 => Generates integer values between 1 and 100

Now we have a VBA function which can return an array. Let’s see how we can call this function within a subroutine. Data type of the array returned by our function is integer. So we need an array of the type integer inside our subroutine. Then we can assign the function’s return value to that array.

Dim RandomNumbers() As Integer

RandomNumbers = ThreeRandomNumbers()

End Sub

Add a breakpoint at End Sub and run the subroutine. Then you will see the result in the Locals window like this.

Also you can print these values to an Excel sheet as well. If the name of the worksheet is “Sheet1” then you can write the array to the worksheet as follows.

Dim WS As Worksheet

Dim RandomNumbers() As Integer

Dim i As Integer

Set WS = Worksheets("Sheet1")

RandomNumbers = ThreeRandomNumbers()

For i = 0 To 2

WS.Range("A1").Offset(i, 0).Value = RandomNumbers(i)

Next i

End Sub

Here is the result of the Test2 subroutine.

Next let’s look at another example where we need to pass arguments to the VBA function. Assume we need to find 5 powers/exponents of a given number. For an example if the given number is 3 then the VBA function should return 1,3,9,27 and 81 (3^{0},3^{1},3^{2},3^{3},3^{4})

Here we have to pass the given number as an argument to the function. Then the function can return 5 exponents of the number as an array.

Dim ResultArr(4) As Integer

ResultArr(0) = GivenNumber ^ 0

ResultArr(1) = GivenNumber ^ 1

ResultArr(2) = GivenNumber ^ 2

ResultArr(3) = GivenNumber ^ 3

ResultArr(4) = GivenNumber ^ 4

FiveExponents = ResultArr

End Function

Now we can call this function within a subroutine like this.

Dim WS As Worksheet

Dim Exponents() As Integer

Dim i As Integer

Set WS = Worksheets("Sheet1")

Exponents = FiveExponents(5)

For i = 0 To 4

WS.Range("A1").Offset(i, 0).Value = Exponents(i)

Next i

End Sub

Below is the outcome of the above subroutine.

Above functions output arrays of integer data type. So the returned array consists of only integers. But sometimes we need VBA functions which should return arrays with elements of various data types. They may contain values of data types such as integer, string, boolean etc. Here is an example.

This worksheet contains order information of a shop. Assume we need a VBA function which takes an order id as an argument and then returns all the other information of that order as an array. It is a type of function we need when searching data. So here various columns have various data types. Therefore we need to declare the data type of the return value of the function as the variant.

This is the function developed for the above requirement.

Dim WS As Worksheet

Dim WS_LastRow As Long

Dim i As Long

Dim j As Integer

Dim ResultArr(6) As Variant

Set WS = Worksheets("Order Details")

WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

For i = 2 To WS_LastRow

If StrComp(WS.Range("A" & i).Value, OrderId, vbTextCompare) = 0 Then

For j = 0 To 6

ResultArr(j) = WS.Range("A" & i).Offset(0, j).Value

Next j

Exit For

End If

Next i

GetOrderInformation = ResultArr

End Function

First we find the last row of the worksheet. Then use For Next Statement to iterate through the rows. Strcomp function is used to find the matching row for the given order id. If a matching row is found then all the information of that row is written to an array.

Here is how you can call the above function inside a subroutine.

Dim OrderInfo() As Variant

OrderInfo = GetOrderInformation("209-2752429-9545")

End Sub

Add a breakpoint at End Sub and run the macro. Then you can view the OrderInfo array in the Locals window.

**Also read**

Quickly Write Multidimensional Array to Excel Range

Re-size Dynamic Arrays

Calculate With Arrays

How to use preserve keyword in arrays

Transposing an Array in VBA