Can a VBA function in Excel return a range?

Question

I seem to be getting a type mismatch error when trying to do something like this:

In new workbook:

A1 B1
5  4

Function Test1() As Integer
    Dim rg As Range
    Set rg = Test2()
    Test1 = rg.Cells(1, 1).Value
End Function
Function Test2() As Range
    Dim rg As Range
    Set rg = Range("A1:B1")
    Test2 = rg
End Function

Adding =Test1() should return 5 but the code seems to terminate when returning a range from test2(). Is it possible to return a range?

Answer 1

A range is an object. Assigning objects requires the use of the SET keyword, and looks like you forgot one in your Test2 function:

Function Test1() As Integer
    Dim rg As Range
    Set rg = Test2()
    Test1 = rg.Cells(1, 1).Value
End Function

Function Test2() As Range
    Dim rg As Range
    Set rg = Range("A1:B1")
    Set Test2 = rg         '<-- Don't forget the SET here'
End Function

Answer 2

You can also return a Variant() which represents an array of values. Here is an example for a function that reverses values from a range into a new range:

Public Function ReverseValues(ByRef r_values As Range) As Variant()
    Dim i As Integer, j As Integer, N As Integer, M As Integer
    Dim y() As Variant
    N = r_values.Rows.Count
    M = r_values.Columns.Count
    y = r_values.value    'copy values from sheet into an array
    'y now is a Variant(1 to N, 1 to M) 
    Dim t as Variant
    For i = 1 To N / 2
        For j = 1 To M
            t = y(i, j)
            y(i, j) = y(N - i + 1, j)
            y(N - i + 1, j) = t
        Next j
    Next i

    ReverseValues = y
End Function

In the worksheet you have to apply this function as an array formula (with Ctrl-Shift-Enter) with an appropriate number of cells selected. The details of the Swap() function are not important here.

Note that for many rows, this is very efficient. Doing the x = Range.Value and Range.Value = x operations when x is an array and the range contains multiple rows columns is many times faster than doing the operations one by one directly on the cells.

Answer 3

Change last line in Test2 to:

Set Test2 = rg

Answer 4

This also works

Function Test2(Rng As Range) As Range
    Set Test2 = Rng
End Function