Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Pages

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 (30,31,32,33,34)

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.

Name

Email *

Message *