Working with VBA Functions That Return an Array in Excel 2016
Array formulas are one of Excel’s most powerful features. If you’re familiar with array formulas, you’ll be happy to know that you can create VBA functions that return an array.
Returning an array of month names
Let’s start out with a simple example. The MonthNames function returns a 12-element array of — you guessed it — month names.
Function MonthNames() MonthNames = Array(“January”, “February”, “March”, _ “April”, “May”, “June”, “July”, “August”, _ “September”, “October”, “November”, “December”) End Function
To use the MonthNames function in a worksheet, you must enter it as a 12-cell array formula. For example, select range A2:L2 and enter =MonthNames(). Then press Ctrl+Shift+Enter to enter the array formula in all 12 selected cells. Check out the result.
If you want the month names to display in a column, select 12 cells in a column and use this array formula. (Don’t forget to enter it by pressing Ctrl+Shift+Enter.)
You can also pick out a single month from the array. Here’s a formula (not an array formula) that displays the fourth element of the array: April.
Returning a sorted list
Suppose that you have a list of names you want to show in sorted order in another range of cells. Wouldn’t it be nice to have a worksheet function do that for you?
This custom function does just that: It takes a single-column range of cells as its argument and then returns an array of those cells sorted. Range A2:A13 contains some names. Range C2:C13 contains this multicell array formula. (Remember that you must enter the formula by pressing Ctrl+Shift+Enter.)
Here’s the code for the Sorted function:
Function Sorted(Rng As Range) Dim SortedData() As Variant Dim Cell As Range Dim Temp As Variant, i As Long, j As Long Dim NonEmpty As Long ‘ Transfer data to SortedData For Each Cell In Rng If Not IsEmpty(Cell) Then NonEmpty = NonEmpty + 1 ReDim Preserve SortedData(1 To NonEmpty) SortedData(NonEmpty) = Cell.Value End If Next Cell ‘ Sort the array For i = 1 To NonEmpty For j = i + 1 To NonEmpty If SortedData(i) > SortedData(j) Then Temp = SortedData(j) SortedData(j) = SortedData(i) SortedData(i) = Temp End If Next j Next i ‘ Transpose the array and return it Sorted = Application.Transpose(SortedData) End Function
The Sorted function starts by creating an array named SortedData. This array contains all the nonblank values in the argument range. Next, the SortedData array is sorted, using a bubble-sort algorithm. Because the array is a horizontal array, it must be transposed before it is returned by the function.
The Sorted Function works with a range of any size, as long as it’s in a single column or row. If the unsorted data is in a row, your formula needs to use Excel’s TRANSPOSE function to display the sorted data horizontally. For example: