Working with VBA Functions That Return an Array in Excel 2016 - dummies

Working with VBA Functions That Return an Array in Excel 2016

By John Walkenbach

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.

Using the MonthNames function to return a 12-element array.
Using the MonthNames function to return a 12-element array.

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.)

=TRANSPOSE(MonthNames())

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.

=INDEX(MonthNames(),4)

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.)

Using a custom function to return a sorted range.
Using a custom function to return a sorted range.
=Sorted(A2:A13)

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:

=TRANSPOSE(Sorted(A16:L16))