How to Return an Array From VBA Function

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.

Function ThreeRandomNumbers() As Integer()

     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.8626193
0.7904852
0.3735362

So I multiplied those numbers by 100.

86.26193
79.04852
37.35362

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

86
79
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.

Sub Test1()

     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.

Sub Test2()

     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⁰,3¹,3²,3³,3⁴)

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.

Function FiveExponents(GivenNumber As Integer) As Integer()

     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.

Sub Test3()

     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.

Function GetOrderInformation(OrderId As String) As Variant

     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.

Sub Test4()

     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